Bienvenue sur PostGIS.fr

Bienvenue sur PostGIS.fr , le site de la communauté des utilisateurs francophones de PostGIS.

PostGIS ajoute le support d'objets géographique à la base de données PostgreSQL. En effet, PostGIS "spatialise" le serverur PostgreSQL, ce qui permet de l'utiliser comme une base de données SIG.

Maintenu à jour, en fonction de nos disponibilités et des diverses sorties des outils que nous testons, nous vous proposons l'ensemble de nos travaux publiés en langue française.

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

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

Initial import of the svn tree

RevLine 
[1]1.. _geometries_exercises:
2
3Section 9: Geometry Exercises
4=============================
5
6Here'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
39Also 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
57Exercises
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
151The 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.