ArcGIS Server Banner

Inserting features to a table with an ST_Geometry column

Inserting features to a table with an ST_Geometry column
Release 9.3 E-mail This TopicPrintable VersionGive Us feedback
Note: This topic was updated for 9.3.1.

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

All of 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 shape formats. See An overview of SQL functions used with ST_Geometry types for a complete list of the functions provided.

For example, the SQL statements below insert records into the sensitive_areas and hazardous_sites tables. 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 LOCATION 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.".

Oracle

INSERT INTO sensitive_areas 
VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', sde.ST_PolyFromText('polygon ((52 28,58 28,58 23,52 23,52 28))', 1));

INSERT INTO hazardous_sites 
VALUES (1, 102, 'W. H. Kleenare Chemical Repository', sde.ST_PointFromText('point (52 24)',1));

PostgreSQL

INSERT INTO sensitive_areas 
VALUES (1, 'Summerhill Elementary School', 67920.64, 'school', ST_Polygon('polygon ((52 28, 58 28, 58 23, 52 23, 52 28))', 1));

INSERT INTO hazardous_sites 
VALUES (1, 102, 'W. H. Kleenare Chemical Repository', ST_Point('point (52 24)', 1);

DB2

INSERT INTO sensitive_areas 
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 
VALUES (1, 102, 'W. H. Kleenare Chemical Repository', db2gse.ST_PointFromText('point (52 24)', 1))

In the simple example above, a few records are inserted into database tables. However, the actual amount of data loaded into a GIS usually ranges from thousands of records for smaller systems to millions of records for larger systems.

Validating data on write

Geometry validation is provided by the spatial type in Oracle and PostgreSQL through the ST_shapelib library. Once a table is created, data is validated on inserts or updates. For example, the following code creates a table in Oracle with a spatial column of type ST_Polygon. An attempt is then made to insert an invalid polygon value with coordinates that do not match the spatial reference ID (SRID) provided.

The table needs to exist first. The insert fails with a message similar to the ones below if the extents are outside the bounds.

insert into testpolypt values (sde.st_polygon('polygon ((10000 520000,1
00008889 55000, 1045545983 234280934, 10000 520000))', 9));

Oracle

insert into testpolypt values (sde.st_polygon('polygon ((10000 520000,100008
889 55000, 1045545983 234280934, 10000 520000))', 9))
                               *
ERROR at line 1:
ORA-20004: Error generating shape from text (-112)
ORA-06512: at "SDE.ST_GEOMETRY_SHAPELIB_PKG", line 42
ORA-06512: at "SDE.ST_POLYGON", line 69
ORA-06512: at line 1

The same error messages for the other databases are as follows:

PostgreSQL

ERROR:  ESRI: error generating shape [-112]:

Informix

SQL Error (-937): Coordinates out of bounds in ST_PolyFromText.

DB2

GSE3416N Coordinate out of bounds