Skip to main content

By clicking Submit, you agree to the developerWorks terms of use.

The first time you sign into developerWorks, a profile is created for you. Select information in your profile (name, country/region, and company) is displayed to the public and will accompany any content you post. You may update your IBM account at any time.

All information submitted is secure.

  • Close [x]

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerworks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

By clicking Submit, you agree to the developerWorks terms of use.

All information submitted is secure.

  • Close [x]

developerWorks Community:

  • Close [x]

Manage spatial data with IBM DB2 Spatial Extender, Part 1: Acquiring spatial data and developing applications

Tips and techniques for developing efficient spatial applications

David Adler (dadler@us.ibm.com), Senior Software Engineer, IBM China
David Adler
David Adler has been responsible for the development of spatial database technology in IBM for over 20 years, the past 10 years in DB2 Spatial Extender Development.

Summary:  This tutorial series describes common tasks to manage spatial data with IBM DB2® Spatial Extender, including importing and creating spatial data, constructing and executing spatial queries, working with IBM, third party, and open source spatial tools, tuning performance, and considering special circumstances in a data warehouse environment. In this first article in the series, learn how to acquire spatial data and build applications. Learn how to use shapefiles, spatial data tables, and spatial indexes.

View more content in this series

Date:  16 Feb 2012
Level:  Intermediate PDF:  A4 and Letter (862 KB | 30 pages)Get Adobe® Reader®

Comments:  

Exploring common problems and tips

Simplifying spatial function syntax

All spatial functions are defined in the schema db2gse, which must be prefixed to the function name in order for it to be correctly identified by DB2. There are two ways to simplify the spatial syntax.

Checking db2gse

The first way to simplify spatial syntax is to tell DB2 to assume that it should check the schema db2gse when resolving functions. This is done by using the set current function path statement before executing queries that involve spatial functions. This enables you to eliminate the schema name on the function calls. Listing 12 shows an example.


Listing 12. Set current function path statement
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.
 

Using method notation

The second way to simplify spatial syntax is to use method notation, which is supported by DB2 LUW, although this is not compatible with the other IBM spatial database products. Using the method notation, you specify the name of the spatial function after the spatial value, as shown in Listing 13.


Listing 13. Using method notation
set current function path = current function path, db2gse;
select
   substr(name, 1, 25) as name
  ,location..st_distance(st_point(-74.237449, 42.036976,1), 'STATUTE MILE') as distance
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      DISTANCE
------------------------- ------------------------
Adventist Home              +2.42936545898570E+001
Greene County Memorial Ho   +2.27887983783443E+001
  2 record(s) selected.
 

In the sections that follow, the examples don't specify the schema because they assume that the function path has been previously set.

Ordering the coordinates

When working with coordinates using latitude and longitude, longitude corresponds to x and latitude corresponds to y. When people give location coordinates or use a device such as a GPS, the coordinates are generally in the order latitude, longitude so it is a very common user error to specify the coordinates in the reverse order when working with latitude and longitude.

For example, to find hospitals within 20 miles of Woodstock, NY (latitude 42.036976, longitude -74.237449), the query would be written as shown in Listing 14.


Listing 14. Coordinate example
select name
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

Working with well-known text

The Spatial Extender supports the well-known text (WKT) representation of spatial values used as constants in spatial queries. The WKT representation is an international specification created by the Open Geospatial Consortium. See the spatial section of the DB2 Infocenter (see Resources) for details about the WKT support in Spatial Extender.

Using WKT, the example query in Listing 14 could be written as shown in Listing 15.


Listing 15. Coordinate example in WKT
select name
from test.hospitals
where st_distance(location,st_point('POINT (-74.237449 42.036976)',1), 'STATUTE MILE')
      < 25.0

Note that coordinates are separated by a space instead of a comma as one might expect.

WKT can also be used to represent line and polygon spatial values. It is important to note that the values defining a coordinate pair are separated by a space, and the coordinate pairs are separated by a comma. Also, when defining polygon values, include double parentheses at the beginning and end of the coordinate list defining the polygon, as shown in Listing 16.


