When you create a table or add a column to a table in a database, you define a specific data type for the column. Data types determine the following:
- What values you can store in the column
- What operations you can use on the data in that column
- How the data in that column is stored in the database
ArcGIS works with specific data types. When you access a database table through a Database Connection or a query layer, ArcGIS filters out any unsupported data types. ArcGIS will not display unsupported data types and you cannot edit them through ArcGIS. Similarly, when you use ArcGIS to copy and paste tables containing unsupported data types from one database to another, ArcGIS only pastes columns that use a supported data type.
The first column in the following table lists the ArcGIS data types. The second column lists the PostgreSQL data type that ArcGIS creates. The third column shows what other PostgreSQL data types (if any) map to the ArcGIS data type when you view a table that you created outside ArcGIS. The last column provides additional information when needed.
ArcGIS data types | PostgreSQL data types created | Other PostgreSQL data types that can be viewed | Notes |
---|---|---|---|
BLOB | BYTEA | ||
DATE | TIMESTAMP WITHOUT ZONE | TIMESTAMP | |
DOUBLE | NUMERIC(p) | BIG SERIAL, DOUBLE PRECISION | The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. |
FLOAT | NUMERIC(p) | The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. | |
GEOMETRY | ST_GEOMETRY or GEOMETRY (PostGIS) | In a geodatabase, the GEOMETRY_STORAGE setting of the configuration keyword used when creating the feature class determines which data type is created in the database. To use ST_Geometry in a database (not geodatabase) , you must install it. See Add the ST_Geometry type to a PostgreSQL database for information. To use the PostGIS geometry type, you must install PostGIS in your PostgreSQL database cluster, and the database itself must be enabled to use PostGIS. See your PostgreSQL documentation for more information. | |
GLOBAL ID | VARCHAR(38) | Only supported in geodatabases. | |
GUID | VARCHAR(38) | ||
LONG INTEGER | INTEGER | SERIAL | |
OBJECT ID | INTEGER in a geodatabase SERIAL in a database | The ArcGIS type ObjectID is the registered row ID column for the table (or feature class). Only one may exist per table. | |
RASTER | BYTEA or ST_RASTER | Rasters are supported only in geodatabases. Which PostgreSQL data type is created depends on the configuration keyword used when creating the raster catalog, raster dataset, or mosaic dataset. You must separately configure ST_Raster in the geodatabase if you want to use it. See Install ST_Raster in PostgreSQL for instructions. | |
SHORT INTEGER | SMALLINT | REAL | |
TEXT | CHARACTER VARYING | CHARACTER, VARCHAR, TEXT |
If your table contains a column with a data type not supported in ArcGIS, you can cast the column to text. However, only do this if you just want to see the values in the column; do not do this if you need to perform any analysis that uses the values in that column. For example, you could execute a SELECT statement to choose the columns in tableb and cast the decimal column (total) to text:
SELECT id, name, total::text
FROM me.mydb.tableb;
Geometry data types
As indicated in the table, ArcGIS creates and can work with two geometry data types in PostgreSQL: Esri ST_Geometry or PostGIS Geometry. The next two sections provide more background on these two data types.
ST_Geometry
The following is a general description of the ST_Geometry spatial data type. For information specific to the PostgreSQL implementation, see ST_Geometry in PostgreSQL.
The ST_Geometry data type 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. It provides International Organization for Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources, to be compatible with database features such as replication and partitioning, and to provide rapid access to spatial data.
ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.
Although you can define a column as type ST_Geometry, you do not insert ST_Geometry values into the column since it cannot be instantiated. Instead, you insert the subclass values.
The following chart demonstrates the hierarchy of the ST_Geometry data type and its subclasses.
ST_Geometry's subclasses are divided into two categories: the base geometry subclasses and the homogeneous collection subclasses. The base geometries include ST_Point, ST_LineString, and ST_Polygon, while the homogeneous collections include ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon. As the names imply, the homogeneous collections are collections of base geometries. In addition to sharing base geometry properties, homogeneous collections have some of their own properties.
Each subclass stores the type of geometry implied by its name; for instance, ST_MultiPoint stores multipoints. A list of the subclasses and their descriptions are in the following table:
Subtype | Description |
---|---|
ST_Point |
|
ST_LineString |
|
ST_Polygon |
|
ST_MultiPoint |
|
ST_MultiLineString |
|
ST_MultiPolygon |
|
Note that each subclass inherits the properties of the ST_Geometry superclass but also has properties of its own. Functions that operate on the ST_Geometry data type accept any of the subclass entity types. However, some functions have been defined at the subclass level and only accept certain subclasses. For example, the ST_GeometryN function only takes ST_MultiLinestring, ST_MultiPoint, or ST_MultiPolygon subtype values as input.
PostGIS geometry
PostGIS is a product that spatially enables PostgreSQL databases. PostGIS follows the Open Geospatial Consortium, Inc. (OGC), Simple Features specification for a Structured Query Language (SQL). It uses the OGC well-known binary (WKB) and well-known text (WKT) representations of geometry.
PostGIS has two spatial type options: geometry and geography. Only the geometry type is supported with ArcGIS.
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.
- The sde user and any user who creates data in the geodatabase must be granted permissions on specific PostGIS tables.
- 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.
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.
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.
Grant privileges to create 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>;
Create feature classes that use PostGIS geometry
ArcGIS uses a configuration parameter setting in the sde_dbtune table to determine what spatial data type to use when you create a feature class. 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, new feature classes use 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 configuration keyword in the sde_dbtune table to PG_GEOMETRY. Or, if you want to store just some of your feature classes in the PostGIS storage type, you can specify the PG_GEOMETRY configuration keyword when you create your feature class. When exported from the geodatabase, 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 configuration keywords and parameters, see the following topics:
Use existing geometry 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 PostGIS documentation.
You can connect to a PostgreSQL database that contains PostGIS tables from ArcGIS for Desktop and register them with the geodatabase. See Register a table with the geodatabase for more information.
Raster data types
You can use either bytea or ST_Raster data types to store raster in a geodatabase in PostgreSQL. Bytea is used by default. If you want to use ST_Raster, you must configure the geodatabase to use it.
Bytea
Bytea is a variable-length binary string data type used by PostgreSQL. It is similar to a BLOB. See the PostgreSQL documentation for more information on this data type.
ST_Raster
ST_Raster is a user-defined data type you can install in enterprise geodatabases to provide SQL access to raster data.
To use the ST_Raster type, you must configure it in the database.
For in-depth information on how the ST_Raster object type is defined, see The ST_Raster data type.