Spatial Query Examples

Please refer to the examples in IBM DB2 Spatial Tutorial I (word)and II (word). You can also dowload the sample data and sql scripts. DB2 Spatial Extender documentation can be downloaded here, and the online reference is here. 

Note that you have to select DB2 spatial option during DB2 setup (V10.x), or install Spatial Extender separately for early versions (V9.x or below).

Check the screenshot on selecting DB2 Spatial Extender during setup. 

Database Setup:

-- show info for the dataset:

db2se shape_info -fileName counties.shp

-- enable the database for spatial support:

db2se enable_db sample

-- import counties data:

db2 -tvf import_counties2.sql

--import hospital data:

db2 -tf create_hospitals.sql

The schemas of the tables are as follows:

Counties:

Hospital:

Example Spatial Queries:

After data is loaded, you can try following queries:

Query 1: find all hospitals located in Greene county of New York state.

SELECT h.name, db2gse.st_astext(h.location)

FROM test.hospitals AS h, test.counties AS c

WHERE db2gse.st_within(h.location, c.shape) = 1 

AND c.state_name = 'New York'

AND c.county_name = 'Greene';

Query 2: find all hospitals within 25 miles of current location ()

select

  name

 ,db2gse.st_astext(location) as location_wkt

 ,db2gse.st_distance(location, db2gse.st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') as distance

from test.hospitals 

where db2gse.st_distance(location, db2gse.st_point(-74.237449, 42.036976, 1), 'STATUTE MILE') < 25.0 

order by distance

 ;

Create Indexes:

To find the current coordinate system:

SELECT SRS_NAME, SRS_ID FROM DB2GSE.ST_SPATIAL_REFERENCE_SYSTEMS;