In the previous section, you simply selected all of the tables and views created by the spatial sample application. Now we should discuss the underlying tables and what useful operations can be done with them.
The following map layers correspond directly to DB2 tables:
- CUSTOMERS— Point values with the coordinates corresponding to customer addresses
- OFFICES— Point values with the coordinates corresponding to branch office addresses
- FLOODZONES— Polygons defining regions of high flood risk
- REGIONS— Polygons representing sales regions in Kentucky
- HIGHRISKCUSTOMERS is actually a DB2 view with the
CREATE VIEW HIGHRISKCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP, INCOME, PREMIUM, CATEGORY, LOCATION) AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP, C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION FROM CUSTOMERS C, FLOODZONES F WHERE DB2GSE.ST_WITHIN(C.LOCATION, F.LOCATION) = 1)
This view implements a spatial join, joining together the
rows in the CUSTOMER table with the corresponding
rows of the FLOODZONES table where the customer
location is within a flood zone polygon. See Resources for the DB2 InfoCenter, which contains information
on all the spatial functions that can be used in a
Some of the most commonly used spatial functions include:
ST_Contains— The inverse of
ST_Intersects— Tests whether any part of one spatial value intersects another
ST_Touches— Tests whether one spatial value touches another in one place but doesn't overlap
ST_Distance— Tests whether the closest distance between two spatial values is less than a specified distance
Another scenario we may be interested in is finding all the customers within a certain distance of branch offices in order to highlight them on the map and to draw a circle of the same distance. Execute the SQL statements below to create the views:
CREATE VIEW NEARCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP, INCOME, PREMIUM, CATEGORY, LOCATION) AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP, C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION FROM CUSTOMERS C, OFFICES O WHERE DB2GSE.ST_DISTANCE(C.LOCATION, O.LOCATION,'STATUTE MILE') < 10.0)
CREATE VIEW OFFICE_CIRCLE (ID, CIRCLEPOLY) AS (SELECT O.ID, TREAT (DB2GSE.ST_BUFFER(O.LOCATION, 5.0, 'STATUTE MILE') AS db2gse.ST_Polygon) FROM OFFICES O )
TREAT operator in the above view is
needed to inform DB2 that the result of the
ST_BUFFER function will be a polygon
spatial type. Otherwise, the result is considered to be a generic
ST_Geometry spatial type.
To see the results in the geobrowser:
- Start the geobrowser. If it was already active, exit and restart it to make sure that the new spatial views will be picked up.
- Use the File > Open Prepared Map dialog to select the map document you saved earlier.
- Use the File > Add Map Data dialog to connect to the database and select NEARCUSTOMERS and OFFICE_CIRCLE.
- Uncheck the map layers for HIGHRISKCUSTOMERS and FLOODZONES.
- Select NEARCUSTOMERS, setting the color to yellow, the symbol to filled square, and the size to 4.
- Select OFFICE_CIRCLE, setting the color to yellow.
This should result in a map like the following.
Figure 7. Map with customers within 5 miles of a branch office highlighted
The operations above assume you are connected to the database as the same user who originally ran the runGseDemo program. This simplifies table access in the geobrowser and creating the additional database views.
To view spatial tables created by someone else, you need to ensure that the user ID making the connection in the geobrowser has been given SELECT permission on the spatial tables because the geobrowser will only list tables you are authorized to access.
When you create the views, unless you specify otherwise, DB2 assumes that you created and own the tables being referenced. To reference tables created by someone else, you need to explicitly prefix the table name with its schema name — usually the creator name, unless otherwise specified. You can also specify a schema for the view you are creating. In this case, we would express the view as:
CREATE VIEW MYSCHEMA.NEARCUSTOMERS (ID, NAME, ADDRESS, CITY, STATE, ZIP, INCOME, PREMIUM, CATEGORY, LOCATION) AS (SELECT C.ID, C.NAME, C.ADDRESS, C.CITY, C.STATE, C.ZIP, C.INCOME, C.PREMIUM, C.CATEGORY, C.LOCATION FROM DAVEA5.CUSTOMERS C, DAVEA5.OFFICES O WHERE DB2GSE.ST_DISTANCE(C.LOCATION, O.LOCATION,'STATUTE MILE') < 10.0)