Insert 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, PostgreSQL, or SQLite 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 SQLite database. The ST_Geometry function is used to convert 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 in Oracle. The ST_Polygon function is used to convert the well-known text representation of a polygon into an ST_Polygon before inserting it into the ZONE column of the sensitive_areas table in PostgreSQL and SQLite. Likewise, the ST_Geometry function converts the well-known text representation of a point into an ST_Point type before inserting it into the LOCATION column of the hazardous_sites table in Oracle, and the ST_Point function is used to convert the well-known text representation of a point into an ST_Point before inserting it into the LOCATION column of the hazardous_sites table in PostgreSQL and SQLite . You can also enter data in well-known binary format using the ST_PolyFromWKB() and ST_PointFromWKB() functions.

Steps:

1. Log in to an 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))', 4326)
     );
     
     INSERT INTO HAZARDOUS_SITES (row_id, site_id, name, location) VALUES (
      1,
      102,
      'W. H. Kleenare Chemical Repository',
      sde.st_geometry('point (52 24)', 4326)
     );
     

   • 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))', 4326)
     );
     
     INSERT INTO hazardous_sites (row_id, site_id, name, location) VALUES (
      1,
      102,
      'W. H. Kleenare Chemical Repository',
      ST_Point('point (52 24)', 4326
     );
     

   • SQLite

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