Comparing Data

Taken together, a coordinate and an SRID define a location on the globe. Without an SRID, a coordinate is just an abstract notion. A “Cartesian” coordinate plane is defined as a “flat” coordinate system placed on the surface of Earth. Because PostGIS functions work on such a plane, comparison operations require that both geometries be represented in the same SRID.

If you feed in geometries with differing SRIDs you will just get an error:

SELECT ST_Equals(
         ST_GeomFromText('POINT(0 0)', 4326),
         ST_GeomFromText('POINT(0 0)', 26918)
         );

ERROR:  Operation on two geometries with different SRIDs
CONTEXT:  SQL function "st_equals" statement 1

Transforming Data

If we return to our proj4 definition for SRID 26918, we can see that our working projection is UTM (Universal Transverse Mercator) of zone 18, with meters as the unit of measurement.

+proj=utm +zone=18 +ellps=GRS80 +datum=NAD83 +units=m +no_defs

Let's convert some data from our working projection to geographic coordinates -- also known as "longitude/latitude".

To convert data from one SRID to another, you must first verify that your geometry has a valid SRID. Since we have already confirmed a valid SRID, we next need the SRID of the projection to transform into. In other words, what is the SRID of geographic coordinates?

The most common SRID for geographic coordinates is 4326, which corresponds to "longitude/latitude on the WGS84 spheroid". You can see the definition at the spatialreference.org site:

http://spatialreference.org/ref/epsg/4326/

You can also pull the definitions from the spatial_ref_sys table:

SELECT srtext FROM spatial_ref_sys WHERE srid = 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.01745329251994328,AUTHORITY["EPSG","9122"]],
  AUTHORITY["EPSG","4326"]]

Let's convert the coordinates of the 'Broad St' subway station into geographics:

SELECT ST_AsText(ST_Transform(the_geom,4326))
FROM nyc_subway_stations
WHERE name = 'Broad St';

POINT(-74.0106714688735 40.7071048155841)

If you load data or create a new geometry without specifying an SRID, the SRID value will be -1. Recall in :ref:`geometries`, that when we created our geoemetries table we didn't specify an SRID. If we query our database, we should expect all the nyc_ tables to have an SRID of 26918, while the geometries table defaulted to an SRID of -1.

To view a table's SRID assignment, query the database's geometry_columns table.

SELECT f_table_name AS name, srid
FROM geometry_columns;

        name         | srid
---------------------+-------
 nyc_census_blocks   | 26918
 nyc_neighborhoods   | 26918
 nyc_streets         | 26918
 nyc_subway_stations | 26918
 geometries          |    -1

However, if you know what the SRID of the coordinates is supposed to be, you can set it post-facto, using :command:`ST_SetSRID` on the geometry. Then you will be able to transform the geometry into other systems.

SELECT ST_AsText(
 ST_Transform(
   ST_SetSRID(geom,26918),
 4326)
)
FROM geometries;

Function List

ST_AsText: Returns the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.

ST_SetSRID(geometry, srid): Sets the SRID on a geometry to a particular integer value.

ST_SRID(geometry): Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table.

ST_Transform(geometry, srid): Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter.