Loading spatial data

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.

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)
);

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)'
);