[1] | 1 | .. _projection: |
---|
| 2 | |
---|
| 3 | Section 15: Projecting Data |
---|
| 4 | =========================== |
---|
| 5 | |
---|
| 6 | The earth is not flat, and there is no simple way of putting it down on a flat paper map (or computer screen), so people have come up with all sorts of ingenious solutions, each with pros and cons. Some projections preserve area, so all objects have a relative size to each other; other projections preserve angles (conformal) like the Mercator projection; some projections try to find a good intermediate mix with only little distortion on several parameters. Common to all projections is that they transform the (spherical) world onto a flat cartesian coordinate system, and which projection to choose depends on how you will be using the data. |
---|
| 7 | |
---|
| 8 | We've already encountered projections when we :ref:`loaded our nyc data <loading_data>`. (Recall that pesky SRID 26918). Sometimes, however, you need to transform and re-project between spatial reference systems. PostGIS includes built-in support for changing the projection of data, using the :command:`ST_Transform(geometry, srid)` function. For managing the spatial reference identifiers on geometries, PostGIS provides the :command:`ST_SRID(geometry)` and :command:`ST_SetSRID(geometry, srid)` functions. |
---|
| 9 | |
---|
| 10 | We can confirm the SRID of our data with the :command:`ST_SRID` command: |
---|
| 11 | |
---|
| 12 | .. code-block:: sql |
---|
| 13 | |
---|
| 14 | SELECT ST_SRID(the_geom) FROM nyc_streets LIMIT 1; |
---|
| 15 | |
---|
| 16 | :: |
---|
| 17 | |
---|
| 18 | 26918 |
---|
| 19 | |
---|
| 20 | And what is definition of "26918"? As we saw in ":ref:`loading data section <loading_data>`", the definition is contained in the ``spatial_ref_sys`` table. In fact, **two** definitions are there. The "well-known text" (:term:`WKT`) definition is in the ``srtext`` column, and there is a second definition in "proj.4" format in the ``proj4text`` column. |
---|
| 21 | |
---|
| 22 | .. code-block:: sql |
---|
| 23 | |
---|
| 24 | SELECT * FROM spatial_ref_sys WHERE srid = 26918; |
---|
| 25 | |
---|
| 26 | In fact, for the internal PostGIS re-projection calculations, it is the contents of the ``proj4text`` column that are used. For our 26918 projection, here is the proj.4 text: |
---|
| 27 | |
---|
| 28 | .. code-block:: sql |
---|
| 29 | |
---|
| 30 | SELECT proj4text FROM spatial_ref_sys WHERE srid = 26918; |
---|
| 31 | |
---|
| 32 | :: |
---|
| 33 | |
---|
| 34 | +proj=utm +zone=18 +ellps=GRS80 +datum=NAD83 +units=m +no_defs |
---|
| 35 | |
---|
| 36 | In practice, both the ``srtext`` and the ``proj4text`` columns are important: the ``srtext`` column is used by external programs like `GeoServer <http://geoserver.org>`_, `uDig <udig.refractions.net>`_, and `FME <http://www.safe.com/>`_ and others; the ``proj4text`` column is used internally. |
---|
| 37 | |
---|
| 38 | Comparing Data |
---|
| 39 | -------------- |
---|
| 40 | |
---|
| 41 | 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. |
---|
| 42 | |
---|
| 43 | If you feed in geometries with differing SRIDs you will just get an error: |
---|
| 44 | |
---|
| 45 | .. code-block:: sql |
---|
| 46 | |
---|
| 47 | SELECT ST_Equals( |
---|
| 48 | ST_GeomFromText('POINT(0 0)', 4326), |
---|
| 49 | ST_GeomFromText('POINT(0 0)', 26918) |
---|
| 50 | ); |
---|
| 51 | |
---|
| 52 | :: |
---|
| 53 | |
---|
| 54 | ERROR: Operation on two geometries with different SRIDs |
---|
| 55 | CONTEXT: SQL function "st_equals" statement 1 |
---|
| 56 | |
---|
| 57 | |
---|
| 58 | .. note:: |
---|
| 59 | |
---|
| 60 | Be careful of getting too happy with using :command:`ST_Transform` for on-the-fly conversion. Spatial indexes are built using SRID of the stored geometries. If comparison are done in a different SRID, spatial indexes are (often) not used. It is best practice to choose **one SRID** for all the tables in your database. Only use the transformation function when you are reading or writing data to external applications. |
---|
| 61 | |
---|
| 62 | |
---|
| 63 | Transforming Data |
---|
| 64 | ----------------- |
---|
| 65 | |
---|
| 66 | 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. |
---|
| 67 | |
---|
| 68 | :: |
---|
| 69 | |
---|
| 70 | +proj=utm +zone=18 +ellps=GRS80 +datum=NAD83 +units=m +no_defs |
---|
| 71 | |
---|
| 72 | Let's convert some data from our working projection to geographic coordinates -- also known as "longitude/latitude". |
---|
| 73 | |
---|
| 74 | 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? |
---|
| 75 | |
---|
| 76 | 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: |
---|
| 77 | |
---|
| 78 | http://spatialreference.org/ref/epsg/4326/ |
---|
| 79 | |
---|
| 80 | You can also pull the definitions from the ``spatial_ref_sys`` table: |
---|
| 81 | |
---|
| 82 | .. code-block:: sql |
---|
| 83 | |
---|
| 84 | SELECT srtext FROM spatial_ref_sys WHERE srid = 4326; |
---|
| 85 | |
---|
| 86 | :: |
---|
| 87 | |
---|
| 88 | GEOGCS["WGS 84", |
---|
| 89 | DATUM["WGS_1984", |
---|
| 90 | SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]], |
---|
| 91 | AUTHORITY["EPSG","6326"]], |
---|
| 92 | PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]], |
---|
| 93 | UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]], |
---|
| 94 | AUTHORITY["EPSG","4326"]] |
---|
| 95 | |
---|
| 96 | Let's convert the coordinates of the 'Broad St' subway station into geographics: |
---|
| 97 | |
---|
| 98 | .. code-block:: sql |
---|
| 99 | |
---|
| 100 | SELECT ST_AsText(ST_Transform(the_geom,4326)) |
---|
| 101 | FROM nyc_subway_stations |
---|
| 102 | WHERE name = 'Broad St'; |
---|
| 103 | |
---|
| 104 | :: |
---|
| 105 | |
---|
| 106 | POINT(-74.0106714688735 40.7071048155841) |
---|
| 107 | |
---|
| 108 | 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. |
---|
| 109 | |
---|
| 110 | To view a table's SRID assignment, query the database's ``geometry_columns`` table. |
---|
| 111 | |
---|
| 112 | .. code-block:: sql |
---|
| 113 | |
---|
| 114 | SELECT f_table_name AS name, srid |
---|
| 115 | FROM geometry_columns; |
---|
| 116 | |
---|
| 117 | :: |
---|
| 118 | |
---|
| 119 | name | srid |
---|
| 120 | ---------------------+------- |
---|
| 121 | nyc_census_blocks | 26918 |
---|
| 122 | nyc_neighborhoods | 26918 |
---|
| 123 | nyc_streets | 26918 |
---|
| 124 | nyc_subway_stations | 26918 |
---|
| 125 | geometries | -1 |
---|
| 126 | |
---|
| 127 | |
---|
| 128 | 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. |
---|
| 129 | |
---|
| 130 | .. code-block:: sql |
---|
| 131 | |
---|
| 132 | SELECT ST_AsText( |
---|
| 133 | ST_Transform( |
---|
| 134 | ST_SetSRID(geom,26918), |
---|
| 135 | 4326) |
---|
| 136 | ) |
---|
| 137 | FROM geometries; |
---|
| 138 | |
---|
| 139 | Function List |
---|
| 140 | ------------- |
---|
| 141 | `ST_AsText <http://postgis.org/docs/ST_AsText.html>`_: Returns the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata. |
---|
| 142 | |
---|
| 143 | `ST_SetSRID(geometry, srid) <http://postgis.org/docs/ST_SetSRID.html>`_: Sets the SRID on a geometry to a particular integer value. |
---|
| 144 | |
---|
| 145 | `ST_SRID(geometry) <http://postgis.org/docs/ST_SRID.html>`_: Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table. |
---|
| 146 | |
---|
| 147 | `ST_Transform(geometry, srid) <http://postgis.org/docs/ST_Transform.html>`_: Returns a new geometry with its coordinates transformed to the SRID referenced by the integer parameter. |
---|