Creates or replaces a spatial reference system.
Syntax ParametersOR REPLACE clause 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 exists in the database without specifying the OR REPLACE clause.
CREATE SPATIAL REFERENCE 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 clause Use this clause to specify 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 SQL Anywhere 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 SQL Anywhere 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 2 147 483 547 - 2147 483 647.
Also, since the SRID is stored as a signed 32-bit integer, the number cannot exceed 231-1 or 2147483647.
DEFINITION clause Use this clause to 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 clause Use this clause to specify information about the organization that created the spatial reference system that the new spatial reference system is based on. organization-name is the name of the organization that created it; organization-srs-id is the numeric identifier the organization uses to identify the spatial reference system.
TRANSFORM DEFINITION clause
Use this clause to specify a description of the transform to use for the spatial reference system. Currently, only the
PROJ.4 transform is supported. For example, the transform-definition-string for WGS 84 is '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs'.
If you specify an unsupported transform definition, an error is returned.
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 clause Use this clause to specify the linear unit of measure for the spatial reference system. The value you specify must match a linear unit of measure that is 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.
While both METRE and METER are accepted spellings, METRE is preferred as it conforms to the SQL/MM standard.
ANGULAR UNIT OF MEASURE clause Use this clause to specify 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.
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 clause Use the TYPE clause to 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 clause Use this clause to specify 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 clause Use the ellipsoid clause to specify 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) |
SQL Anywhere uses the ellipsoid definition when computing distance in geographic spatial reference systems.
The ellipsoid must be defined for geographic spatial reference systems (either in the DEFINITION clause or the ELLIPSOID clause), and it must not be specified for non-geographic spatial reference systems.
SNAP TO GRID clause For flat-Earth (planar) spatial reference systems, use the SNAP TO GRID clause to define the size of the grid SQL Anywhere uses when performing calculations. By default, SQL Anywhere 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.
grid-size must be large enough so that points snapped to the grid can be represented with equal precision at all points in the bounded space. If grid-size is too small, the server reports an error.
When set to 0, no snapping to grid is performed.
For round-Earth spatial reference systems, SNAP TO GRID must be set to 0.
Specify SNAP TO GRID DEFAULT to set the grid size to the default that the database server would use.
TOLERANCE clause For 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 clause Internally, SQL Anywhere 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 SQL Anywhere 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' The input follows SQL Anywhere's internal interpretation: the inside of the polygon is on the left side while following ring orientation.
'Clockwise' The input follows the opposite of SQL Anywhere's approach: the inside of the polygon is on the right side while following ring orientation.
'EvenOdd' EvenOdd is the default format. With EvenOdd, 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 clause When you insert spatial data into the database from an external format (such as WKT or WKB), the database server normalizes the data to improve the performance and semantics of spatial operations. The normalized representation may differ from the original representation (for example, in the orientation of polygon rings or the precision stored in individual coordinates). While spatial equality is maintained after the normalization, some original input characteristics may not be reproducible, such as precision and ring orientation. In some cases you may want to store the original representation, either exclusively, or in addition to the normalized representation.
To control what is stored, specify the STORAGE FORMAT clause followed by one of the following values:
'Internal' SQL Anywhere stores only the normalized representation. Specify this value when the original input characteristics do not need to be reproduced. This is the default for planar spatial reference systems (TYPE PLANAR).
If you are using MobiLink to synchronize your spatial data, you should specify Mixed. MobiLink tests for equality during synchronization, which requires the data in its original format.
'Original' SQL Anywhere 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' SQL Anywhere stores the internal version and, if it is different from the original version, it 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).
Remarks Permissions Side effects See also Standards and compatibility Example |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |