SRID 4326 - longitude and latitude

On this page Carat arrow pointing down
Warning:
CockroachDB v22.2 is no longer supported as of June 5, 2024. For more details, refer to the Release Support Policy.

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:

icon/buttons/copy
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.

icon/buttons/copy
select ST_Transform(ST_GeomFromText('SRID=4326;LINESTRING(-73.756233 42.652580, -74.130833 44.326111)'),3101);
                                         st_transform
----------------------------------------------------------------------------------------------
  01020000201D0C000002000000487C82CDD54D4D41EC8A5DA9AD726B4181403E98D7B34C417A15F9897C116B41
(1 row)
Warning:

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:

icon/buttons/copy
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:

icon/buttons/copy
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:

icon/buttons/copy
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

Note:

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


Yes No
On this page

Yes No