source:
trunk/workshop-foss4g/geometries_exercises.rst
@
1
Revision 1, 7.1 KB checked in by djay, 13 years ago (diff) |
---|
Section 9: Geometry Exercises
Here's a reminder of all the functions we have seen so far. They should be useful for the exercises!
:command:`sum(expression)` aggregate to return a sum for a set of records
:command:`count(expression)` aggregate to return the size of a set of records
:command:`ST_GeometryType(geometry)` returns the type of the geometry
:command:`ST_NDims(geometry)` returns the number of dimensions of the geometry
:command:`ST_SRID(geometry)` returns the spatial reference identifier number of the geometry
:command:`ST_X(point)` returns the X ordinate
:command:`ST_Y(point)` returns the Y ordinate
:command:`ST_Length(linestring)` returns the length of the linestring
:command:`ST_StartPoint(geometry)` returns the first coordinate as a point
:command:`ST_EndPoint(geometry)` returns the last coordinate as a point
:command:`ST_NPoints(geometry)` returns the number of coordinates in the linestring
:command:`ST_Area(geometry)` returns the area of the polygons
:command:`ST_NRings(geometry)` returns the number of rings (usually 1, more if there are holes)
:command:`ST_ExteriorRing(polygon)` returns the outer ring as a linestring
:command:`ST_InteriorRingN(polygon, integer)` returns a specified interior ring as a linestring
:command:`ST_Perimeter(geometry)` returns the length of all the rings
:command:`ST_NumGeometries(multi/geomcollection)` returns the number of parts in the collection
:command:`ST_GeometryN(geometry, integer)` returns the specified part of the collection
:command:`ST_GeomFromText(text)` returns geometry
:command:`ST_AsText(geometry)` returns WKT text
:command:`ST_AsEWKT(geometry)` returns EWKT text
:command:`ST_GeomFromWKB(bytea)` returns geometry
:command:`ST_AsBinary(geometry)` returns WKB bytea
:command:`ST_AsEWKB(geometry)` returns EWKB bytea
:command:`ST_GeomFromGML(text)` returns geometry
:command:`ST_AsGML(geometry)` returns GML text
:command:`ST_GeomFromKML(text)` returns geometry
:command:`ST_AsKML(geometry)` returns KML text
:command:`ST_AsGeoJSON(geometry)` returns JSON text
:command:`ST_AsSVG(geometry)` returns SVG text
Also remember the tables we have available:
- nyc_census_blocks
- name, popn_total, boroname, the_geom
- nyc_streets
- name, type, the_geom
- nyc_subway_stations
- name, the_geom
- nyc_neighborhoods
- name, boroname, the_geom
Exercises
"What is the area of the 'West Village' neighborhood?"
SELECT ST_Area(the_geom) FROM nyc_neighborhoods WHERE name = 'West Village';1044614.53027344Note
The area is given in square meters. To get an area in hectares, divide by 10000. To get an area in acres, divide by 4047.
"What is the area of Manhattan in acres?" (Hint: both nyc_census_blocks and nyc_neighborhoods have a boroname in them.)
SELECT Sum(ST_Area(the_geom)) / 4047 FROM nyc_neighborhoods WHERE boroname = 'Manhattan';13965.3201224118or...
SELECT Sum(ST_Area(the_geom)) / 4047 FROM nyc_census_blocks WHERE boroname = 'Manhattan';14572.1575543757"How many census blocks in New York City have a hole in them?"
SELECT Count(*) FROM nyc_census_blocks WHERE ST_NRings(the_geom) > 1;66"What is the total length of streets (in kilometers) in New York City?" (Hint: The units of measurement of the spatial data are meters, there are 1000 meters in a kilometer.)
SELECT Sum(ST_Length(the_geom)) / 1000 FROM nyc_streets;10418.9047172"How long is 'Columbus Cir' (Columbus Circle)?
SELECT ST_Length(the_geom) FROM nyc_streets WHERE name = 'Columbus Cir';308.34199"What is the JSON representation of the boundary of the 'West Village'?"
SELECT ST_AsGeoJSON(the_geom) FROM nyc_neighborhoods WHERE name = 'West Village';{"type":"MultiPolygon","coordinates": [[[[583263.2776595836,4509242.6260239873], [583276.81990686338,4509378.825446927], ... [583263.2776595836,4509242.6260239873]]]]}
The geometry type is "MultiPolygon", interesting!
"How many polygons are in the 'West Village' multipolygon?"
SELECT ST_NumGeometries(the_geom) FROM nyc_neighborhoods WHERE name = 'West Village';1Note
It is not uncommon to find single-element MultiPolygons in spatial tables. Using MultiPolygons allows a table with only one geometry type to store both single- and multi-geometries without using mixed types.
"What is the length of streets in New York City, summarized by type?"
SELECT type, Sum(ST_Length(the_geom)) AS length FROM nyc_streets GROUP BY type ORDER BY length DESC;type | length --------------------------------------------------+------------------ residential | 8629870.33786606 motorway | 403622.478126363 tertiary | 360394.879051303 motorway_link | 294261.419479668 secondary | 276264.303897926 unclassified | 166936.371604458 primary | 135034.233017947 footway | 71798.4878378096 service | 28337.635038596 trunk | 20353.5819826076 cycleway | 8863.75144825929 pedestrian | 4867.05032825026 construction | 4803.08162103562 residential; motorway_link | 3661.57506293745 trunk_link | 3202.18981240201 primary_link | 2492.57457083536 living_street | 1894.63905457332 primary; residential; motorway_link; residential | 1367.76576941335 undefined | 380.53861910346 steps | 282.745221342127 motorway_link; residential | 215.07778911517Note
The ORDER BY length DESC clause sorts the result by length in descending order. The result is that most prevalent types are first in the list.