天文データセンター SQL講習会[データベース構築編] 資料 回答例

山内＠天文データセンター

2011年12月 初版 (2012年2月更新)

課題1

1. CREATE TABLE AkariFis (
           objID           INTEGER           NOT NULL,
           objName         CHARACTER(14)     NOT NULL,
           ra              DOUBLE PRECISION  NOT NULL,
           dec             DOUBLE PRECISION  NOT NULL,
           posErrMj        REAL              NOT NULL,
           posErrMi        REAL              NOT NULL,
           posErrPA        REAL              NOT NULL,
           flux_65         REAL,
           flux_90         REAL,
           flux_140        REAL,
           flux_160        REAL,
           fErr_65         REAL,
           fErr_90         REAL,
           fErr_140        REAL,
           fErr_160        REAL,
           fQual_65        SMALLINT          NOT NULL,
           fQual_90        SMALLINT          NOT NULL,
           fQual_140       SMALLINT          NOT NULL,
           fQual_160       SMALLINT          NOT NULL,
           flags_65        SMALLINT,
           flags_90        SMALLINT,
           flags_140       SMALLINT,
           flags_160       SMALLINT,
           nScanC_65       SMALLINT          NOT NULL,
           nScanC_90       SMALLINT          NOT NULL,
           nScanC_140      SMALLINT          NOT NULL,
           nScanC_160      SMALLINT          NOT NULL,
           nScanP_65       SMALLINT          NOT NULL,
           nScanP_90       SMALLINT          NOT NULL,
           nScanP_140      SMALLINT          NOT NULL,
           nScanP_160      SMALLINT          NOT NULL,
           mConf_65        SMALLINT,
           mConf_90        SMALLINT,
           mConf_140       SMALLINT,
           mConf_160       SMALLINT,
           nDens           SMALLINT          NOT NULL,
           cx              DOUBLE PRECISION  NOT NULL,
           cy              DOUBLE PRECISION  NOT NULL,
           cz              DOUBLE PRECISION  NOT NULL,
           CONSTRAINT akarifis_pkey PRIMARY KEY(objid),
           CONSTRAINT akarifis_objname UNIQUE(objname)
   );
   

課題2

