SRID 4326 - longitude and latitude

Every geometric shape has a spatial reference system associated with it, and each such reference system has a Spatial Reference System ID (). 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 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 , 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 :

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 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 by querying the 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 is not currently supported.

See also

• Introducing Distributed Spatial Data in CockroachDB (blog post)