ST_Geometry in PostgreSQL

The ST_Geometry spatial data type can be used in PostgreSQL databases that contain a geodatabase and those that do not. The ST_Geometry data type allows 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 then manage fewer data storage resources.

Geodatabases in PostgreSQL use the ST_Geometry spatial type by default, but you must copy the st_geometry library to the PostgreSQL installation before you create a geodatabase. For instructions, see Create a geodatabase in PostgreSQL on Windows or Create a geodatabase in PostgreSQL on Linux. If you use a PostgreSQL database that does not contain a geodatabase, you can install the ST_Geometry type for use in spatial tables you create in the database.

To help you understand how the ST_Geometry type is used in PostgreSQL, this topic describes the following:

• The architecture of the ST_Geometry spatial type in PostgreSQL
• How to create feature classes in ArcGIS that use ST_Geometry storage
• How to access PostgreSQL tables that contain ST_Geometry columns with ArcGIS
• How to register an ST_Geometry column to restrict the spatial reference that can be used

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

• Create tables with an ST_Geometry column
• Insert features to a table with an ST_Geometry column
• Spatial SQL queries on tables with an ST_Geometry column
• Update values in an ST_Geometry spatial column
• Use spatial views on tables with an ST_Geometry column

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)
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 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:

• The ST_Area member function returns the area of a geometry.
• ST_Length returns the length of a geometry.
• ST_Entity returns a number containing a bit mask that describes the entity type.
• ST_NumPoints returns the number of points (vertices) that define a geometry.
• ST_MinM, ST_MinX, ST_MinY, and ST_MinZ return the minimum desired coordinate of a geometry.
• ST_MaxM, ST_MaxX, ST_MaxY, and ST_MaxZ return the maximum desired coordinate of a geometry.
• ST_SRID returns the spatial reference identifier for a geometry.

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 functions, tables, and views 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 reference information.

For a description of each table and view, see System tables of a geodatabase in PostgreSQL. The tables are sde_geometry_columns and sde_coordinate_systems. The views are st_geometry_columns and st_spatial_references, and are based on these tables.

Dive-in:

In addition to the system tables, views, and functions, the following database objects are used to maintain ST_Geometry metadata:

• An sde login role
• An sde schema in the database
• An ST_Geometry trigger: sde_coord_sys_def_insert_tg
• ST_Geometry domains:
  • st_geomcollection
  • st_linestring
  • st_multilinestring
  • st_multipoint
  • st_multipolygon
  • st_point
  • st_polygon

Use ArcGIS to create feature classes with ST_Geometry storage

You choose which storage type to use when creating feature classes through ArcGIS Desktop.

When you create a feature class in ArcGIS that uses ST_Geometry storage, 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.

In a database

You specify the spatial data type to use when a feature class is created in ArcGIS. For more information, see Create a feature class in a database in ArcMap.

In a geodatabase

Feature class storage information is controlled by configuration keyword settings in the sde_dbtune table. You specify a configuration keyword when you create a feature class in ArcGIS. The DEFAULTS configuration keyword has the GEOMETRY_STORAGE parameter set to ST_Geometry when the geodatabase is created. If you want to store all or most of your spatial data using the ST_Geometry type, do not alter the GEOMETRY_COLUMNS parameter value of the DEFAULTS keyword, then when you create a feature class from ArcGIS, specify the DEFAULTS keyword.

If you change the DEFAULTS GEOMETRY_STORAGE parameter to use the PostGIS geometry data type but want to create some feature classes using the ST_Geometry data type, you could create a new configuration keyword for ST_Geometry storage in the sde_dbtune table. Use the sdedbtune administration command to export the contents of the sde_dbtune table to a text file, add a keyword that has GEOMETRY_STORAGE set to ST_GEOMETRY, then use sdedbtune to import your changes. For example, you could export the sde_dbtune table and add a configuration keyword as follows:

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

END

For more information, see Alter configuration keywords.

Once the keyword has been added, you can specify it when you create a feature class in ArcGIS so that your new feature class will use ST_Geometry storage.

Use ArcGIS to access PostgreSQL tables with ST_Geometry columns

If you use SQL to create a table with an ST_Geometry column, you can access the data through SQL, custom third-party applications, and ArcGIS. When you connect to the database from ArcGIS, you can view, perform analyses, or load data into tables that contain an ST_Geometry column. To do this, the following criteria must be met:

• The table must have a single ST_Geometry column.
• The table must have no other columns of a user-defined type.
• If the table stores multiple types of geometry (points, lines, and polygons), you must specify which geometry types you want to view; only one type at a time can be viewed in ArcGIS.
• All records in the table must use the same spatial reference.

If you created the table in a geodatabase using SQL, you can register the table with the geodatabase if you want to use geodatabase functionality (such as replication, networks, relationship classes, and topology) or you want to edit the table in ArcGIS. To register it with the geodatabase, the following criteria must be met:

• The table must be owned by the user registering it.
• It must have a single ST_Geometry column.
• It must have no other columns of a user-defined type.
• It must have a single type of geometry (points, lines, or polygons).
• All records in the table must use the same spatial reference.
• You must also have an integer, unique, not-NULL column that can be used as an ObjectID.

Register the ST_Geometry column

If you use SQL to create a table that contains an ST_Geometry column, you can register the column to use a specific spatial reference and dimensionality. That way, when you insert records through SQL, you cannot accidentally insert records that use a different spatial reference. To do this, use the sde.st_register_spatial_column function. The syntax for using this function is as follows:

SELECT st_register_spatial_column('<database_name>', '<schema_name>', 
'<table_name>', '<spatial_column_name>', <srid>, <coordinate_dimension>)

The SRID you specify must exist in the public.sde_spatial_references table. The coordinate dimension indicates whether the data has only x,y coordinates (2), x,y,z coordinates (3), x,y,z,m coordinates (4), or x,y,m coordinates (5). By default, if you do not specify a coordinate dimension, the data is registered as having only x,y dimensions.

In the following example, the shape column of the blocks table in the sasha schema of database mycitydb is registered to use an SRID of 4236 and store only three-dimensional coordinates:

SELECT st_register_spatial_column(
'mycitydb', 'sasha', 'blocks', 'shape', 4236, 3);

This adds a record for the spatial column to the public.sde_geometry_columns table in the geodatabase or database.

If the spatial column is empty and you register it with a particular SRID and dimensionality, you could unregister it to change the SRID or dimensionality then reregister it with different values. You can unregister a spatial column by executing the st_unregister_spatial_column() function. This function removes the spatial column from the public.sde_geometry_columns system table so the spatial column is no longer associated with any spatial reference system. The syntax for using this function is as follows:

SELECT st_unregister_spatial_column(
'<database_name>', '<schema_name>',
 '<table_name>', '<column_name>')

You can check to see whether a spatial column is registered by executing the st_isregistered_spatial_column function. The syntax to use this function is as follows:

SELECT st_isregistered_spatial_column(
'<database_name>', '<schema_name>',
 '<table_name>', '<column_name>', <srid>)

If the spatial column is registered with the specified SRID, 1 is returned; a 0 is returned if it is not.

To discover what dimensionality the table was registered with, use the st_get_coord_dimension function. The syntax for the st_get_coord_dimension function is as follows:

SELECT st_get_coord_dimension(
'<schema_name>', '<table_name>', '<column_name>', <srid>)

In this example, st_get_coord_dimension returns xyz since the blocks table is registered as three-dimensional:

SELECT st_get_coord_dimension(
'sasha', 'blocks', 'shape', 4236);

st_get_coord_dimension
---------------------------
xyz