IBM® Informix® 12.10

The spatial_references table

The spatial_references table contains a spatial reference ID (SRID) for each spatial reference system.

The spatial_references table stores data about each map projection that you use to store the spherical geometry of the Earth, for example, your data might use the Mercator projection. The spatial reference ID (SRID) is the unique key for the record in the spatial_references table that describes a particular spatial reference system. All spatial reference systems that you use in your database must have a record in the spatial_references table. All geometries in a spatial column must use the same spatial reference system.

The IBM® Informix® spatial functions use the parameters of a spatial reference system to translate and scale each floating point coordinate of the geometry into 54-bit positive integers before storage. When retrieved, the coordinates are restored to their external floating point format.

The columns of the spatial_references table are described in the following table.

Table 1. The spatial_references table
Column name	Type	Example value	Description
srid	INTEGER NOT NULL	`12345`	Primary key: the unique key for the record that describes a particular spatial reference system
description	VARCHAR(64)	`WGS 1984`	A text description of the spatial reference system
auth_name	VARCHAR(255)	`EPSG`	The name of the standard or standards body cited for the reference system
auth_srid	INTEGER	`4326`	The ID of the spatial reference system as defined by the authority cited in auth_name column.
falsex	FLOAT NOT NULL	`-180`	The external floating point X coordinates are converted to integers for storage in the database by subtracting the falsex values.
falsey	FLOAT NOT NULL	`-90`	The external floating point Y coordinates are converted to integers for storage in the database by subtracting the falsey values.
xyunits	FLOAT NOT NULL	`1000000`	Before the external floating point X and Y coordinates are inserted into the database, the coordinates are scaled by the value in xyunits. The scaling process adds a half unit and truncates the remainder.
falsez	FLOAT NOT NULL	`-1000`	The external floating point Z coordinates are converted to integers for storage in the database by subtracting the falsez values.
zunits	FLOAT NOT NULL	`1000`	A factor that is used to scale the Z-coordinate
falsem	FLOAT NOT NULL	`-1000`	The external floating point M coordinates are converted to integers for storage in the database by subtracting the falsem values.
munits	FLOAT NOT NULL	`1000`	A factor that is used to scale the measure values
srtext	CHAR(2048)	`GEOGCS["GCS_WGS_1984",` `DATUM["D_WGS_1984",` `SPHEROID["WGS_1984", 6378137, 298.257223563]],` `PRIMEM["Greenwich",0],` `UNIT["Degree", 0.0174532925199433]]`	The srtext column contains the well known text representation of the spatial reference system.

Example: Create a spatial reference system with an INSERT statement

The following example shows how to insert a spatial reference system into the spatial_references table:

INSERT INTO spatial_references
     (srid, description, auth_name, auth_srid, falsex, falsey,
      xyunits, falsez, zunits, falsem, munits, srtext)
   VALUES (1, NULL, NULL, NULL, 0, 0, 100, 0, 1, 0, 1, 'UNKNOWN');

In this example, the spatial reference system has an SRID value of 1, a false X, Y of (0,0), and its system units are 100. The Z coordinate and measure offsets are 0, while the Z coordinate and measure units are 1.

Example: Create a spatial reference system with the SE_CreateSRID() function

For the hazardous sites and sensitive areas example, the coordinates are in a local countywide XY coordinate system. The X and Y coordinates range from 0 - 250 000. The SE_CreateSRID() function creates the new spatial reference system with the SRID of 5:

EXECUTE FUNCTION SE_CreateSRID(0, 0, 250000, 250000,
                               "Springfield county XY coord system");

(expression) 

           5

Examples exchange | Troubleshooting

To find the PDF, see Publications for the IBM Informix 12.10 family of products.
For the release notes, documentation notes, and/or machine notes, see the Release Notes page.