Data migration

You can using the WKT data exchange format or shapefiles to migrate geospatial data.

Method 1: Use the WKT data exchange format

You can import geometry data into Db2® after first converting it into the WKT data exchange format.

  1. On the source PureData® System for Analytics system, use the ST_AsText spatial function to export, in ASCII WKT format, geospatial data to a external table. For example:
    create external table '/nz/mydatadir/unload/environmental_hazards.txt' using ( escapechar '\' delimiter '|' maxerrors 1000 ) as SELECT OBJECTID, GEOID, STATE, STUSAB, STATE_NAME, COUNTY, COUNTY_NAM, CNTY_FIPS, TRACT, "haz_idx", "Shape__Are", "Shape__Len", inza..st_astext(GEOM) FROM ENVIRONMENTAL_HAZARDS;
  2. Note the size of the geometry column of the resulting external table.
  3. Transfer the external table to the target Db2 system.
  4. On the target system, create a new table. For the columns that are to contain geospatial data, specify inline lengths as described in Specifying inline lengths for geospatial columns. For example, to create a table geotest.hazards with an inline length of 4800:
    create table geotest.hazards (objectid integer, geoid varchar(20), state char(2), county-id integer, tract_id integer, area double, haz_idx integer, length double, shape st_polygon inline length 4800)
  5. Issue an INSERT statement to copy the values from the external table to the new table. This statement includes a SELECT statement that uses a constructor function to convert the data from WKT format into a geometry of type ST_Geometry or one of its subtypes. For example, the following INSERT statement uses the ST_GEOMETRY constructor to insert WKT data contained in an external table into a new table with the name GEOTEST.ENVIRONMENTAL_HAZARDS:
    INSERT INTO GEOTEST.ENVIRONMENTAL_HAZARDS
SELECT OBJECTID, GEOID, STATE, STUSAB, STATE_NAME, COUNTY, COUNTY_NAM, CNTY_FIPS, TRACT, HAZ_IDX, SHAPE__ARE, SHAPE__LEN, 
ST_GEOMETRY( GEOM )
FROM EXTERNAL '/scratch/home/bluadmin/geo/data/environmental_hazards.txt.out'
(
                  OBJECTID INTEGER ,
                  GEOID VARCHAR(80 OCTETS) ,
                  STATE VARCHAR(80 OCTETS) ,
                  STUSAB VARCHAR(80 OCTETS) ,
                  STATE_NAME VARCHAR(80 OCTETS) ,
                  COUNTY VARCHAR(80 OCTETS) ,
                  COUNTY_NAM VARCHAR(80 OCTETS) ,
                  CNTY_FIPS VARCHAR(80 OCTETS) ,
                  TRACT VARCHAR(80 OCTETS) ,
                  haz_idx INTEGER ,
                  Shape__Are DOUBLE ,
                  Shape__Len DOUBLE ,
                  GEOM varchar(31000) )
USING ( DELIMITER '|' escapechar '\' maxerrors 10000 );

Method 2: Shapefiles

This method is suitable for smaller data loads, and can be used only when the shapefiles are local to the database server.
  1. Use a third-party tool to export spatial data that is stored in PureData System for Analytics tables into a shapefile.
  2. Transfer the shapefile to the target Db2 system.
  3. Use the ST_IMPORT_SHAPE procedure procedure to create a new table to hold the exported data and to load the data into that table.