#
source:
trunk/workshop-foss4g/geometries_exercises.rst
@
1

Revision 1, 7.1 KB checked in by djay, 8 years ago (diff) |
---|

Rev | Line | |
---|---|---|

[1] | 1 | .. _geometries_exercises: |

2 | ||

3 | Section 9: Geometry Exercises | |

4 | ============================= | |

5 | ||

6 | Here's a reminder of all the functions we have seen so far. 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_GeometryType(geometry)` returns the type of the geometry | |

11 | * :command:`ST_NDims(geometry)` returns the number of dimensions of the geometry | |

12 | * :command:`ST_SRID(geometry)` returns the spatial reference identifier number of the geometry | |

13 | * :command:`ST_X(point)` returns the X ordinate | |

14 | * :command:`ST_Y(point)` returns the Y ordinate | |

15 | * :command:`ST_Length(linestring)` returns the length of the linestring | |

16 | * :command:`ST_StartPoint(geometry)` returns the first coordinate as a point | |

17 | * :command:`ST_EndPoint(geometry)` returns the last coordinate as a point | |

18 | * :command:`ST_NPoints(geometry)` returns the number of coordinates in the linestring | |

19 | * :command:`ST_Area(geometry)` returns the area of the polygons | |

20 | * :command:`ST_NRings(geometry)` returns the number of rings (usually 1, more if there are holes) | |

21 | * :command:`ST_ExteriorRing(polygon)` returns the outer ring as a linestring | |

22 | * :command:`ST_InteriorRingN(polygon, integer)` returns a specified interior ring as a linestring | |

23 | * :command:`ST_Perimeter(geometry)` returns the length of all the rings | |

24 | * :command:`ST_NumGeometries(multi/geomcollection)` returns the number of parts in the collection | |

25 | * :command:`ST_GeometryN(geometry, integer)` returns the specified part of the collection | |

26 | * :command:`ST_GeomFromText(text)` returns ``geometry`` | |

27 | * :command:`ST_AsText(geometry)` returns WKT ``text`` | |

28 | * :command:`ST_AsEWKT(geometry)` returns EWKT ``text`` | |

29 | * :command:`ST_GeomFromWKB(bytea)` returns ``geometry`` | |

30 | * :command:`ST_AsBinary(geometry)` returns WKB ``bytea`` | |

31 | * :command:`ST_AsEWKB(geometry)` returns EWKB ``bytea`` | |

32 | * :command:`ST_GeomFromGML(text)` returns ``geometry`` | |

33 | * :command:`ST_AsGML(geometry)` returns GML ``text`` | |

34 | * :command:`ST_GeomFromKML(text)` returns ``geometry`` | |

35 | * :command:`ST_AsKML(geometry)` returns KML ``text`` | |

36 | * :command:`ST_AsGeoJSON(geometry)` returns JSON ``text`` | |

37 | * :command:`ST_AsSVG(geometry)` returns SVG ``text`` | |

38 | ||

39 | Also remember the tables we have available: | |

40 | ||

41 | * ``nyc_census_blocks`` | |

42 | ||

43 | * name, popn_total, boroname, the_geom | |

44 | ||

45 | * ``nyc_streets`` | |

46 | ||

47 | * name, type, the_geom | |

48 | ||

49 | * ``nyc_subway_stations`` | |

50 | ||

51 | * name, the_geom | |

52 | ||

53 | * ``nyc_neighborhoods`` | |

54 | ||

55 | * name, boroname, the_geom | |

56 | ||

57 | Exercises | |

58 | --------- | |

59 | ||

60 | * **"What is the area of the 'West Village' neighborhood?"** | |

61 | ||

62 | .. code-block:: sql | |

63 | ||

64 | SELECT ST_Area(the_geom) | |

65 | FROM nyc_neighborhoods | |

66 | WHERE name = 'West Village'; | |

67 | ||

68 | :: | |

69 | ||

70 | 1044614.53027344 | |

71 | ||

72 | .. note:: | |

73 | ||

74 | 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. | |

75 | ||

76 | * **"What is the area of Manhattan in acres?"** (Hint: both ``nyc_census_blocks`` and ``nyc_neighborhoods`` have a ``boroname`` in them.) | |

77 | ||

78 | .. code-block:: sql | |

79 | ||

80 | SELECT Sum(ST_Area(the_geom)) / 4047 | |

81 | FROM nyc_neighborhoods | |

82 | WHERE boroname = 'Manhattan'; | |

83 | ||

84 | :: | |

