[1] | 1 | .. _spatial_relationships_exercises: |
---|
| 2 | |
---|
| 3 | Section 11: Spatial Relationships Exercises |
---|
| 4 | =========================================== |
---|
| 5 | |
---|
| 6 | Here's a reminder of the functions we saw in the last section. They should be useful for the exercises! |
---|
| 7 | |
---|
| 8 | * :command:`sum(expression)` aggregate to return a sum for a set of records |
---|
| 9 | * :command:`count(expression)` aggregate to return the size of a set of records |
---|
| 10 | * :command:`ST_Contains(geometry A, geometry B)` returns true if geometry A contains geometry B |
---|
| 11 | * :command:`ST_Crosses(geometry A, geometry B)` returns true if geometry A crosses geometry B |
---|
| 12 | * :command:`ST_Disjoint(geometry A , geometry B)` returns true if the geometries do not "spatially intersect" |
---|
| 13 | * :command:`ST_Distance(geometry A, geometry B)` returns the minimum distance between geometry A and geometry B |
---|
| 14 | * :command:`ST_DWithin(geometry A, geometry B, radius)` returns true if geometry A is radius distance or less from geometry B |
---|
| 15 | * :command:`ST_Equals(geometry A, geometry B)` returns true if geometry A is the same as geometry B |
---|
| 16 | * :command:`ST_Intersects(geometry A, geometry B)` returns true if geometry A intersects geometry B |
---|
| 17 | * :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. |
---|
| 18 | * :command:`ST_Touches(geometry A, geometry B)` returns true if the boundary of geometry A touches geometry B |
---|
| 19 | * :command:`ST_Within(geometry A, geometry B)` returns true if geometry A is within geometry B |
---|
| 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 geometry value for the street named 'Atlantic Commons'?"** |
---|
| 43 | |
---|
| 44 | .. code-block:: sql |
---|
| 45 | |
---|
| 46 | SELECT the_geom |
---|
| 47 | FROM nyc_streets |
---|
| 48 | WHERE name = 'Atlantic Commons'; |
---|
| 49 | |
---|
| 50 | :: |
---|
| 51 | |
---|
| 52 | 01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141 |
---|
| 53 | |
---|
| 54 | * **"What neighborhood and borough is Atlantic Commons in?"** |
---|
| 55 | |
---|
| 56 | .. code-block:: sql |
---|
| 57 | |
---|
| 58 | SELECT name, boroname |
---|
| 59 | FROM nyc_neighborhoods |
---|
| 60 | WHERE ST_Intersects( |
---|
| 61 | the_geom, |
---|
| 62 | '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141' |
---|
| 63 | ); |
---|
| 64 | |
---|
| 65 | :: |
---|
| 66 | |
---|
| 67 | name | boroname |
---|
| 68 | ------------+---------- |
---|
| 69 | Fort Green | Brooklyn |
---|
| 70 | |
---|
| 71 | |
---|
| 72 | * **"What streets does Atlantic Commons touch?"** |
---|
| 73 | |
---|
| 74 | .. code-block:: sql |
---|
| 75 | |
---|
| 76 | SELECT name |
---|
| 77 | FROM nyc_streets |
---|
| 78 | WHERE ST_Touches( |
---|
| 79 | the_geom, |
---|
| 80 | '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141' |
---|
| 81 | ); |
---|
| 82 | |
---|
| 83 | :: |
---|
| 84 | |
---|
| 85 | name |
---|
| 86 | --------------- |
---|
| 87 | S Oxford St |
---|
| 88 | Cumberland St |
---|
| 89 | |
---|
| 90 | .. image:: ./spatial_relationships/atlantic_commons.jpg |
---|
| 91 | |
---|
| 92 | |
---|
| 93 | * **"Approximately how many people live on (within 50 meters of) Atlantic Commons?"** |
---|
| 94 | |
---|
| 95 | .. code-block:: sql |
---|
| 96 | |
---|
| 97 | SELECT Sum(popn_total) |
---|
| 98 | FROM nyc_census_blocks |
---|
| 99 | WHERE ST_DWithin( |
---|
| 100 | the_geom, |
---|
| 101 | '01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADFE82141CB2D3EFFA52E5141', |
---|
| 102 | 50 |
---|
| 103 | ); |
---|
| 104 | |
---|
| 105 | :: |
---|
| 106 | |
---|
| 107 | 1186 |
---|
| 108 | |
---|