source:
trunk/workshop-foss4g/spatial_relationships_exercises.rst
@
18
| Revision 1, 3.3 KB checked in by djay, 13 years ago (diff) |
|---|
Section 11: Spatial Relationships Exercises
Here's a reminder of the functions we saw in the last section. 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_Contains(geometry A, geometry B)` returns true if geometry A contains geometry B
:command:`ST_Crosses(geometry A, geometry B)` returns true if geometry A crosses geometry B
:command:`ST_Disjoint(geometry A , geometry B)` returns true if the geometries do not "spatially intersect"
:command:`ST_Distance(geometry A, geometry B)` returns the minimum distance between geometry A and geometry B
:command:`ST_DWithin(geometry A, geometry B, radius)` returns true if geometry A is radius distance or less from geometry B
:command:`ST_Equals(geometry A, geometry B)` returns true if geometry A is the same as geometry B
:command:`ST_Intersects(geometry A, geometry B)` returns true if geometry A intersects geometry B
:command:`ST_Overlaps(geometry A, geometry B)` returns true if geometry A and geometry B share space, but are not completely contained by each other.
:command:`ST_Touches(geometry A, geometry B)` returns true if the boundary of geometry A touches geometry B
:command:`ST_Within(geometry A, geometry B)` returns true if geometry A is within geometry B
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 geometry value for the street named 'Atlantic Commons'?"
SELECT the_geom FROM nyc_streets WHERE name = 'Atlantic Commons';01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141"What neighborhood and borough is Atlantic Commons in?"
SELECT name, boroname FROM nyc_neighborhoods WHERE ST_Intersects( the_geom, '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141' );name | boroname ------------+---------- Fort Green | Brooklyn"What streets does Atlantic Commons touch?"
SELECT name FROM nyc_streets WHERE ST_Touches( the_geom, '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141' );name --------------- S Oxford St Cumberland St
"Approximately how many people live on (within 50 meters of) Atlantic Commons?"
SELECT Sum(popn_total) FROM nyc_census_blocks WHERE ST_DWithin( the_geom, '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141', 50 );1186
