DB2 Spatial Extender V8 Hospital Demo Queries

1. Patients within 1 mile of an arbitrary point

   CREATE VIEW hospdemo.v01 AS
     SELECT *
     FROM hospdemo.patient
     WHERE db2gse.st_distance(feature,
                              db2gse.st_point(-82.835, 39.954, 1),
                              'STATUTE MILE') < 1.0
   ;
   

2. Patients within an arbitrary rectangle

   CREATE VIEW hospdemo.v02 AS
     SELECT *
     FROM hospdemo.patient
     WHERE db2gse.st_contains(db2gse.st_polygon('polygon((-83.0 40.0,-83.0 40.1,-82.9 40.1,-82.9 40.0,-83.0 40.0))',1),
                              feature) = 1
   ;
   

3. Patients within 2.1 miles of hospital 1

   CREATE VIEW hospdemo.v03 AS
     SELECT *
     FROM hospdemo.patient
     WHERE db2gse.st_distance(feature,
                              (SELECT feature FROM hospdemo.hospital WHERE h_id=1),
                              'STATUTE MILE') < 2.1
   ;
   

4. Patients within 2.1 miles of hospital 1 or hospital 7

   CREATE VIEW hospdemo.v04 AS
     SELECT *
       FROM hospdemo.patient
         WHERE
           db2gse.st_distance(feature,
                              ( SELECT feature
                                FROM hospdemo.hospital
                                WHERE h_id=1),
                              'STATUTE MILE') < 2.1
           OR
           db2gse.st_distance(feature,
                              ( SELECT feature
                                FROM hospdemo.hospital
                                WHERE h_id=7),
                              'STATUTE MILE') < 2.1
   ;
   

5. Patients between 2.1 and 3.5 miles from hospital 1

   CREATE VIEW hospdemo.v05 AS
     SELECT *
     FROM hospdemo.patient
     WHERE db2gse.st_distance(feature,
                              ( SELECT feature
                                FROM hospdemo.hospital
                                WHERE h_id=1 ),
                              'STATUTE MILE') >= 2.1
           AND
           db2gse.st_distance(feature,
                              ( SELECT feature
                                FROM hospdemo.hospital
                                WHERE h_id=1 ),
                              'STATUTE MILE') <= 3.5
   ;
   

6. Patients diagnosed with malaria within 2.1 miles of hospital 1

   CREATE VIEW hospdemo.v06 AS
     SELECT *
     FROM hospdemo.patient
     WHERE db2gse.st_distance(feature,
                              ( SELECT feature
                                FROM hospdemo.hospital
                                WHERE h_id=1),
                              'STATUTE MILE' ) <= 2.1
           AND
           pt_id IN (SELECT pt_id
                     FROM hospdemo.medhist
                     WHERE diag_code = '272.4')
   ;
   

7. Interstates within 3.5 miles of a hospital

   CREATE VIEW hospdemo.v07 AS
     SELECT i.*
     FROM hospdemo.hospital h, hospdemo.intersta i
     WHERE db2gse.ST_Distance(h.feature,i.feature,'STATUTE MILE') < 3.5
   ;
   

8. Number of hospital visits for each patient

   CREATE VIEW hospdemo.v08 AS
     SELECT p.*, m.cnt
     FROM hospdemo.patient AS p,
       (SELECT pt_id, COUNT(date) AS cnt
        FROM hospdemo.medhist
        GROUP BY pt_id
        HAVING COUNT(date)>1) AS m
     WHERE m.pt_id=p.pt_id
   ;
   

9. Hospital charges for each hospital

   CREATE VIEW hospdemo.v09 AS
     SELECT h.*, m.avg_chgs, m.sum_chgs, m.cnt_chgs
      FROM hospdemo.hospital h,
       ( SELECT h_id,
                AVG(chgs) AS avg_chgs,
                SUM(chgs) AS sum_chgs,
                COUNT(chgs) AS cnt_chgs
         FROM hospdemo.medhist
         GROUP BY h_id) AS m
       WHERE m.h_id=h.h_id
   ;
   

10. Nearest hospital for each patient

    CREATE VIEW hospdemo.v10 AS
      WITH dist(pt_id, h_id, distance) AS (
        SELECT pt_id, h_id, db2gse.st_distance(p.feature, h.feature, 'STATUTE MILE')
        FROM hospdemo.patient p, hospdemo.hospital h
        WHERE p.pt_id > 0 AND
              p.feature IS NOT NULL AND
              h.feature IS NOT NULL
        ),
        closest(pt_id, distance) AS (
          SELECT pt_id, MIN(distance)
          FROM dist
          GROUP BY pt_id
        )
      SELECT p.*, d1.h_id, d1.distance
      FROM closest AS c1, dist AS d1, hospdemo.patient p
      WHERE d1.pt_id=c1.pt_id AND
            d1.distance = c1.distance AND
            p.pt_id = c1.pt_id
    ;