CREATE SPATIAL REFERENCE SYSTEM Statement

Quick Links:

Syntax

{ CREATE [ OR REPLACE ] SPATIAL REFERENCE SYSTEM 
    | CREATE SPATIAL REFERENCE SYSTEM IF NOT EXISTS } 
    srs-name 
    [ srs-attribute ] [ srs-attribute ... ]

srs-attribute - (back to Syntax)
    SRID srs-id
    | DEFINITION { definition-string | NULL }
    | ORGANIZATION { organization-name IDENTIFIED BY organization-srs-id | NULL } 
    | TRANSFORM DEFINITION { transform-definition-string | NULL } 
    | LINEAR UNIT OF MEASURE linear-unit-name 
    | ANGULAR UNIT OF MEASURE { angular-unit-name | NULL } 
    | TYPE { ROUND EARTH | PLANAR } 
    | COORDINATE coordinate-name { UNBOUNDED | BETWEEN low-number AND high-number } 
    | ELLIPSOID SEMI MAJOR AXIS semi-major-axis-length { SEMI MINOR AXIS semi-minor-axis-length 
    | INVERSE FLATTENING inverse-flattening-ratio } 
    | TOLERANCE { tolerance-distance | DEFAULT }
    | SNAP TO GRID { grid-size | DEFAULT } 
    | AXIS ORDER axis-order
    | POLYGON FORMAT polygon-format 
    | STORAGE FORMAT storage-format

grid-size - (back to srs-attribute)
   DOUBLE : usually between 0 and 1

axis-order - (back to srs-attribute)
   { 'x/y/z/m' | 'long/lat/z/m' | 'lat/long/z/m' }

polygon-format - (back to srs-attribute)
   { 'CounterClockWise' | 'Clockwise' | 'EvenOdd' }

storage-format - (back to srs-attribute)
   { 'Internal' | 'Original' | 'Mixed' }

Parameters

(back to top)

OR REPLACE – Specifying OR REPLACE creates the spatial reference system if it does not already exist in the database, and replaces it if it does exist. An error is returned if you attempt to replace a spatial reference system while it is in use. An error is also returned if you attempt to replace a spatial reference system that already xists in the database without specifying the OR REPLACE clause.

IF NOT EXISTS – Specifying CREATE SPATIAL REFERENCE IF NOT EXISTS checks to see if a spatial reference system by that name already exists. If it does not exist, the database server creates the spatial reference system. If it does exist, no further action is performed and no error is returned.

IDENTIFIED BY – the SRID (srs-id) for the spatial reference system. If the spatial reference system is defined by an organization with an organization-srs-id, then srs-id should be set to that value.
If the IDENTIFIED BY clause is not specified, then the SRID defaults to the organization-srs-id defined by either the ORGANIZATION clause or the DEFINITION clause. If neither clause defines an organization-srs-id that could be used as a default SRID, an error is returned.

When the spatial reference system is based on a well known coordinate system, but has a different geodesic interpretation, set the srs-id value to be 1000000000 (one billion) plus the well known value. For example, the SRID for a planar interpretation of the geodetic spatial reference system WGS 84 (ID 4326) would be 1000004326.

With the exception of SRID 0, spatial reference systems provided by SAP Sybase IQ that are not based on well known systems are given a SRID of 2000000000 (two billion) and above. The range of SRID values from 2000000000 to 2147483647 is reserved by SAP Sybase IQ and you should not create SRIDs in this range.

To reduce the possibility of choosing a SRID that is reserved by a defining authority such as OGC or by other vendors, you should not choose a SRID in the range 0 - 32767 (reserved by EPSG), or in the range 2147483547 - 2147483647.

Also, since the SRID is stored as a signed 32-bit integer, the number cannot exceed 231-1 or 2147483647.

DEFINITION – set, or override, default coordinate system settings. If any attribute is set in a clause other than the DEFINITION clause, it takes the value specified in the other clause regardless of what is specified in the DEFINITION clause.
definition-string is a string in the Spatial Reference System Well Known Text syntax as defined by SQL/MM and OGC. For example, the following query returns the definition for WGS 84.
```
SELECT ST_SpatialRefSys::ST_FormatWKT( definition ) 
    FROM ST_SPATIAL_REFERENCE_SYSTEMS 
    WHERE srs_id=4326;
```
In Interactive SQL, if you double-click the value returned, an easier to read version of the value appears.

When the DEFINITION clause is specified, definition-string is parsed and used to choose default values for attributes. For example, definition-string may contain an AUTHORITY element that defines the organization-name and organization-srs-id.

Parameter values in definition-string are overridden by values explicitly set using the SQL statement clauses. For example, if the ORGANIZATION clause is specified, it overrides the value for ORGANIZATION in definition-string.

ORGANIZATION – information about the organization that created the spatial reference system that the spatial reference system is based on.

TRANSFORM DEFINITION – a description of the transform to use for the spatial reference system. Currently, only the PROJ.4 transform is supported. The transform definition is used by the ST_Transform method when transforming data between spatial reference systems. Some transforms may still be possible even if there is no transform-definition-string defined.

