Creating projects that use spatial data

This procedure describes the steps involved in creating projects that use spatial data.

Before you begin

  • Set up Db2® Spatial Extender

Procedure

To create a project that uses spatial data:

  1. Plan and make preparations (set goals for the project, decide what tables and data you need, determine what coordinate system or systems to use, and so on).
  2. Decide whether an existing spatial reference system meets your needs. If none does, create one.
    A spatial reference system is a set of parameter values that includes:
    • Coordinates that define the maximum possible extent of space referenced by a given range of coordinates. You need to determine the maximum possible range of coordinates that can be determined from the coordinate system that you are using, and to select or create a spatial reference system that reflects this range.
    • The name of the coordinate system from which the coordinates are derived.
    • Numbers used in mathematical operations to convert coordinates received as input into values that can be processed with maximum efficiency. The coordinates are stored in their converted form and returned to the user in their original form.
  3. Create spatial columns as needed. Note that in many cases, if data in a spatial column is to be read by a visualization tool, the column must be the only spatial column in the table or view to which it belongs.
    Alternatively, if the column is one of multiple spatial columns in a table, it could be included in a view that has no other spatial columns, and visualization tools could read the data from this view.
  4. Set up spatial columns for access by visualization tools, as needed, by registering the columns in the Db2 Spatial Extender catalog.
    When you register a spatial column, Db2 Spatial Extender imposes a constraint that all data in the column must belong to the same spatial reference system. This constraint enforces integrity of the data-a requirement of most visualization tools.
  5. Populate spatial columns by performing one of the following actions:
    1. For a project that requires spatial data to be imported, import the data.
    2. For a project that requires a spatial column to be set from the LATITUDE and LONGITUDE columns, run an SQL UPDATE statement using the db2gse.ST_Point constructor.
  6. Facilitate access to spatial columns, as needed. This involves defining indexes that enable the database manager to access spatial data quickly, and defining views that enable users to retrieve interrelated data efficiently.
    If you want visualization tools to access the views' spatial columns, you might need to register these columns with Db2 Spatial Extender as well.
  7. Analyze generated spatial information and related business information.
    This task involves querying spatial columns and related non-spatial columns. In such queries, you can include Db2 Spatial Extender functions that return a wide variety of information. For example, coordinates that define a proposed safety zone around a hazardous waste site, or the minimum distance between this site and the nearest public building.

Example

The following example is a continuation from the example in Setting up Db2 Spatial Extender It shows the steps performed by Safe Harbor Real Estate Insurance Company to create a project for the integration of business and spatial data.The following example shows a fictional company, Safe Harbor Real Estate Insurance Company, creating a project for the integration of business and spatial data..
  1. The Spatial department prepares to develop a project; for example:
    • The management team sets these goals for the project:
      • To determine where to establish new branch offices
      • To adjust premiums on the basis of customers' proximity to hazardous areas (areas with high rates of traffic accidents, areas with high rates of crime, flood zones, earthquake faults, and so on)
    • This particular project will be concerned with customers and offices in the United States. Therefore, the spatial administration team decides to use the GCS_NORTH_AMERICAN_1983 coordinate system for the United States that Db2 Spatial Extender provides.
    • The spatial administration team decides what data is needed to meet the project's goals and what tables will contain this data.
  2. Db2 Spatial Extender provides a spatial reference system, called NAD83_SRS_1, that is designed to be used with GCS_NORTH_AMERICAN_1983. The spatial administration team decides to use NAD83_SRS_1.
  3. The spatial administration team defines columns to contain spatial data.
    • The team verifies that the table already contains customer LATITUDE and LONGITUDE columns. The values in these columns will be used subsequently to translate to spatial point values.
    • The team creates the OFFICE_LOCATIONS and OFFICE_SALES tables to contain data that is now stored in a separate file system using an industry-standard shapefile format. This data includes the addresses of Safe Harbor's branch offices, spatial data that was derived from these addresses by a geocoder, and spatial data that defines a zone within a five-mile radius around each office. The data derived by the geocoder will go into a LOCATION column in the OFFICE_LOCATIONS table, and the data that defines the zones will go into a SALES_AREA column in the OFFICE_SALES table.
  4. The spatial administration team expects to use visualization tools to render the content of the LOCATION columns and the SALES_AREA column graphically on a map. Therefore, the team registers all three columns.
  5. The spatial administration team populates the LOCATION column in the CUSTOMER table, the OFFICE_LOCATIONS table, the OFFICE_SALES table, and a new HAZARD_ZONES table:
    • The team uses the statement UPDATE CUSTOMERS SET LOCATION = db2gse.ST_Point(LONGITUDE, LATITUDE,1) to populate the LOCATION value from LATITUDE and LONGITUDE.
    • The team imports data to the OFFICE_LOCATIONS and OFFICE_SALES tables by using the db2se import_shape command.
    • The team creates a HAZARD_ZONES table, registers its spatial columns, and imports data to it. The data comes from a file supplied by a map vendor.
  6. The spatial administration team creates indexes for the registered columns. Then, they create a view that joins columns from the CUSTOMERS and HAZARD ZONES tables and register the spatial columns in this view.
  7. The spatial analysis team runs queries to obtain information that will help them determine where to establish new branch offices, and to adjust premiums on the basis of customers' proximity to hazard areas.