Inserting features to a table with an ST_Geometry column

Use the SQL INSERT statement and ST_Geometry functions to insert records to a table with an ST_Geometry column.

Data to be inserted into an ST_Geometry column can be expressed in one of these geometry formats:

• Open Geospatial Consortium, Inc. (OGC), well-known text (WKT) representation
• OGC well-known binary (WKB) representation

These formats require the use of input and output conversion functions to insert spatial data into and retrieve data from a database. Functions are provided to convert data from each of these external formats into the stored data types in an Oracle or PostgreSQL database. For PostgreSQL, functions are also provided to convert Esri shapefile formats.

In the following example, a few records are inserted into database table (sensitive_areas and hazardous_sites) in an Oracle, PostgreSQL, and DB2 database. The ST_PolyFromText() function (use ST_Polygon in PostgreSQL) converts the well-known text representation of a polygon into an ST_Polygon type before inserting it into the ZONE column of the sensitive_areas table. Likewise, the ST_PointFromText() function (use ST_Point in PostgreSQL) converts the well-known text representation of a point into an ST_Point type before inserting it into the LOC column of the hazardous_sites table. You can also enter data in well-known binary format using the ST_PolyFromWKB() and ST_PointFromWKB() functions.

Note:

When using spatial type functions in DB2, you must qualify the functions with db2gse.

Steps:

1. Log in to a SQL editor, connecting to the database that contains the tables you want to insert data to and as a user with privileges to insert data to the tables.
2. Use the SQL INSERT statement to add a record to the sensitive_areas and hazardous_sites tables.
   • Oracle

     INSERT INTO SENSITIVE_AREAS (area_id, name, area_size, type, zone)
     VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', sde.st_geometry('polygon ((52 28,58 28,58 23,52 23,52 28))', 0));
     
     INSERT INTO HAZARDOUS_SITES (row_id, site_id, name, loc) 
     VALUES (1, 102, 'W. H. Kleenare Chemical Repository', sde.st_geometry('point (52 24)',0));
     

   • PostgreSQL

     INSERT INTO sensitive_areas (area_id, name, area_size, type, zone)
     VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', ST_Polygon('polygon ((52 28, 58 28, 58 23, 52 23, 52 28))', 0));
     
     INSERT INTO hazardous_sites (row_id, site_id, name, loc)
     VALUES (1, 102, 'W. H. Kleenare Chemical Repository', ST_Point('point (52 24)', 0);
     

   • DB2

     INSERT INTO SENSITIVE_AREAS (area_id, name, area_size, type, zone)
     VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', db2gse.ST_PolyFromText('polygon ((52 28,58 28,58 23,52 23,52 28))', 1))
     
     INSERT INTO HAZARDOUS_SITES (row_id, site_id, name, loc)
     VALUES (1, 102, 'W. H. Kleenare Chemical Repository', db2gse.ST_PointFromText('point (52 24)', 1))