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:  

Executing spatial queries

Because spatial queries are no different than any other SQL query, any environment that can be used to execute SQL queries can be used to execute queries that include spatial operations. In the previous sections, spatial queries were executed using the DB2 command line processor by referencing a file of SQL statements. This section discusses some of the other environments for executing spatial queries, including IBM Data Studio, Java language applications, and C language applications.

Working with IBM Data Studio

IBM Data Studio is a free, Eclipse-based environment in which to develop and test database applications. Data Studio replaces the DB2 Control Center, which has been deprecated and is no longer available with DB2 Version 10. See Resources for information on downloading and setting up Data Studio.

The IBM Data Studio full-client enables you to run SQL commands as well as do Java development. The full client is recommended for the following sections of this tutorial.

When you have installed IBM Data Studio and have brought it up, set up an environment for executing the queries by completing the following steps.

  1. Click File > New > Data Development Project.
  2. Specify a name for your project, such as SpatialProject.
  3. Select a database to which to connect.
  4. Append DB2GSE to the default path, and click Finish.
  5. Right-click your project name in the left pane, and select New SQL or XQuery Script.
  6. Name your project something like query_hospital_counties, and click Finish.
  7. Copy the code in Listing 24 into the script input area.

Listing 24. Code for script input area
SELECT h.name, st_astext(h.location)
FROM test.hospitals AS h, test.counties AS c
WHERE st_within(h.location, c.shape) = 1
AND c.state_name = 'New York'
AND c.county_name = 'Greene';

  1. Press Ctrl+S to save the script.
  2. Press F5 to run the query. The query runs and presents a screen as shown in Figure 2. Click the Result1 tab to see your actual results.

Figure 2. Hospitals in county query in Data Studio
Data Studio results of first query run

Repeat the steps for the SQL query of distance shown in Listing 25.


Listing 25. SQL distance query
select
  name
 ,st_astext(location) as location_wkt
 ,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
order by distance

The query runs and presents a screen as shown in Figure 3.


Figure 3. Distance to hospitals query in Data Studio
Data Studio distance to hospitals results

Working with the JDBC interface in Java applications

This section describes how to run spatial queries using JDBC in the Data Studio environment. If you have a JAVA SDK installed, the sample JDBC program available in Downloads can also be simply compiled and executed in a command-line environment.

Complete the following steps to create a Java development project in Data Studio and execute the same queries that you ran as SQL scripts.

  1. Click File > New > Java Development Project.
  2. Specify a name for your project, such as SpatialJDBCProject, and click Next.
  3. On the Java Settings window, click the Libraries tab.
  4. Click the Add External JARs button, and navigate to the sqllib\java directory in your DB2 installation.
  5. Select the db2jcc.jar and db2jcc_license_cu.jar files, and click Finish.
  6. Right-click the src node under your new project, and click New > Class.
  7. Specify JDBCSpatialQuery as the name of your class, and click Finish.
  8. Open the sample Java program from Downloads in a text editor, copy all the text, and replace the minimal class definition in the Data Studio editor window.
  9. Click Ctrl+S to save the file.
  10. If you are not using the SAMPLE database, modify the source code to substitute the name of your database, and save the file.
  11. Right-click in the editor window, and select Run As > Java Application. Figure 4 shows the console window in the bottom pane.

Figure 4. JDBC query in Data Studio
Data Studio output of JDBCSpatialQuery

Explanation of application

The static main function simply creates a connection to the SAMPLE database using the current user authorization by default or by the specified user ID and password, if provided as parameters. An instance of the class is created, and then each of the instance methods to perform queries are invoked with the database connection and appropriate parameters.

Function hospitalInCounty1

This section describes the simplest approach, although not the most efficient. The SQL query is constructed identically to the query executed previously in the tutorial in the Data Studio script editor by substituting into the character string the parameter values for the county and state names as constants. A Java statement object is created, and then the SQL query is passed to the executeQuery method. A loop is then set up to read each row of the result set, getting the name and location as text strings, which are output to the console. When this is complete, the result set and statement are closed. Listing 26 shows an example.


