Every geometric shape has a spatial reference system associated with it, and each such reference system has a Spatial Reference System ID (SRID). The SRID is used to tell which spatial reference system will be used to interpret each spatial object.
A common SRID in use is 4326, which represents spatial data using longitude and latitude coordinates on the Earth's surface as defined in the WGS84 standard, which is also used for the Global Positioning System (GPS).
Here is an example shape that uses SRID 4326, a line stretching from Milwaukee, WI to Tulsa, OK:
SRID=4326;LINESTRING( -87.906471 43.038902, -95.992775 36.153980)
Most users should only have to care about SRIDs in the following situations:
- When creating shapes. By default, shapes which do not have an SRID associated with them will use an SRID of 0, which means "this shape has no SRID".
- When comparing shapes. If you try to compare two shapes with different SRIDs using a spatial predicate, CockroachDB will signal an error.
Adding SRIDs to shapes
Add an SRID with WKT
To add an SRID to a shape you are defining with Well Known Text, prepend the text with SRID=N; as shown below:
SRID=4326;POLYGON((-79.976111 40.374444, -74.621157 40.323294, -76.609383 39.299236, -79.976111 40.374444))
Add an SRID with SQL
To add an SRID to a shape in SQL, use the ST_SetSRID function:
select ST_SetSRID(ST_MakePoint(1,2), 4326);
st_setsrid
------------------------------------------------------
0101000020E6100000000000000000F03F0000000000000040
(1 row)
Change a shape's SRID
To convert a shape to use a different SRID while maintaining the same reference location, use the ST_Transform function. In the example below, we transform the reference system for a line from Albany, NY to Saranac Lake, NY from 4326 to use the StatePlane New York East reference (SRID 3101). This can be useful (or at least interesting) in some situations, since the StatePlane systems give distances in feet.
select ST_Transform(ST_GeomFromText('SRID=4326;LINESTRING(-73.756233 42.652580, -74.130833 44.326111)'),3101);
st_transform
----------------------------------------------------------------------------------------------
01020000201D0C000002000000487C82CDD54D4D41EC8A5DA9AD726B4181403E98D7B34C417A15F9897C116B41
(1 row)
When setting a shape's SRID, you can only use SRIDs that are defined in the spatial_ref_sys table. For more information, see Getting SRID information.
Getting SRID information
You can get more detailed information about the SRIDs supported in CockroachDB from the SQL client by querying the spatial_ref_sys table.
To see how many SRIDs are supported, run the following query:
SELECT count(*) FROM spatial_ref_sys;
count
---------
6139
(1 row)
To get more information about a specific SRID (in this case 4326), run the following query:
SELECT * FROM spatial_ref_sys WHERE srid = 4326;
srid | auth_name | auth_srid | srtext | proj4text
-------+-----------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------
4326 | EPSG | 4326 | 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.0174532925199433,AUTHORITY["EPSG","9122"]],AUTHORITY["EPSG","4326"]] | +proj=longlat +datum=WGS84 +no_defs
(1 row)
Comparing shapes with different SRIDs
If you try to compare two shapes with different SRIDs using a spatial predicate, CockroachDB will signal an error:
select ST_Contains(ST_MakePoint(1,2), ST_SetSRID(ST_MakePoint(1,2), 4326));
ERROR: st_contains(): operation on mixed SRIDs forbidden: (Point, 0) != (Point, 4326)
Known limitations
Defining a custom SRID by inserting rows into spatial_ref_sys is not currently supported. For more information, see the tracking issue cockroachdb/cockroach#55903.
See also
- Spatial features
- Spatial tutorial
- Spatial indexes
- Spatial and GIS Glossary of Terms
- Well known text
- Well known binary
- GeoJSON
- Introducing Distributed Spatial Data in Free, Open Source CockroachDB (blog post)
- Using GeoServer with CockroachDB
Was this helpful?
