ST_Geometry storage in PostgreSQL

ArcSDE for PostgreSQL supports geometry storage types that provide International Organization for Standardization (ISO) and Open Geospatial Consortium, Inc. (OGC)-compliant structured query language (SQL) access to the geodatabase. These storage types are ST_Geometry and PostGIS geometry. They extend the capabilities of the PostgreSQL database by providing storage for objects (points, lines, and polygons) that represent geographic features. They allow you to integrate spatial data with other types of business data, so your multiuser database gains the advantage of adding a geographic component to your analyses and data products. Keeping your spatial data together with other business objects also simplifies multiuser access, management, and security of your data, because you will have to manage fewer data storage resources.

By default, an ArcSDE geodatabase in PostgreSQL uses the ST_Geometry spatial type. It implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. For an explanation of the ST_Geometry spatial type, see What is the ST_Geometry storage type?.

Using the ST_Geometry spatial type in your geodatabase in PostgreSQL, you can access your spatial data through SQL functions that implement the ISO SQL/MM Spatial Standard and the OGC Simple Feature Specification. You can use SQL commands to store, retrieve, and manipulate spatial features as you would any other type of data. You can use a long list of standards-based functions with SQL commands and stored procedures to retrieve and analyze spatial data. Having SQL access to the data makes it possible for you to use other applications to access data that was created in a geodatabase in PostgreSQL.

This topic describes

For information on how to work with tables that use ST_Geometry storage using SQL, see the following topics:

For information on the PostGIS geometry type, see What is the PostGIS geometry type?.

How ST_Geometry stores spatial data

The following is the description of ST_Geometry in PostgreSQL:

Name

Type

Description

size

LONG INTEGER

The total length of the ST_Geometry structure including shape buffer

srid

LONG INTEGER

Contains the identifier for the geometry that links it to its associated spatial reference (coordinate system) record in the sde_spatial_references table

numpts

LONG INTEGER

The number of points defining the geometry; for multipart geometries, this includes the separators between each part, one point for each separator.

entity

SHORT INTEGER

The type of geometric feature stored in the spatial column (linestring, multilinestring, multipoint, multipolygon, point, or polygon), the value of which is a bit mask derived from the st_geom_util stored procedure

sqltype

SHORT INTEGER

The SQL type for the shape; for example, POINT_TYPE, POINTM_TYPE, or MULTIPOLYGONZM_TYPE

minx

LFLOAT

Together with miny, maxx, and maxy, defines the spatial envelope of the geometry

miny

LFLOAT

Together with minx, maxx, and maxy, defines the spatial envelope of the geometry

maxx

LFLOAT

Together with minx, miny, and maxy, defines the spatial envelope of the geometry

maxy

LFLOAT

Together with minx, miny, and maxx, defines the spatial envelope of the geometry

minz

LFLOAT

The minimum z-value

maxz

LFLOAT

The maximum z-value

minm

LFLOAT

The minimum measure value

maxm

LFLOAT

The maximum measure value

area

LFLOAT

The area of the geometry

len

LFLOAT

The perimeter length of the geometry

shape

BYTEA

The ESRI compressed shape

Like other object types, the ST_Geometry data type contains a constructor method and functions. A constructor method is a function that returns a new instance (object) of the data type and sets up the values of its attributes.

The name of the constructor is the same as the type (ST_Geometry). When you instantiate an object of the ST_Geometry type, you invoke the constructor method, as shown in the following example:

CREATE TABLE hazardous_sites (name        varchar(128),
                              location    st_geometry);

The following are ST_Geometry accessor functions that take a single ST_Geometry as input and return the requested property value as a number:

For example, the following query returns the name and area of the individual states in the United States.

SELECT name, st_area(geometry)
FROM us_states
ORDER BY name;

ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon are all subtypes (or subclasses) of ST_Geometry. ST_Geometry and its subtypes share common attributes and functions. The constructor definition for ST_LineString, ST_MultiLineString, ST_MultiPoint, ST_MultiPolygon, ST_Point, and ST_Polygon is the same. The name of the constructor is the same as that of the type it constructs.

Metadata schema

The spatial type for PostgreSQL types and metadata tables are stored in the sde schema. The schema definition is the base table description for metadata tables used to define and describe the type column/table, spatial index, and spatial references information.

For a description of each table, see the tables listed in System tables of a geodatabase stored in PostgreSQL. The tables are st_coordinate_systems, st_units_of_measure, sde_geometry_columns, sde_spatial_references, and sde_coordinate_system.