LINEAR UNIT OF MEASURE – the linear unit of measure for the spatial reference system. The value you specify must match a linear unit of measure defined in the ST_UNITS_OF_MEASURE system view.
If this clause is not specified, and is not defined in the DEFINITION clause, the default is METRE. To add predefined units of measure to the database, use the sa_install_feature system procedure.

To add custom units of measure to the database, use the CREATE SPATIAL UNIT OF MEASURE statement.
Note: While both METRE and METER are accepted spellings, METRE is preferred as it conforms to the SQL/MM standard.

ANGULAR UNIT OF MEASURE – the angular unit of measure for the spatial reference system. The value you specify must match an angular unit of measure defined in the ST_UNITS_OF_MEASURE system table.
If this clause is not specified, and is not defined in the DEFINITION clause, the default is DEGREE for geographic spatial reference systems and NULL for non-geographic spatial reference systems.

The angular unit of measure must be non-NULL for geographic spatial reference systems and it must be NULL for non-geographic spatial reference systems.

The angular unit of measure must be non-NULL for geographic spatial reference systems and it must be NULL for non-geographic spatial reference systems. To add predefined units of measure to the database, use the sa_install_feature system procedure.

To add custom units of measure to the database, use the CREATE SPATIAL UNIT OF MEASURE statement.

TYPE – control how the SRS interprets lines between points. For geographic spatial reference systems, the TYPE clause can specify either ROUND EARTH (the default) or PLANAR. The ROUND EARTH model interprets lines between points as great elliptic arcs. Given two points on the surface of the Earth, a plane is selected that intersects the two points and the center of the Earth. This plane intersects the Earth, and the line between the two points is the shortest distance along this intersection.
For two points that lie directly opposite each other, there is not a single unique plane that intersects the two points and the center of the Earth. Line segments connecting these anti-podal points are not valid and give an error in the ROUND EARTH model.

The ROUND EARTH model treats the Earth as a spheroid and selects lines that follow the curvature of the Earth. In some cases, it may be necessary to use a planar model where a line between two points is interpreted as a straight line in the equirectangular projection where x=long, y=lat.

In the following example, the blue line shows the line interpretation used in the ROUND EARTH model and the red line shows the corresponding PLANAR model.

The PLANAR model may be used to match the interpretation used by other products. The PLANAR model may also be useful because there are some limitations for methods that are not supported in the ROUND EARTH model (such as ST_Area, ST_ConvexHull) and some are partially supported (ST_Distance only supported between point geometries). Geometries based on circularstrings are not supported in ROUND EARTH spatial reference systems.

For non-geographic SRSs, the type must be PLANAR (and that is the default if the TYPE clause is not specified and either the DEFINITION clause is not specified or it uses a non-geographic definition).

COORDINATE – the bounds on the spatial reference system's dimensions. coordinate-name is the name of the coordinate system used by the spatial reference system. For non-geographic coordinate systems, coordinate-name can be x, y, or m. For geographic coordinate systems, coordinate-name can be LATITUDE, LONGITUDE, z, or m.
Specify UNBOUNDED to place no bounds on the dimensions. Use the BETWEEN clause to set low and high bounds.

The X and Y coordinates must have associated bounds. For geographic spatial reference systems, the longitude coordinate is bounded between -180 and 180 degrees and the latitude coordinate is bounded between -90 and 90 degrees by default the unless COORDINATE clause overrides these settings. For non-geographic spatial reference systems, the CREATE statement must specify bounds for both X and Y coordinates.

LATITUDE and LONGITUDE are used for geographic coordinate systems. The bounds for LATITUDE and LONGITUDE default to the entire Earth, if not specified.

ELLIPSOID – the values to use for representing the Earth as an ellipsoid for spatial reference systems of type ROUND EARTH. If the DEFINITION clause is present, it can specify ellipsoid definition. If the ELLIPSOID clause is specified, it overrides this default ellipsoid.
The Earth is not a perfect sphere because the rotation of the Earth causes a flattening so that the distance from the center of the Earth to the North or South pole is less than the distance from the center to the equator. For this reason, the Earth is modeled as an ellipsoid with different values for the semi-major axis (distance from center to equator) and semi-minor axis (distance from center to the pole). It is most common to define an ellipsoid using the semi-major axis and the inverse flattening, but it can instead be specified using the semi-minor axis (for example, this approach must be used when a perfect sphere is used to approximate the Earth). The semi-major and semi-minor axes are defined in the linear units of the spatial reference system, and the inverse flattening (1/f) is a ratio:
```
1/f = (semi-major-axis) / (semi-major-axis - semi-minor-axis)
```
product-name uses the ellipsoid definition when computing distance in geographic spatial reference systems.

SNAP TO GRID – flat-Earth (planar) spatial reference systems, use the SNAP TO GRID clause to define the size of the grid SAP Sybase IQ uses when performing calculations. By default, SAP Sybase IQ selects a grid size so that 12 significant digits can be stored at all points in the space bounds for X and Y. For example, if a spatial reference system bounds X between -180 and 180 and Y between -90 and 90, then a grid size of 0.000000001 (1E-9) is selected.

