Loading spatial data

After you prepare for spatial data, you can load data into the spatial table. The method for loading depends on the type and amount of data. Some loading methods automatically create an association between the spatial table and a spatial reference system in the geometry_columns table and create a spatial index. Otherwise, you must create the association and the index manually.

You can use the following data exchange formats for your spatial data:
  • OGC well-known text representation (WKT)
  • OGC well-known binary representation (WKB)
  • ESRI shapefile format
  • Geography Markup Language (GML)
  • Keyhole Markup Language (KML)

These data exchange formats require input and output conversion functions to insert spatial data into, and retrieve data from, a database. Each data exchange format has a set of functions to convert data into its stored data types.

You can also use the HCL® OneDB® load format, which does not require conversion functions.

The actual amount of data that is loaded into a GIS system usually ranges between 10 000 records for smaller systems and 100 000 000 records for larger systems. You have several options for loading large amounts of data. If you are loading data in bulk, you can avoid large numbers of log records by temporarily turning off logging for the database until the data is loaded.

Loading ESRI shapefiles for ESRI clients

To load ESRI shapefiles that can be accessed by ESRI SDE clients, acquire data from a vendor and load the data through the ESRI SDE server by running the ESRI shp2sde command. Accessing spatial tables through SDE software provides immediate access to SDE client software such as ArcView GIS, MapObjects, ARC/INFO, and ArcExplorer. MicroStation and AutoCAD are also accessible through SDE CAD client software.

The shp2sde command automatically updates the geometry_columns table and creates the spatial index.

Loading ESRI shapefiles for non-ESRI clients

To load ESRI shapefiles for clients other than ESRI clients, run the loadshp utility. A companion utility, unloadshp, unloads data from a database to shapefiles. Data that is loaded by the loadshp utility is also accessible to client programs that do not depend on ESRI system tables other than the OGC-standard geometry_columns and spatial_references tables. Data that you load with the loadshp utility is not accessible to ArcSDE and other ESRI client tools. You can optionally create the spatial table when you run the loadshp utility.

The loadshp utility automatically updates the geometry_columns table and creates the spatial index.

Loading any type of geometry format

To load any type of geometry format:

  1. Load the spatial data with one of the following methods:
    • INSERT statements that load rows individually.
    • Develop your own loader application. The source code for two sample programs, load_wkb and load_shapes, is supplied with the HCL OneDB software. These programs illustrate how to convert data into OGC well-known binary and ESRI shapefile formats. The programs can be modified and linked into existing applications. They are located under the $ONEDB_HOME/extend/spatial.version/examples directory. ESQL/C and ODBC versions of both programs are provided.
  2. Create an association between the spatial table and a spatial reference system by updating the geometry_columns table.
  3. Create a spatial index on the spatial table by running the CREATE INDEX statement with the USING RTREE clause.

Examples: Load individual rows

You can load individual records into the spatial table with an INSERT statement.

You can convert the well-known text representation of a polygon into an ST_Polygon type by running the ST_PolyFromText() function. You can convert the well-known text representation of a point into an ST_Point type by running the ST_PointFromText() function.

For example, in the following SQL statements, records are inserted into the sensitive_areas and hazardous_sites table:
INSERT INTO sensitive_areas VALUES (
    1, 408, 'Summerhill Elementary School', 67920.64, 'school',
    ST_PolyFromText('polygon ((52000 28000,58000 28000,58000 23000,
52000 23000,52000 28000))',5)
);

INSERT INTO hazardous_sites VALUES (
    1, 102, 'W. H. Kleenare Chemical Repository',
    ST_PointFromText('point (17000 57000)',5)
);
You can use the HCL OneDB load format, which does not require a conversion function. The following example inserts a row in the sensitive_areas table and the hazardous_sites table:
INSERT INTO sensitive_areas VALUES (
    2, 129, 'Johnson County Hospital', 102281.91, 'hospital',
    '5 polygon ((32000 55000,32000 68000,38000 68000,38000 52000,
35000 52000,35000 55000,32000 55000))'
);

INSERT INTO hazardous_sites VALUES (
    2, 59, 'Landmark Industrial',
    '5 point (58000 49000)'
);