1. CREATE FUNCTION fEq2Y(r DOUBLE PRECISION, d DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       BEGIN
         RETURN sin(radians(r)) * cos(radians(d));
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

2. CREATE FUNCTION fEq2Z(r DOUBLE PRECISION, d DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       BEGIN
         RETURN sin(radians(d));
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

課題3

1. CREATE FUNCTION 
   fDistanceArcminXyz(x1 DOUBLE PRECISION,y1 DOUBLE PRECISION,z1 DOUBLE PRECISION,
                      x2 DOUBLE PRECISION,y2 DOUBLE PRECISION,z2 DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       DECLARE
         v DOUBLE PRECISION;
       BEGIN
         v := x2*x1 + y2*y1 + z2*z1;
         IF ( 1.0 < v ) THEN v = 1.0;
         ELSIF ( v < -1.0 ) THEN v = -1.0;
         END IF;
         RETURN acos( v ) * 3437.7467707849396;
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

課題4

1. CREATE FUNCTION fArcMin2Rad(v DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       BEGIN
         RETURN 0.0002908882086657216 * v;
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

課題5

1. SELECT objid, objname, ra, dec,
          cx, cy, cz
   FROM akariirc
   WHERE (cx BETWEEN fEq2X(188.580537,6.467703) - fArcMin2Rad(50) AND
                     fEq2X(188.580537,6.467703) + fArcMin2Rad(50)     ) AND
         (cy BETWEEN fEq2Y(188.580537,6.467703) - fArcMin2Rad(50) AND
                     fEq2Y(188.580537,6.467703) + fArcMin2Rad(50)     ) AND
         (cz BETWEEN fEq2Z(188.580537,6.467703) - fArcMin2Rad(50) AND
                     fEq2Z(188.580537,6.467703) + fArcMin2Rad(50)     );
   

   結果の行数は6．

2. SELECT o.*
   FROM 
   (
    SELECT objid, objname, ra, dec, cx, cy, cz,
           fDistanceArcminXyz( cx,cy,cz, 
                               fEq2X(188.580537,6.467703),
                               fEq2Y(188.580537,6.467703),
                               fEq2Z(188.580537,6.467703) ) as distance
    FROM akariirc
    WHERE (cx BETWEEN fEq2X(188.580537,6.467703) - fArcMin2Rad(50) AND
                      fEq2X(188.580537,6.467703) + fArcMin2Rad(50)     ) AND
          (cy BETWEEN fEq2Y(188.580537,6.467703) - fArcMin2Rad(50) AND
                      fEq2Y(188.580537,6.467703) + fArcMin2Rad(50)     ) AND
          (cz BETWEEN fEq2Z(188.580537,6.467703) - fArcMin2Rad(50) AND
                      fEq2Z(188.580537,6.467703) + fArcMin2Rad(50)     )
   ) o
   WHERE o.distance <= 50;
   

   結果の行数は3．

課題6

1. CREATE FUNCTION fAkariIrcGetNearbyObjEq(DOUBLE PRECISION, DOUBLE PRECISION, DOUBLE PRECISION)
     RETURNS SETOF tObjCelAndDistance
     AS 'SELECT o.*
         FROM (
          SELECT objid, ra, dec,
                 fDistanceArcMinXYZ(cx,cy,cz,
                               fEq2X($1,$2),fEq2Y($1,$2),fEq2Z($1,$2)) AS distance
          FROM akariirc
          WHERE (cx BETWEEN fEq2X($1,$2) - fArcMin2Rad($3) AND 
                            fEq2X($1,$2) + fArcMin2Rad($3)) AND 
                (cy BETWEEN fEq2Y($1,$2) - fArcMin2Rad($3) AND 
                            fEq2Y($1,$2) + fArcMin2Rad($3)) AND 
                (cz BETWEEN fEq2Z($1,$2) - fArcMin2Rad($3) AND 
                            fEq2Z($1,$2) + fArcMin2Rad($3))
         ) o
         WHERE o.distance <= $3'
     LANGUAGE 'sql';
   

課題7

1. CREATE FUNCTION fABMag2Jy(v DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       BEGIN
           RETURN 3631.0 * (10.0 ^ (-0.4 * v));
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

2. CREATE FUNCTION fJy2ABMag(v DOUBLE PRECISION)
     RETURNS DOUBLE PRECISION AS $$
       BEGIN
           IF v <= 0.0 THEN RETURN NULL; END IF;
           RETURN -2.5 * log(v/3631.0);
       END;
     $$ IMMUTABLE LANGUAGE 'plpgsql';
   

課題8

1. CREATE INDEX akariirc_ab_flux_09 ON akariirc (fJy2ABMag(flux_09));
   

課題9

1. CREATE FUNCTION fAkariIrcGetNearestObjIDEq(FLOAT8,FLOAT8,FLOAT8)
     RETURNS INTEGER
     AS 'SELECT o.objid FROM
         (SELECT objid, fDistanceArcMinXYZ(cx,cy,cz, 
                               fEq2X($1,$2),fEq2Y($1,$2),fEq2Z($1,$2)) AS distance
          FROM akariirc
          WHERE (cx BETWEEN fEq2X($1,$2) - fArcMin2Rad($3) AND 
                            fEq2X($1,$2) + fArcMin2Rad($3)) AND 
                (cy BETWEEN fEq2Y($1,$2) - fArcMin2Rad($3) AND 
                            fEq2Y($1,$2) + fArcMin2Rad($3)) AND 
                (cz BETWEEN fEq2Z($1,$2) - fArcMin2Rad($3) AND 
                            fEq2Z($1,$2) + fArcMin2Rad($3))
         ) o
         WHERE o.distance <= $3
         ORDER BY o.distance
         LIMIT 1'
     IMMUTABLE LANGUAGE 'sql';