Importing spatial data from a shapefile

You can import spatial data that is stored in a shapefile by transferring the shapefile to your database server and then using a stored procedure to extract and load that data. For example, a map vendor might provide a shapefile containing spatial data about cities, streets, and points of interest that you can import into your database and use.

Before you begin

  • Note that a shapefile is actually a collection of files with the same file name but different file extensions (.shp, .shx, .dbf, and others) that, together, constitute a load unit.
  • The shapefile that contains the data to be imported must be located on the same server as the database.
  • Ensure that your user ID has the necessary authorities or privileges to issue the INSERT statement for the target table.

Procedure

To import spatial data from a shapefile:

  1. Use the ST_SHAPEFILE_INFO procedure to retrieve information about the shapefile. This information includes, among other things, whether a coordinate system and spatial reference system (SRS) that match the geometry data already exist in the database.
    1. If a coordinate system does not already exists in the database, create one as described in Determine what coordinate system to use
    2. If an SRS does not already exists in the database, create one as described in Creating a spatial reference system.
    call ST_SHAPEFILE_INFO('<installdir>/sqllib/samples/extenders/spatial/data/offices',?,?,?)
  2. Use the ST_IMPORT_SHAPE procedure to import the contents of a shapefile into a relational database table. 
    call ST_IMPORT_SHAPE('<installdir>/sqllib/samples/extenders/spatial/data/offices',NULL,'MYSRS',NULL,'MyTable',NULL,1,NULL,'MyColumnName',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'/home/dir3/import_msg2',?,?)