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:

• The ST_Area member function returns the area of a geometry.
• ST_Len 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 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

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

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:

• 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), although the geometry can be multipart (multipoint, multilinestring, multipolygon).
• 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 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

• 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 spatial reference ID (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.

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

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:

• Simple Features specification
The OpenGIS Simple Features specification for SQL can be found at www.opengeospatial.org/docs/99-049.pdf. The purpose of this specification is to define a standard SQL schema that supports storage, retrieval, query, and update of simple geospatial feature collections via the ODBC API. A simple feature is defined by the OpenGIS Abstract specification to have both spatial and nonspatial attributes. Spatial attributes are geometry valued, and simple features are based on 2D geometry with linear interpolation between vertices.
• Conformance testing
The spatial type for PostgreSQL will be tested with the Conformance Test Guidelines for OpenGIS Simple Features Specification for SQL before final release. Information on conformance testing can be found at www.opengeospatial.org/resource/testing?highlight=testing.
• OGC conformance test scripts
The test suite software is provided by OGC in two ways for each test—as a set of SQL scripts and as embedded C programming language source code. OGC recognizes that this test suite software will probably have to be adapted to work with software products seeking conformance. Organizations whose products are being tested may select either form of test suite for adaptation and testing. The spatial type for PostgreSQL has been tested using the OpenGIS Simple Features Specification for SQL Compliance Testing suite located at www.opengeospatial.org/resources/?page=testing&view=sfsql.

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.

• User groups
There are many PostgreSQL user groups throughout the world. You can find a list of some at http://pugs.postgresql.org/.
• Case studies
You can find links to case studies of sites that use PostgreSQL at http://www.postgresql.org/about/casestudies/.
• Weekly news
A community newsletter is put out every week detailing new developments and information related to PostgreSQL: http://www.postgresql.org/community/weeklynews/.

Top of page