Creating feature classes with ST_Geometry storage using ArcGIS

When you first install ArcSDE for PostgreSQL, ST_Geometry is the default geometry storage type when creating feature classes through ArcGIS Desktop or the sdelayer command. The default settings for ArcSDE storage are defined in the sde_dbtune table by the GEOMETRY_STORAGE parameter of the DEFAULTS configuration keyword. If you do not alter the GEOMETRY_STORAGE parameter value of the DEFAULTS keyword, all your feature classes will be created using ST_Geometry storage unless you specify a different configuration keyword that has GEOMETRY_STORAGE set to PG_GEOMETRY when creating the feature class.

If you decide to change the DEFAULTS GEOMETRY_STORAGE parameter to PG_GEOMETRY, which indicates the spatial types used by PostGIS, you could create a new configuration keyword for ST_Geometry storage. For example, you could create a configuration keyword as follows:

##ST_GEOMETRY
GEOMETRY_STORAGE    "ST_GEOMETRY"
UI_TEXT   "User-interface for ST_GEOMETRY keyword"

END

You use the sdedbtune command to alter or add values to the sde_dbtune table. See Altering the contents of the DBTUNE table after it is created for instructions.

The PostGIS storage type and the ST_Geometry storage type can be used together in the same database. While there can be only one default geometry storage type, individual tables can be created using different geometry storage types. Therefore, if you change your default GEOMETRY_STORAGE to PG_GEOMETRY, you could still create some of your feature classes using the ST_GEOMETRY storage type by specifying a keyword similar to the one shown above.

When you create a feature class that uses the GEOMETRY_STORAGE of ST_GEOMETRY, the business table of the feature class is created with a column of type ST_Geometry in which spatial data for the feature class is stored.

Using existing PostgreSQL tables with ST_Geometry columns

If you use SQL to create a table with an ST_Geometry column, you can register the table with ArcSDE, provided the following criteria are met:

You must also have an integer, unique, not-NULL column that can be used as a row ID. This column can be added to the table before being registered with ArcSDE, or it can be added during registration.

In addition, the table should have a spatial index. This can be added using SQL prior to or after registering the table with ArcSDE.

Create a table with an ST_Geometry column

In the following example, a table, blocks, is created with an ST_Geometry column, shape, using SQL.

CREATE TABLE sasha.blocks 
(objectid integer NOT NULL, block varchar(4), res smallint, shape st_geometry)
WITHOUT OIDS;

Create a spatial index on the table

ArcSDE for PostgreSQL uses a Generalized Search Tree (GiST) index. Use a CREATE INDEX statement with the gist access method and st_geometry_ops operator class to create an index on the ST_Geometry column of a table. For example:

CREATE INDEX blockssp_idx ON blocks
USING gist(zone st_geometry_ops);

Register the table with ArcSDE

Once the table with the ST_Geometry column exists and has a spatial index, use the sdelayer –o register command to register the table with ArcSDE. When you register a table with ArcSDE, a record is added for the table in the sde_layers, sde_table_registry, and sde_geometry_columns system tables. Records are also added to the sde_column_registry system table for each column in the table.

sdelayer –o register –l blocks,shape 
–e a –C objectid,SDE –R 5 –t ST_GEOMETRY – s myserver
–i sde:postgresql:myserver –D mycitydb –u sasha –p super1

For tables that contain a large number of records, registration may take less time if you register the row ID column as user maintained.

sdelayer –o register –l blocks,shape 
–e a –C objectid,USER –t ST_GEOMETRY –s myserver 
–i sde:postgresql:myserver –D mycitydb –u sasha –p super1

However, if you register the feature identifier column as user maintained and subsequently register the feature class with the geodatabase, ArcGIS will add an additional feature identifier column, object_ID. ArcGIS will maintain the values of this column. If the table contains a large number of records, adding this additional object_ID column may take some time.

Provided you registered your spatial column and specified a spatial reference ID (SRID) for the table using the st_register_spatial_column function, you do not need to specify an SRID with the sdelayer command. However, if you did not already assign an SRID to the table, you must specify a valid SRID with the –R option, as shown in the following example:

sdelayer –o register –l infestations,site 
–e p+ –C siteid,SDE –s myserver –t ST_GEOMETRY –R 3 
–i sde:postgresql:myserver –D meddb –u blorca –p it.s.me

As mentioned earlier in this topic, if the spatial reference you need does not exist in the sde_spatial_references table, create a template feature class in ArcCatalog that uses the spatial reference you want, then query the sde_spatial_references table to see what SRID was assigned to it. That is the number you use when registering other spatial tables in the same coordinate reference system.

