Spatial Reference System Identifier (SRID)
Spatial Reference Systems are managed in the database using Spatial Reference System Identifiers (SRID) that are defined in the spatial_ref_sys
system catalog (see
Geospatial System Catalogs). Each SRID references a record in the table that provides comprehensive details about the coordinate system properties assigned to the geospatial data.
When a new table with one or more spatial data types is created, an entry is created in the spatial_ref_sys table that links the table to the SRID that is assigned. This relationship is used by geospatial functions to determine coordinate system information when required.
Example CREATE TABLE statement:
This statement creates a table tracking the location of trees planted by the city using the POINT data type:
CREATE TABLE ncc_tree (
id INTEGER PRIMARY KEY,
type INTEGER NOT NULL,
location POINT SRID 4326 NOT NULL
);
Example record from spatial_ref_sys:
srid: 4326
auth_name: EPSG
auth_srid: 4326
srtext: GEOGCS["WGS 84",DATUM["WGS_1984",SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],AUTHORITY["EPSG","6326"]],PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]]
proj4text: +proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs
The SRID is a shortcut to the comprehensive coordinate system definitions that are also classified by the external EPSG numbers used in popular geospatial applications. An SRID is used internally by Ingres and is not guaranteed to be the same as the EPSG designator.
In the descriptions of spatial functions in this guide, the SRID is sometimes shown as optional, to be used when the SRID is not already defined. If an SRID is not assigned to a geometry data type or column, then an SRID will have to be specified manually in queries when any coordinate system related functions are used.
SRIDs are defined at table creation time or at feature creation time, as shown in the following example queries. The first query defines a feature using SRID 4326, which means that latitude and longitude geographic coordinates are being used. The second query uses SRID 3347, which refers to a Canadian specific coordinate system measured in meters:
SELECT ASTEXT('POINT(-121 52)', 4326);
SELECT ASTEXT('POINT(4114829, 2314857)', 3347;
Later, features stored with SRID information can be transformed into other coordinate systems for various reasons. For example, a feature that was defined as SRID 4326 can be converted into SRID 3347 coordinate space using the TRANSFORM() function:
SELECT TRANSFORM(shape,3347) FROM houses;
The SRID used by a feature or defined in a table can be returned by the SRID() function:
SELECT SRID(shape) FROM houses;
-------------
col1
-------------
4326
-------------
SRID Restrictions
The following syntax does not work:
CREATE TABLE cras AS SELECT PointFromText('POINT(1 1)', 123);
but the following syntax works:
CREATE TABLE cras AS SELECT PointFromText('POINT(1 1)');
If a literal is used in a CREATE TABLE AS SELECT statement, the SRID cannot be looked up because there is no physical table behind it. Ingres by default assigns an SRID of -1, which works in the second example. You can always do an ALTER TABLE ALTER COLUMN to change the SRID after the table is created.
Last modified date: 11/09/2022