TOLERANCE – flat-Earth (planar) spatial reference systems, use the TOLERANCE clause to specify the precision to use when comparing points. If the distance between two points is less than tolerance-distance, the two points are considered equal. Setting tolerance-distance allows you to control the tolerance for imprecision in the input data or limited internal precision. By default, tolerance-distance is set to be equal to grid-size.
When set to 0, two points must be exactly equal to be considered equal.

For round-Earth spatial reference systems, TOLERANCE must be set to 0.

POLYGON FORMAT – internally, SAP Sybase IQ interprets polygons by looking at the orientation of the constituent rings. As one travels a ring in the order of the defined points, the inside of the polygon is on the left side of the ring. The same rules are applied in PLANAR and ROUND EARTH spatial reference systems.
The interpretation used by SAP Sybase IQ is a common but not universal interpretation. Some products use the exact opposite orientation, and some products do not rely on ring orientation to interpret polygons. The POLYGON FORMAT clause can be used to select a polygon interpretation that matches the input data, as needed. The following values are supported:
- CounterClockwise – input follows SAP Sybase IQ's internal interpretation: the inside of the polygon is on the left side while following ring orientation.
- Clockwise – input follows the opposite of SAP Sybase IQ's approach: the inside of the polygon is on the right side while following ring orientation.
- EvenOdd – (default) The orientation of rings is ignored and the inside of the polygon is instead determined by looking at the nesting of the rings, with the exterior ring being the largest ring and interior rings being smaller rings inside this ring. A ray is traced from a point within the rings and radiating outward crossing all rings. If the number the ring being crossed is an even number, it is an outer ring. If it is odd, it is an inner ring.

STORAGE FORMAT – control what is stored when spatial data is loaded into the database. Possible values are:
- Internal – SAP Sybase IQ stores only the normalized representation. Specify this when the original input characteristics do not need to be reproduced. This is the default for planar spatial reference systems (TYPE PLANAR).
- Original – SAP Sybase IQ stores only the original representation. The original input characteristics can be reproduced, but all operations on the stored values must repeat normalization steps, possibly slowing down operations on the data.
- Mixed – SAP Sybase IQ stores the internal version and, if it is different from the original version, SAP Sybase SQL Anywhere® stores the original version as well. By storing both versions, the original representation characteristics can be reproduced and operations on stored values do not need to repeat normalization steps. However, storage requirements may increase significantly because potentially two representations are being stored for each geometry. Mixed is the default format for round-Earth spatial reference systems (TYPE ROUND EARTH).

Examples

(back to top)

Example 1 – creates a spatial reference system named mySpatialRS:

CREATE SPATIAL REFERENCE SYSTEM "mySpatialRS" 
IDENTIFIED BY 1000026980 
LINEAR UNIT OF MEASURE "metre" 
TYPE PLANAR 
COORDINATE X BETWEEN 171266.736269555 AND 831044.757769222 
COORDINATE Y BETWEEN 524881.608973277 AND 691571.125115319 
DEFINITION 'PROJCS["NAD83 / Kentucky South", 
GEOGCS["NAD83", 
DATUM["North_American_Datum_1983", 
SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]], 
AUTHORITY["EPSG","6269"]], 
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], 
UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]], 
AUTHORITY["EPSG","4269"]], 
UNIT["metre",1,AUTHORITY["EPSG","9001"]], 
PROJECTION["Lambert_Conformal_Conic_2SP"], 
PARAMETER["standard_parallel_1",37.93333333333333], 
PARAMETER["standard_parallel_2",36.73333333333333], 
PARAMETER["latitude_of_origin",36.33333333333334], 
PARAMETER["central_meridian",-85.75],
PARAMETER["false_easting",500000],
PARAMETER["false_northing",500000],
AUTHORITY["EPSG","26980"],
AXIS["X",EAST],
AXIS["Y",NORTH]]'
TRANSFORM DEFINITION '+proj=lcc +lat_1=37.93333333333333+lat_2=36.73333333333333+lat_0=36.33333333333334+lon_0=-85.75+x_0=500000+y_0=500000+ellps=GRS80+datum=NAD83+units=m+no_defs';

Usage

(back to top)

For a geographic spatial reference system, you can specify both a LINEAR and an ANGULAR unit of measure; otherwise for non-geographic, you specify only a LINEAR unit of measure. The LINEAR unit of measure is used for computing distance between points and areas. The ANGULAR unit of measure tells how the angular latitude/longitude are interpreted and is NULL for projected coordinate systems, non-NULL for geographic coordinate systems.

All derived geometries returned by operations are normalized.

When working with data that is being synchronized with a non-SQL Anywhere database, STORAGE FORMAT should be set to either 'Original' or 'Mixed' so that the original characteristics of the data can be preserved.

Standards

(back to top)

ANSI SQL–Compliance level: Transact-SQL® extension.

Permissions

(back to top)

Requires one of:

MANAGE ANY SPATIAL OBJECT system privilege.
CREATE ANY OBJECT system privilege.