If you did not register the spatial column with a SRID using the st_register_spatial_column function, and you do not specify one with the –R option when you register the table with ArcSDE, the SRID of the first record will be used. If you did not register the spatial column with a SRID using the st_register_spatial_column function and the table is empty, an SRID of 0 will be used. The 0 SRID exists for sample and documentation purposes only and you should not use it for production data. The specifications for this SRID are as follows:

falsex

- 400

falsey

- 400

xyunits

1000000000

falsez

- 100000

zunits

10000

falsem

- 100000

munits

10000

xycluster_tol

0.000000008983153

zcluster_tol

.001

mcluster_tol

.001

srtext

GEOGCS["GCS_WGS_1984",DATUM["D_WGS_1984",SPHEROID["WGS_1984",6378137.0,298.257223563]], PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]

cs_id

4326

organization

EPSG

For complete syntax and a description of the sdelayer command, consult the ArcSDE Administration Command Reference provided with ArcGIS Server Enterprise.

The sdelayer command only adds the table to the ArcSDE system tables. To be able to use ArcGIS Desktop geodatabase functionality such as topology, versioning, and networks, you must also register the table with the geodatabase. See Registering a table with the geodatabase and Registering a third-party table containing a spatial column for more information.

Tips

  • You can register the spatial column in a table that has an ST_Geometry column by executing the sde.st_register_spatial_column function. This will assign the SRID for the table and thereby prevent users from specifying other SRIDs when inserting data via SQL.
    SELECT st_register_spatial_column( '<database_name>', 
'<schema_name>', '<table_name>', '<spatial_column_name>', <srid>)
    To register the shape column of the blocks table and assign it an SRID, execute the following statement:
    SELECT st_register_spatial_column(
'mycitydb', 'sasha', 'blocks', 'shape', 4)
    This adds the spatial column to the sde_geometry_columns table in the geodatabase. The SRID you specify (4 in the preceding example) must exist in the sde_spatial_references table. If the spatial reference you want is not in the sde_spatial_references table, you can open ArcCatalog, connect to the geodatabase, and create a new feature class that uses the spatial reference system you want and ST_Geometry storage. When you do this, the sde_spatial_references table is populated with the spatial reference you need, and ArcCatalog calculates the x,y, z, and m- offsets and units and the layer extent for you. It is also possible to use SQL to insert the spatial reference, but you must be sure to calculate the correct x,y, z, and m- offsets and units yourself. Be aware, though, that to register the table with ArcSDE using the sdelayer command, you must unregister the ST_Geometry column. For example, you might create a table, and other users will perform inserts to the table using SQL. To enforce the use of a single SRID, you could register the ST_Geometry column with the SRID you want. Then, when you decide to register the table with ArcSDE, execute the st_unregister_spatial_column function. (See the next tip.)
  • You can unregister a spatial column by executing the st_unregister_spatial_column() function. This function unregisters a spatial column by removing the spatial column from the sde_geometry_columns system table so the spatial column is no longer associated with any spatial reference system.
    SELECT st_unregister_spatial_column(
'<database_name>', '<schema_name>',
 '<table_name>', '<column_name>')
    You would do this before you register the table with ArcSDE, or you might do this if you want to register an empty spatial column with a different SRID; you could unregister it, then reregister it with a different SRID using the st_register_spatial_column function.
  • You can check to see whether a spatial column is registered by executing the st_isregistered_spatial_column function.
    SELECT st_isregistered_spatial_column(
'<database_name>', '<schema_name>',
 '<table_name>', '<column_name>', <srid>)
    This returns 1 if the spatial column is registered and 0 if it is not.

Records in the sde_coordinate_systems table

The sde_coordinate_systems table in the SDE schema stores the ID and description of the coordinate systems used in the database. It is fully populated with the coordinate systems when the geodatabase is created.

You can use the coordinate systems in this table for the feature classes you create through ArcGIS Desktop or SQL.

In addition to assigning a coordinate system, you must create a spatial reference. ArcGIS Desktop does this as part of the feature class creation. To create a spatial reference for tables using SQL, see Creating spatial indexes on tables with an ST_Geometry column.

Where to find PostgreSQL documentation

You can find PostgreSQL documentation at the PostgreSQL Web site at http://www.postgresql.org/docs/. This documentation includes information on installing and administering PostgreSQL, SQL commands and programming, and tutorials on using PostgreSQL.


Copyright © 1995-2013 Esri. All rights reserved.
11/18/2013