Listing 26. Example using constants
 void hospitalInCounty1(Connection con, String stateName, String countyName)
                        throws SQLException {
                Statement stmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "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 = '" + stateName + "' "
                                + "AND c.county_name = '" + countyName + "' ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                stmt = con.createStatement();
                rs = stmt.executeQuery(sel1);

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                stmt.close();
        }

Function hospitalInCounty2

This function is similar to Listing 26, but it uses parameter markers for the parameters instead of constants. This is particularly important if similar SQL statements will be executed repeatedly, because DB2 will cache recently compiled SQL statements and reuse them when it sees them again, even if they come from a different application. This can be useful when multiple users are running the same application with different values.

In order to use parameter markers, place question mark (?) characters in the SQL statement instead of the actual parameter value. A Java PreparedStatement object is created from the connection, passing in the SQL statement. The setString method is then invoked to set the parameter values for the state and county names. The rest of the logic is the same.

If the same SQL statement is going to be executed multiple times within the same application, it would be even more efficient to save the PreparedStatement and reuse it while providing new parameter values. Listing 27 shows an example.


Listing 27. Example using question marks in place of parameter values
void hospitalInCounty2(Connection con, String stateName, String countyName)
                        throws SQLException {
                PreparedStatement pstmt;
                ResultSet rs;
                // Query hospitals that are within the specified state and county
                String sel1 = "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 = ?" + "AND c.county_name = ? ";

                System.out.println("\n\nQuery hospitals in " + countyName + ", "
                                + stateName);
                pstmt = con.prepareStatement(sel1);
                pstmt.setString(1, stateName); // set state name parameter
                pstmt.setString(2, countyName); // set county name parameter
                rs = pstmt.executeQuery();

                // display the result set
                // rs.next() returns false when there are no more rows
                while (rs.next()) {
                        String name1 = rs.getString(1);
                        String location = rs.getString(2);
                        System.out.println("Hospital name: '" + name1 + "'; location: "
                                        + location);
                }
                rs.close();
                pstmt.close();
        }

Function hospitalDistance

This hospitalDistance function implements the query to find hospitals within 25 miles of the specified location. The approach also uses parameter markers for the coordinates of the specified location. Note the use of the CAST expression in the SQL statement. Listing 28 shows an example.


Listing 28. Using the hospitalDistance function
String sel2 = "SELECT name, db2gse.st_astext(location), "
            + "db2gse.st_distance(location, db2gse.st_point(CAST (? AS DOUBLE), "
            + "CAST (? AS DOUBLE), 1), 'STATUTE MILE') AS distance "
            + "FROM test.hospitals "
            + "WHERE db2gse.st_distance(location, "
            + "db2gse.st_point(CAST (? AS DOUBLE), CAST (? AS DOUBLE), 1), "
            + "STATUTE MILE')  < 25.0 "
            + "ORDER BY distance";

In order for the point constructor to be recognized properly, it is necessary to explicitly cast the longitude and latitude parameters as DOUBLE values.


Working with the CLI interface in C-language applications

The installation of DB2 Spatial Extender includes a sample program gseRunGseDemo in the directory sqllib/samples/extenders/spatial that can be executed directly to verify the spatial installation by loading spatial data, executing various spatial stored procedures, and executing sample spatial queries against the data that was loaded. gseRunGseDemo is a C-language application that uses the DB2 CLI interface and is provided in source form that you can examine, modify, and compile in your own environment. The application is documented in the spatial section of the DB2 Infocenter under the topic Writing applications and using the sample program (see Resources).


Working with other application languages

Generally, any language that allows you to execute SQL statements against DB2 can be used to execute spatial queries. This is particularly useful with web-oriented languages such as PHP, Ruby, and Perl, which can be used in conjunction with the web mapping APIs provided by services such as Google, Yahoo, and Esri ArcGIS Online.

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