ArcGIS and the PostGIS geometry type
PostGIS is a product from Refractions Research that can spatially enable PostgreSQL databases. PostGIS follows the Open Geospatial Consortium, Inc., Simple Features specification for SQL.
When you use the PostGIS geometry storage type with ArcGIS, keep the following rules in mind:
- You must use the PostGIS database template to create the PostgreSQL database you use for your geodatabase or enable PostGIS in the database. See the section in this topic "Preparing your database to use PostGIS geometry."
- The sde user and any user who creates data in the geodatabase must be granted permissions on specific PostGIS tables. See the section "Granting users rights to create tables with PostGIS geometry columns."
- Feature classes that you create can only use the spatial references listed in the PostGIS public.spatial_ref_sys table. If you specify one that is not in that table, feature class creation fails.
- You must specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY to create a feature class that uses the PostGIS geometry type.
- You cannot rename spatial tables stored using the PostGIS geometry type. This is because there is no PostGIS function to update the table name in the public.geometry_columns table.
The following sections contain more information on how to use the PostGIS geometry storage type with ArcGIS applications.
Preparing your database to use PostGIS geometry
To use the PostGIS geometry type, you must install PostGIS. Install PostGIS after you install PostgreSQL. Be sure to install a version of PostGIS supported by the ArcGIS release you want to use.
Tip:See the database system requirements page on the ArcGIS Resource Center to find out which version of PostGIS is supported with the ArcGIS release you are using.
When you install PostGIS, a PostGIS template database is created in the PostgreSQL database cluster. Use the PostGIS template database to create a database in which to store your geodatabase.
Note:If you create your geodatabase using the Create Enterprise Geodatabase geoprocessing tool, your database is created using a template other than the PostGIS template. Therefore, if you want to use the PostGIS geometry type, you must create a database manually using the PostGIS template. Then you can specify your existing database when you run the Create Enterprise Geodatabase geoprocessing tool, and the geodatabase will be created in it.
Granting users rights to create tables with PostGIS geometry columns
When a database is enabled for PostGIS, two tables—geometry_columns and spatial_ref_sys—are created in the public schema. You must grant, at a minimum, SELECT, INSERT, UPDATE, and DELETE privileges on the geometry_columns table and SELECT on the spatial_ref_sys table to the sde user and any users who will create data in the geodatabase.
GRANT select, insert, update, delete
ON TABLE public.geometry_columns
TO <user_name>;
GRANT select
ON TABLE public.spatial_ref_sys
TO <user_name>;
Creating feature classes in a geodatabase in PostgreSQL using PostGIS geometry storage
Once the database is enabled to store PostGIS geometry, you can create spatially enabled tables that include spatial columns of type geometry. Geographic features can be inserted into the spatial columns.
You can access the spatially enabled tables through ArcGIS for Desktop or by creating applications using the ArcSDE C application programming interface (API). If you are an experienced SQL programmer, you can also make calls to the spatial functions.
Creating feature classes using ArcGIS
The geometry storage type used for feature classes created using ArcGIS is controlled by a parameter setting in the sde_dbtune table. This parameter is GEOMETRY_STORAGE. In geodatabases in PostgreSQL, this can be set to either ST_GEOMETRY or PG_GEOMETRY (the setting for the PostGIS geometry type). Therefore, when you want to create a feature class using ArcGIS that uses the PostGIS geometry type, specify a configuration keyword that contains the GEOMETRY_STORAGE parameter set to PG_GEOMETRY.
By default, the geometry storage type for new feature classes in an ArcSDE geodatabase for PostgreSQL uses ST_Geometry storage. If you want to store most of your data in PostGIS storage types, alter the GEOMETRY_STORAGE parameter value under the DEFAULTS keyword in the sde_dbtune table to PG_GEOMETRY. (Use the sdedbtune command to alter parameters in the sde_dbtune table. See the ArcSDE Administration Command Reference for details on using this command.) Or, if you want to store just some of your feature classes in the PostGIS storage type, you can use the PG_GEOMETRY configuration keyword and specify that keyword when you create your feature class. In the dbtune.sde file, which can be created by exporting the contents of the sde_dbtune table using the sdedbtune command, the PG_GEOMETRY keyword appears as follows:
##PG_GEOMETRY GEOMETRY_STORAGE "PG_GEOMETRY" UI_TEXT "User Interface text description for POSTGIS geometry storage" END
The rest of the storage parameters are picked up from the DEFAULTS keyword. For more information on sde_dbtune storage, see the following topics:
Using existing PostGIS tables
ArcGIS can use tables containing PostGIS geometry columns created externally by other applications or using SQL (also referred to as third-party tables) as long as the tables meet the following prerequisites:
- Each table must have a single geometry column. If it does not, define a query layer or view that includes only one of the geometry columns.
- The tables must contain no other columns of a user-defined type.
- Tables must have a single type of geometry (points, lines, or polygons), though geometry can be multipart.
- Each table must have an integer, unique, not-NULL column suitable as a registered row ID column.
- Each table should have a spatial index.
For information on creating tables with a PostGIS column using SQL, see the PostGIS documentation.
Registering third-party tables containing PostGIS geometry columns
You can connect to a PostgreSQL database that contains PostGIS tables from ArcGIS for Desktop and register them with the geodatabase. See Registering a table with the geodatabase for more information.
Where to find PostGIS and PostgreSQL documentation
You can find PostGIS documentation on the PostGIS website: http://www.postgis.org/documentation/.
For general PostgreSQL information, there is documentation on the PostgreSQL website: http://www.postgresql.org/docs/.