[1] | 1 | .. _projection_exercises: |
---|
| 2 | |
---|
| 3 | Section 16: Projection Exercises |
---|
| 4 | ================================ |
---|
| 5 | |
---|
| 6 | Here's a reminder of some of the functions we have seen. Hint: they should be useful for the exercises! |
---|
| 7 | |
---|
| 8 | * :command:`sum(expression)` aggregate to return a sum for a set of records |
---|
| 9 | * :command:`ST_Length(linestring)` returns the length of the linestring |
---|
| 10 | * :command:`ST_SRID(geometry, srid)` returns the SRID of the geometry |
---|
| 11 | * :command:`ST_Transform(geometry, srid)` converts geometries into different spatial reference systems |
---|
| 12 | * :command:`ST_GeomFromText(text)` returns ``geometry`` |
---|
| 13 | * :command:`ST_AsText(geometry)` returns WKT ``text`` |
---|
| 14 | * :command:`ST_AsGML(geometry)` returns GML ``text`` |
---|
| 15 | |
---|
| 16 | Remember the online resources that are available to you: |
---|
| 17 | |
---|
| 18 | * http://spatialreference.org |
---|
| 19 | * http://prj2epsg.org |
---|
| 20 | |
---|
| 21 | Also remember the tables we have available: |
---|
| 22 | |
---|
| 23 | * ``nyc_census_blocks`` |
---|
| 24 | |
---|
| 25 | * name, popn_total, boroname, the_geom |
---|
| 26 | |
---|
| 27 | * ``nyc_streets`` |
---|
| 28 | |
---|
| 29 | * name, type, the_geom |
---|
| 30 | |
---|
| 31 | * ``nyc_subway_stations`` |
---|
| 32 | |
---|
| 33 | * name, the_geom |
---|
| 34 | |
---|
| 35 | * ``nyc_neighborhoods`` |
---|
| 36 | |
---|
| 37 | * name, boroname, the_geom |
---|
| 38 | |
---|
| 39 | Exercises |
---|
| 40 | --------- |
---|
| 41 | |
---|
| 42 | * **"What is the length of all streets in New York, as measured in UTM 18?"** |
---|
| 43 | |
---|
| 44 | .. code-block:: sql |
---|
| 45 | |
---|
| 46 | SELECT Sum(ST_Length(the_geom)) |
---|
| 47 | FROM nyc_streets; |
---|
| 48 | |
---|
| 49 | :: |
---|
| 50 | |
---|
| 51 | 10418904.7172 |
---|
| 52 | |
---|
| 53 | * **"What is the WKT definition of SRID 2831?"** |
---|
| 54 | |
---|
| 55 | .. code-block:: sql |
---|
| 56 | |
---|
| 57 | SELECT srtext FROM spatial_ref_sys |
---|
| 58 | WHERE SRID = 2831; |
---|
| 59 | |
---|
| 60 | Or, via `prj2epsg <http://prj2epsg.org/epsg/2831>`_ |
---|
| 61 | |
---|
| 62 | :: |
---|
| 63 | |
---|
| 64 | PROJCS["NAD83(HARN) / New York Long Island", |
---|
| 65 | GEOGCS["NAD83(HARN)", |
---|
| 66 | DATUM["NAD83 (High Accuracy Regional Network)", |
---|
| 67 | SPHEROID["GRS 1980", 6378137.0, 298.257222101, AUTHORITY["EPSG","7019"]], |
---|
| 68 | TOWGS84[-0.991, 1.9072, 0.5129, 0.0257899075194932, -0.009650098960270402, -0.011659943232342112, 0.0], |
---|
| 69 | AUTHORITY["EPSG","6152"]], |
---|
| 70 | PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]], |
---|
| 71 | UNIT["degree", 0.017453292519943295], |
---|
| 72 | AXIS["Geodetic longitude", EAST], |
---|
| 73 | AXIS["Geodetic latitude", NORTH], |
---|
| 74 | AUTHORITY["EPSG","4152"]], |
---|
| 75 | PROJECTION["Lambert Conic Conformal (2SP)", AUTHORITY["EPSG","9802"]], |
---|
| 76 | PARAMETER["central_meridian", -74.0], |
---|
| 77 | PARAMETER["latitude_of_origin", 40.166666666666664], |
---|
| 78 | PARAMETER["standard_parallel_1", 41.03333333333333], |
---|
| 79 | PARAMETER["false_easting", 300000.0], |
---|
| 80 | PARAMETER["false_northing", 0.0], |
---|
| 81 | PARAMETER["scale_factor", 1.0], |
---|
| 82 | PARAMETER["standard_parallel_2", 40.666666666666664], |
---|
| 83 | UNIT["m", 1.0], |
---|
| 84 | AXIS["Easting", EAST], |
---|
| 85 | AXIS["Northing", NORTH], |
---|
| 86 | AUTHORITY["EPSG","2831"]] |
---|
| 87 | |
---|
| 88 | |
---|
| 89 | * **"What is the length of all streets in New York, as measured in SRID 2831?"** |
---|
| 90 | |
---|
| 91 | .. code-block:: sql |
---|
| 92 | |
---|
| 93 | SELECT Sum(ST_Length(ST_Transform(the_geom,2831))) |
---|
| 94 | FROM nyc_streets; |
---|
| 95 | |
---|
| 96 | :: |
---|
| 97 | |
---|
| 98 | 10421993.706374 |
---|
| 99 | |
---|
| 100 | .. note:: |
---|
| 101 | |
---|
| 102 | The difference between the UTM 18 and the Stateplane Long Island measurements is (10421993 - 10418904)/10418904, or 0.02%. Calculated on the spheroid using :ref:`geography` the total street length is 10421999, which is closer to the Stateplane value. This is not surprising, since the Stateplane Long Island projection is precisely calibrated for a very small area (New York City) while UTM 18 has to provide reasonable results for a large regional area. |
---|
| 103 | |
---|
| 104 | * **"What is the KML representation of the point at 'Broad St' subway station?"** |
---|
| 105 | |
---|
| 106 | .. code-block:: sql |
---|
| 107 | |
---|
| 108 | SELECT ST_AsKML(the_geom) |
---|
| 109 | FROM nyc_subway_stations |
---|
| 110 | WHERE name = 'Broad St'; |
---|
| 111 | |
---|
| 112 | :: |
---|
| 113 | |
---|
| 114 | <Point><coordinates>-74.010671468873468,40.707104815584088</coordinates></Point> |
---|
| 115 | |
---|
| 116 | Hey! The coordinates are in geographics even though we didn't call :command:`ST_Transform`, why? Because the KML standard dictates that all coordinates *must* be in geographics (ESPG:4326, in fact) so the :command:`ST_AsKML` function does the transformation automatically. |
---|