Listing 16. Defining polygon values
select name
from test.hospitals
where st_within(location,st_polygon(
'POLYGON ((-74.1 42.0, -74.1 42.1, -74.0 42.1, -74.0 42.0, -74.1 42.0))',1)) = 1

Selecting spatial values

Most often, spatial values are used in a query but don't actually need to be returned to the user or application as part of the result set, as in the previous example. If you execute a query such as select * from test.hospitals or select location from test.hospitals, you will likely see a long hexadecimal string that isn't very meaningful, because this is the internal, compressed representation of the spatial data. If you want to see the coordinates in a readable form, you need to use the ST_AsText function to return the spatial value in the WKT representation. It is a good idea to restrict the length of the character data returned with the SUBSTR or VARCHAR SQL functions, as shown in Listing 17.


Listing 17. Using the st_astext function
select
   substr(name, 1, 25) as name
  ,varchar(st_astext(location), 30) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ------------------------------
Adventist Home            POINT (-73.785400 42.139526)
Greene County Memorial Ho POINT (-73.878181 42.230648)
  2 record(s) selected.

Another readable format is the Geography Markup Language (GML), which is an XML representation for spatial values defined by the Open Geospatial Consortium. You can select spatial values in this format using the ST_AsGML function, as shown in Listing 18.


Listing 18. Using the st_asgml function
select
   substr(name, 1, 25) as name
  ,varchar(st_asgml(location), 90) as location
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LOCATION
------------------------- ----------------------------------------
Adventist Home            <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.785400 42.139526</gml:pos></gml:Point>

Greene County Memorial Ho <gml:Point srsName="EPSG:4269"><gml:pos>
                          -73.878181 42.230648</gml:pos></gml:Point>
  2 record(s) selected.

When working with web mapping applications and displaying markers for point objects in the database, it is often useful to select the coordinates as x and y (or more likely, longitude and latitude). This is easily done with the ST_X and ST_Y functions, as shown in Listing 19.


Listing 19. Using the st_x and st_y functions
select
   substr(name, 1, 25) as name
  ,st_x(location) as longitude
  ,st_y(location) as latitude
from test.hospitals
where st_distance(location,st_point(-74.237449, 42.036976,1), 'STATUTE MILE') < 25.0

NAME                      LONGITUDE                LATITUDE
------------------------- ------------------------ ------------------------
Adventist Home              -7.37854000000000E+001   +4.21395260000000E+001
Greene County Memorial Ho   -7.38781810000000E+001   +4.22306480000000E+001
  2 record(s) selected.

When to treat a spatial value

A number of the spatial functions that can create different types of spatial values are documented as returning the generic type ST_Geometry. If you then try to use a spatial function that is valid only for a specific spatial type, you will get an error, as shown in Listing 20.


Listing 20. Error message for incompatible spatial type
The invocation of routine "ST_PERIMETER" is ambiguous. The argument in position "1"
does not have a best fit.. SQLCODE=-245, SQLSTATE=428F5, DRIVER=3.62.80

For example, the code in Listing 21 returns this error because the function st_buffer has a return type of st_geometry, even though the actual result will be of type st_polygon.


Listing 21. Example of code using incompatible spatial type
 select
  name
 ,st_perimeter(st_buffer(location, .1)) as perimeter
from test.hospitals
where name like 'New York%'
 

This can be resolved by informing DB2 that it should treat the result as st_polygon, as shown in Listing 22.


Listing 22. Using the treat function
select
  name
 ,st_perimeter(TREAT(st_buffer(location, .1) as db2gse.st_polygon))
  as perimeter
from test.hospitals
where name like 'New York%'
 

The code executes without error and returns the output shown in Listing 23.


Listing 23. Result of using the treat function
NAME                    PERIMETER
----------------------- ------------------------
New York Hospital         +6.28206837256265E-001
New York Hospital         +6.28206837256265E-001
New York State Hospital   +6.28206837256265E-001
  3 record(s) selected.

6 of 11 | Previous | Next

Comments



static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=793988
TutorialTitle=Manage spatial data with IBM DB2 Spatial Extender, Part 1: Acquiring spatial data and developing applications
publish-date=02162012
author1-email=dadler@us.ibm.com
author1-email-cc=