85 | ||

86 | 13965.3201224118 | |

87 | ||

88 | or... | |

89 | ||

90 | .. code-block:: sql | |

91 | ||

92 | SELECT Sum(ST_Area(the_geom)) / 4047 | |

93 | FROM nyc_census_blocks | |

94 | WHERE boroname = 'Manhattan'; | |

95 | ||

96 | :: | |

97 | ||

98 | 14572.1575543757 | |

99 | ||

100 | ||

101 | * **"How many census blocks in New York City have a hole in them?"** | |

102 | ||

103 | .. code-block:: sql | |

104 | ||

105 | SELECT Count(*) | |

106 | FROM nyc_census_blocks | |

107 | WHERE ST_NRings(the_geom) > 1; | |

108 | ||

109 | :: | |

110 | ||

111 | 66 | |

112 | ||

113 | * **"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.) | |

114 | ||

115 | .. code-block:: sql | |

116 | ||

117 | SELECT Sum(ST_Length(the_geom)) / 1000 | |

118 | FROM nyc_streets; | |

119 | ||

120 | :: | |

121 | ||

122 | 10418.9047172 | |

123 | ||

124 | * **"How long is 'Columbus Cir' (Columbus Circle)?** | |

125 | ||

126 | .. code-block:: sql | |

127 | ||

128 | SELECT ST_Length(the_geom) | |

129 | FROM nyc_streets | |

130 | WHERE name = 'Columbus Cir'; | |

131 | ||

132 | :: | |

133 | ||

134 | 308.34199 | |

135 | ||

136 | * **"What is the JSON representation of the boundary of the 'West Village'?"** | |

137 | ||

138 | .. code-block:: sql | |

139 | ||

140 | SELECT ST_AsGeoJSON(the_geom) | |

141 | FROM nyc_neighborhoods | |

142 | WHERE name = 'West Village'; | |

143 | ||

144 | :: | |

145 | ||

146 | {"type":"MultiPolygon","coordinates": | |

147 | [[[[583263.2776595836,4509242.6260239873], | |

148 | [583276.81990686338,4509378.825446927], ... | |

149 | [583263.2776595836,4509242.6260239873]]]]} | |

150 | ||

151 | The geometry type is "MultiPolygon", interesting! | |

152 | ||

153 | ||

154 | * **"How many polygons are in the 'West Village' multipolygon?"** | |

155 | ||

156 | .. code-block:: sql | |

157 | ||

158 | SELECT ST_NumGeometries(the_geom) | |

159 | FROM nyc_neighborhoods | |

160 | WHERE name = 'West Village'; | |

161 | ||

162 | :: | |

163 | ||

164 | 1 | |

165 | ||

166 | .. note:: | |

167 | ||

168 | 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. | |

169 | ||

170 | ||

171 | * **"What is the length of streets in New York City, summarized by type?"** | |

172 | ||

173 | .. code-block:: sql | |

174 | ||

175 | SELECT type, Sum(ST_Length(the_geom)) AS length | |

176 | FROM nyc_streets | |

177 | GROUP BY type | |

178 | ORDER BY length DESC; | |

179 | ||

180 | :: | |

181 | ||

182 | type | length | |

183 | --------------------------------------------------+------------------ | |

184 | residential | 8629870.33786606 | |

185 | motorway | 403622.478126363 | |

186 | tertiary | 360394.879051303 | |

187 | motorway_link | 294261.419479668 | |

188 | secondary | 276264.303897926 | |

189 | unclassified | 166936.371604458 | |

190 | primary | 135034.233017947 | |

191 | footway | 71798.4878378096 | |

192 | service | 28337.635038596 | |

193 | trunk | 20353.5819826076 | |

194 | cycleway | 8863.75144825929 | |

195 | pedestrian | 4867.05032825026 | |

196 | construction | 4803.08162103562 | |

197 | residential; motorway_link | 3661.57506293745 | |

198 | trunk_link | 3202.18981240201 | |

199 | primary_link | 2492.57457083536 | |

200 | living_street | 1894.63905457332 | |

201 | primary; residential; motorway_link; residential | 1367.76576941335 | |

202 | undefined | 380.53861910346 | |

203 | steps | 282.745221342127 | |

204 | motorway_link; residential | 215.07778911517 | |

205 | ||

206 | ||

207 | .. note:: | |

208 | ||

209 | 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. | |

210 | ||

211 | ||

212 | ||

213 | ||

214 |

**Note:**See TracBrowser for help on using the repository browser.