ArcGIS Server Banner

ST_Geometry storage in PostgreSQL

ST_Geometry storage in PostgreSQL

Release 9.3 E-mail This TopicPrintable VersionGive Us feedback

ArcSDE for PostgreSQL supports geometry storage types—ST_Geometry and the PostGIS geometry—that provide International Organization for Standards (ISO)- and Open Geospatial Consortium, Inc. (OGC)-compliant Structured Query Language (SQL) access to the geodatabase. These storage types 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, refer to 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 to 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

For information on the PostGIS geometry type, see Using 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, 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 as follows:

st_coordinate_systems

st_units_of_measure

sde_geometry_columns

sde_spatial_references

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. Consult the ArcSDE Administration Command Reference installed with the ArcSDE component for details on its use.

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.

Top of page

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 or during registred 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 –i 1444 –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 –i 1444 –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 an 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
–t ST_GEOMETRY –R 3 –i sde:postgresql:server –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 the ArcSDE component of 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 tables to be used by ArcGIS Desktop for more information on registering tables with the geodatabase.

Tips

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 references using SQL.

Top of page

Related documentation

You may want to read further about OGC standards and PostgreSQL database functionality. This section offers some suggestions on where you can find this information.

Where to find OGC documentation

OGC is a nonprofit organization working to formalize open interface specifications for geoprocessing products and services. Its Web site—www.opengeospatial.org—will provide you with information on adopted and proposed specifications, discussion papers on technology issues, and white papers on open standards in general. OGC also maintains another site for its OGC Network—www.ogcnetwork.org. OGC Network is an online forum that brings together OGC members and the public to engage in discussion, testing, and demonstration of open geospatial specification-based capabilities. Additional OGC documentation sources include the following:

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. For technical documentation, such as detailed installation guides and security information, see http://www.postgresql.org/docs/techdocs.

Top of page

See Also

  • The ST_Geometry storage type