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:  

Creating a spatial table from point coordinates

Understanding point locations

Much of the spatial data important in analysis is based on point locations, including the following:

  • Customer home locations
  • Customer mobile location
  • Store locations
  • Service locations
  • Cell tower locations
  • Hospitals

The most common representation of point locations uses degrees of latitude and longitude, which are derived from a geocoding process, from a GPS, or possibly from coordinates on a map. Geocoding is the process of converting a street address to a latitude and longitude value. This is typically performed by specialized software using a large dataset of addresses or of street segments. Often this is done in a batch mode to handle a large number of addresses, although it is also often done by a web service to geocode an address typed in by a user. Many companies already have this software installed and have the locations in a file or in database columns. See Resources for a developerWorks article with more details about geocoding with DB2 Spatial Extender.


Loading and maintaining spatial values

This section of the tutorial discusses the loading and maintenance of spatial values in a table, assuming that the latitude and longitude are already available. This example addresses managing a table of hospital locations.

See Downloads to download the data and SQL scripts used in this section.


Importing and creating from a file

Listing 5 shows an example that you can execute with the command db2 -tvf create_hospitals.sql


Listing 5. Contents of create_hospitals.sql
drop table test.hospitals;

create table test.hospitals(name varchar(70), longitude double, latitude double);

import from "hospitals.csv" of del
modified by coldel, decpt.
method p (1, 2, 3)
messages "import.msg"
insert into test.hospitals (name, longitude, latitude);

alter table test.hospitals add column location db2gse.st_point;

!db2se register_spatial_column sample
-tableSchema      test
-tableName        hospitals
-columnName       location
-srsName          nad83_srs_1
;

update test.hospitals
set location = db2gse.st_point(longitude, latitude,1)
;

reorg table test.hospitals;

create index test.hospitalsidx on test.hospitals(location)
extend using db2gse.spatial_index(.1, 0, 0);

runstats on table test.hospitals and indexes all;

select count(*) from test.hospitals;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

Notes:

  1. Drop the table in case it already exists. Only do this for test purposes.
  2. The hospital table is created with columns for the name, longitude, and latitude.
  3. The DB2 import utility is used to populate the table from a file containing delimited fields with the data for the name, longitude, and latitude.
  4. The table is altered to add a column of type db2gse.ST_Point. If the table already existed with the latitude and longitude, the previous steps would have been skipped.
  5. The db2se register_spatial_column command creates a constraint that all spatial values use the SRS nad83_srs_1. It also creates an entry in the db2gse.st_geometry_columns view that associates this SRS with this spatial column.
  6. The work of creating the spatial value is done very simply with an SQL update statement that sets the location column to the result of the ST_Point constructor, which accepts longitude, latitude, and srid values. It is important to note that the longitude value comes first, the opposite of the ordering you commonly see. The srid value of 1 indicates that the spatial reference system for NAD83 should be used.
  7. The DB2 reorg utility is invoked as the process of updating the location column increases the size of the row and will likely cause page spills.
  8. A spatial index is created with reasonable values.
  9. The runstats command is executed to update the table statistics. It is always good to do this after loading data or after making significant data updates.
  10. The number of rows is queried, and a few rows for New York hospitals are selected. The db2gse.ST_AsText spatial function is used to return a readable representation of the spatial value.

Maintaining spatial values

It is very likely that a table of locations will have updates, and it is important to ensure that the spatial values are set or updated appropriately as rows are inserted or updated. The SQL mechanism for doing this is to establish triggers on the table when the latitude or longitude values are changed.

Listing 6 shows an example of creating triggers and testing the consequences.


Listing 6. Contents of create_hospitals_triggers.sql
create trigger test.hosp_loc_update no cascade
before update of latitude, longitude
on test.hospitals
referencing  new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

create trigger test.hosp_loc_insert no cascade
before insert
on test.hospitals
referencing new as n
for each row mode db2sql
set n.location = db2gse.st_point(n.longitude, n.latitude, 1)
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

insert into test.hospitals(name, longitude, latitude)
values('New York2', -76.5, -42.3)
;

update test.hospitals
set (longitude, latitude) = (-76.5, -42.3)
where name = 'New York Hospital'
;

select
  substr(name, 1, 25) as name
 ,decimal(longitude, 8, 4) as longitude
 ,decimal(latitude, 8, 4) as latitude
 ,varchar(db2gse.st_astext(location), 30) as location_wkt
from test.hospitals
 where name like 'New York%'
 ;

Notes:

  1. Two separate but very similar triggers are created: one for update and one for insert. Before the row data is inserted or updated, the spatial constructor is invoked to return the ST_Point value, which will be stored in the location column.
  2. To see the effect of the triggers, rows that will be modified are selected, as shown in Listing 7.

Listing 7. Rows to be modified
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -73.9537    40.7645 POINT (-73.953751 40.764542)
New York Hospital           -73.7537    41.0275 POINT (-73.753746 41.027599)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
  3 record(s) selected.

  1. A new row is inserted, and the rows for New York Hospital are updated. You can see the updated location values, as shown in Listing 8.

Listing 8. Updated location values
.
NAME                      LONGITUDE  LATITUDE   LOCATION_WKT
------------------------- ---------- ---------- ------------------------------
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York Hospital           -76.5000   -42.3000 POINT (-76.500000 -42.300000)
New York State Hospital     -73.9517    41.5273 POINT (-73.951797 41.527317)
New York2                   -76.5000   -42.3000 POINT (-76.500000 -42.300000)
  4 record(s) selected.

  1. For readability purposes, the first part of the name is selected, the longitude and latitude are converted to decimal values, and the location column is converted to the well-known text (wkt) representation.

4 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=