Creating spatial references using SQL
ESRI recommends creating spatial references using ArcGIS Desktop tools when creating feature classes. ArcGIS provides a full suite of tools for this task and will calculate x-, y-, z-, and m-units and offsets for you to maintain high-precision data. ESRI understands, though, that some users and application developers want to have options when creating spatial references. This section deals with the database tools provided by ESRI and IBM for creating spatial references.
All spatial reference systems recognized by the database are stored in system tables, some of which have editable views. For ArcSDE geodatabases in Oracle using a geometry storage of ST_Geometry, it is the ST_Spatial_References table. In Informix, this table is named Spatial_References. In DB2, it is called ST_Spatial_Reference_Systems. In PostgreSQL, the table is sde_spatial_references, but there is also a view of the sde_spatial_references table, st_spatial_references, that can have spatial references inserted into it.
Internal functions use the parameters of a spatial reference system to translate and scale each floating-point coordinate of the geometry into 64-bit positive integers prior to storage. Upon retrieval, the coordinates are restored to their external floating-point format.
The floating-point coordinates are converted to integers by subtracting the false x- and false y-values, which translates to the false origin; scaling by multiplying by the x,y units; adding a half unit; then truncating the remainder.
The optional z-coordinates and measures are dealt with similarly, except they are translated with false z- and false m-values and scaled with z-units and m-units, respectively.
For a definition of all the columns in the spatial reference table in each database management system, see their respective system table topics:
The spatial reference system is assigned to a geometry during its construction. The spatial reference system must exist in the spatial reference table. All geometries in a column must have the same spatial reference system.
Creating a spatial reference in IBM DB2
In DB2, the command to create a spatial reference is db2se create_srs (or invoke the db2se.ST_create_srs stored procedure). The following example creates a spatial reference record with a scale factor of 10 and using a specific coordinate system:
db2se create_srs mydb -srsName \"mysrs\" -srsID 100 -xScale 10 -coordsysName \"GCS_North_American_1983\"
For more information about using this command, see the "Creating a spatial reference system" section of the DB2 Spatial Extender and Geodetic Extender User's Guide and Reference.
Creating a spatial reference in IBM Informix
Informix users can create a spatial reference record using an INSERT statement similar to this example:
INSERT INTO SDE.SPATIAL_REFERENCES (srid, description, auth_name, auth_srid, falsex, falsey, xyunits, falsez, zunits, falsem, munits, srtext, object_flags, xycluster_tol, zcluster_tol, mcluster_tol) VALUES ( 20, 'GCS_North_American_1983', NULL, NULL, -400, -400, 1000000000, -100000, 100000, -100000, 10000, 'GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]', 1, 0, 0, 0);
For more information about creating a spatial reference in Informix, see the "Using the spatial references table" section of the IBM Informix Spatial DataBlade Module User's Guide.
Creating a spatial reference in Oracle
Creating a spatial reference for tables using the spatial type in Oracle is conducted via an INSERT statement like the one below. Be sure to replace the SRID with a unique value. In the following example, the SRID is the 1 in the third line of code.
INSERT INTO SDE.ST_SPATIAL_REFERENCES (SR_NAME, SRID, X_OFFSET, Y_OFFSET, XYUNITS, Z_OFFSET, Z_SCALE, M_OFFSET, M_SCALE, MIN_X, MAX_X, MIN_Y, MAX_Y, MIN_Z, MAX_Z, MIN_M, MAX_M, CS_ID, CS_NAME, CS_TYPE, ORGANIZATION, ORG_COORDSYS_ID, DEFINITION, DESCRIPTION) VALUES ( 'GCS_North_American_1983', 1, -400, -400, 1000000000, -100000, 100000, -100000, 100000, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 4269, 'GCS_North_American_1983', 'PROJECTED', NULL, NULL, 'GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]', 'ArcSDE SpRef');
Creating a spatial reference in PostgreSQL
To create a spatial reference system for the spatial type in PostgreSQL, use an INSERT statement similar to the one below. Be sure to replace the SRID with a unique value. In the following example, the SRID is the 1 in the third line of code.
INSERT INTO sde.st_spatial_references (sr_name, srid, x_offset, y_offset, xyunits, z_offset, z_scale, m_offset, m_scale, min_x, max_x, min_y, max_y, min_z, max_z, min_m, max_m, cs_id, cs_name, cs_type, organization, org_coordsys_id, definition, description) VALUES ( 'GCS_North_American_1983', 1, -400, -400, 1000000000, -100000, 100000, -100000, 100000, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 9.999E35, -9.999E35, 4269, 'GCS_North_American_1983', 'PROJECTED', NULL, NULL, 'GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137.0,298.257222101]],PRIMEM["Greenwich",0.0],UNIT["Degree",0.0174532925199433]]', 'ArcSDE SpRef');