DB2 Spatial Extender V8 Bank Demo Queries

1. Nearest branch for each customer

   CREATE VIEW bankdemo.v01 AS
    WITH dist(ct_id, b_id, distance) AS
         (SELECT customer_id, branch_id, db2gse.st_distance(c.feature, b.feature)
          FROM bankdemo.customer c, bankdemo.branch b
          )
    SELECT c.*, d1.b_id, d1.distance
    FROM dist d1, bankdemo.customer c
    WHERE d1.ct_id=c.customer_id AND
          d1.distance = (SELECT MIN(distance)
                            FROM dist d2
                            WHERE d2.ct_id=d1.ct_id)
   ;
   

2. Checking balances greater than $1,000 for customers within 1.0 mile of branches

   CREATE VIEW bankdemo.v02 AS
    SELECT db2gse.st_distance(b.feature, c.feature, 'STATUTE MILE') as distance, a.balance, c.*
    FROM bankdemo.account a, bankdemo.branch b, bankdemo.customer c
    WHERE db2gse.st_distance(b.feature, c.feature, 'STATUTE MILE') < 1.0 AND
          a.type = 'Checking' AND
          a.customer_id = c.customer_id AND
          a.balance > 1000
   ;
   

3. Savings balances greater than $45,000 for customers within 1.0 mile of branches

   CREATE VIEW bankdemo.v03 AS
    SELECT db2gse.st_distance(b.feature, c.feature, 'STATUTE MILE') as distance, a.balance, c.*
    FROM bankdemo.account a, bankdemo.branch b, bankdemo.customer c
    WHERE db2gse.st_distance(b.feature, c.feature, 'STATUTE MILE') < 1.0 AND
          a.type = 'Saving' AND
          a.customer_id = c.customer_id AND
          a.balance > 45000
   ;
   

4. Customers with greater than $50,000 in their saving accounts in overlapping buffer zones

   CREATE VIEW bankdemo.v04 AS
     SELECT c.*, a.balance
     FROM   bankdemo.customer c, bankdemo.account a
     WHERE
     (db2gse.st_within(c.feature,
             db2gse.st_intersection(
                    db2gse.st_buffer(
                           (select feature from bankdemo.branch where branch_id = 1)
                            , 2.5, 'STATUTE MILE'),
                    db2gse.st_buffer(
                           (select feature from bankdemo.branch where branch_id = 2)
                           , 2.5, 'STATUTE MILE' ) )
                       )=1)
             AND a.balance > 50000 AND a.type = 'Saving'
             AND a.customer_id = c.customer_id
   ;
   

5. Customers of Branch 1 - Meridian

   CREATE VIEW bankdemo.v05 AS
    SELECT c.*,a.branch_id
    FROM bankdemo.customer c, bankdemo.account a
    WHERE a.customer_id = c.customer_id
    AND a.branch_id = 1
   ;
   

6. Customers of Branch 2 - San Carlos

   CREATE VIEW bankdemo.v06 AS
    SELECT c.*,a.branch_id
    FROM bankdemo.customer c, bankdemo.account a
    WHERE a.customer_id = c.customer_id
    AND a.branch_id = 2
   ;
   

7. Total account balance for each customer

   CREATE VIEW bankdemo.v07 AS
    SELECT c.*, a.balance
    FROM bankdemo.customer c, (SELECT x.customer_id, SUM(x.balance) AS balance
                     FROM bankdemo.account x
                     GROUP BY x.customer_id) a
    WHERE a.customer_id = c.customer_id
   ;
   

8. Savings account balance for each customer

   CREATE VIEW bankdemo.v08 AS
    SELECT c.*, a.balance
    FROM bankdemo.account a, bankdemo.customer c
    WHERE a.type = 'Saving' AND
          a.customer_id = c.customer_id
   ;
   

9. Checking account balance for each customer

   CREATE VIEW bankdemo.v09 AS
    SELECT c.*, a.balance
    FROM bankdemo.account a, bankdemo.customer c
    WHERE a.type = 'Checking' AND
          a.customer_id = c.customer_id
   ;
   

10. Buffer zones 2.5 miles around each branch

    CREATE VIEW bankdemo.v10(se_row_id, feature) AS
      SELECT se_row_id, TREAT(db2gse.st_buffer(feature, 2.5, 'STATUTE MILE') AS db2gse.st_polygon)
       FROM bankdemo.branch
    ;
    

11. Average savings balance per census block

    CREATE VIEW bankdemo.v11 AS
      SELECT s.blockgroup, x.av, s.se_row_id, s.feature
      FROM bankdemo.selblocks s,
         (SELECT s1.blockgroup, AVG(c1.balance) av
          FROM bankdemo.selblocks s1,
              (SELECT c.feature, a.balance
               FROM bankdemo.customer c, bankdemo.account a
               WHERE (a.type='Saving') AND
                     (a.customer_id=c.customer_id) ) AS c1
          WHERE  (db2gse.st_within(c1.feature, s1.feature)=1)
          GROUP BY s1.blockgroup) AS x
      WHERE s.blockgroup=x.blockgroup
    ;
    

12. Census blocks with income greater than 50% of the maximum average savings balance

    CREATE VIEW bankdemo.v12 AS
     WITH temp(max_avg_bal) AS
        (SELECT MAX(x.av) FROM
           (SELECT s1.blockgroup, AVG(c1.balance) av
            FROM bankdemo.selblocks s1,
                 (SELECT c.feature, a.balance
                  FROM bankdemo.customer c, bankdemo.account a
                  WHERE (a.type='Saving') AND
                        (a.customer_id=c.customer_id) ) AS c1
            WHERE  (db2gse.st_within(c1.feature, s1.feature)=1)
            GROUP BY s1.blockgroup) AS x)
      SELECT s.blockgroup, s.median_fam, s.se_row_id, s.feature
      FROM bankdemo.selblocks s, temp
    	WHERE s.median_fam > (0.5 * temp.max_avg_bal)
    ;