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.rst @ 5

Revision 1, 18.5 KB checked in by djay, 13 years ago (diff)

Initial import of the svn tree

RevLine 
[1]1.. _geometries:
2
3Section 8: Geometries
4=====================
5
6Introduction
7------------
8
9In the previous :ref:`section <loading_data>`, we loaded a variety of data.  Before we start playing with our data lets have a look at some simpler examples.  In pgAdmin, once again select the **nyc** database and open the SQL query tool.  Paste this example SQL code into the pgAdmin SQL Editor window (removing any text that may be there by default) and then execute.
10
11.. code-block:: sql
12
13  CREATE TABLE geometries (name varchar, geom geometry);
14 
15  INSERT INTO geometries VALUES
16    ('Point', 'POINT(0 0)'),
17    ('Linestring', 'LINESTRING(0 0, 1 1, 2 1, 2 2)'),
18    ('Polygon', 'POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
19    ('PolygonWithHole', 'POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))'),
20    ('Collection', 'GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))');
21   
22  SELECT Populate_Geometry_Columns();
23 
24  SELECT name, ST_AsText(geom) FROM geometries;
25
26.. image:: ./geometries/start01.png
27
28The above example CREATEs a table (**geometries**) then INSERTs five geometries: a point, a line, a polygon, a polygon with a hole, and a collection. Finally, the inserted rows are SELECTed and displayed in the Output pane.
29
30Metadata Tables
31---------------
32
33In conformance with the Simple Features for SQL (:term:`SFSQL`) specification, PostGIS provides two tables to track and report on the geometry types available in a given database.
34
35* The first table, ``spatial_ref_sys``, defines all the spatial reference systems known to the database and will be described in greater detail later. 
36* The second table, ``geometry_columns``, provides a listing of all "features" (defined as an object with geometric attributes), and the basic details of those features. 
37
38.. image:: ./geometries/table01.png
39
40In our introductory example, the :command:`Populate_Geometry_Columns()` function finds all the columns in the database that contain geometry and updates the ``geometry_columns`` table to include references to them. 
41
42Lets have a look at the ``geometry_columns`` table in our database.  Paste this command in the Query Tool as before:
43
44.. code-block:: sql
45
46  SELECT * FROM geometry_columns;
47
48.. image:: ./geometries/start08.png
49
50* ``f_table_catalog``, ``f_table_schema``, and ``f_table_name`` provide the fully qualified name of the feature table containing a given geometry.  Because PostgreSQL doesn't make use of catalogs, ``f_table_catalog`` will tend to be empty. 
51* ``f_geometry_column`` is the name of the column that geometry containing column -- for feature tables with multiple geometry columns, there will be one record for each. 
52* ``coord_dimension`` and ``srid`` define the the dimension of the geometry (2-, 3- or 4-dimensional) and the Spatial Reference system identifier that refers to the ``spatial_ref_sys`` table respectively. 
53* The ``type`` column defines the type of geometry as described below; we've seen Point and Linestring types so far. 
54
55By querying this table, GIS clients and libraries can determine what to expect when retrieving data and can perform any necessary projection, processing or rendering without needing to inspect each geometry.
56
57Representing Real World Objects
58-------------------------------
59
60The Simple Features for SQL (:term:`SFSQL`) specification, the original guiding standard for PostGIS development, defines how a real world object is represented.  By taking a continuous shape and digitizing it at a fixed resolution we achieve a passable representation of the object.  SFSQL only handled 2-dimensional representations.  PostGIS has extended that to include 3- and 4-dimensional representations; more recently the SQL-Multimedia Part 3 (:term:`SQL/MM`) specification has officially defined their own representation. 
61
62Our example table contains a mixture of different geometry types. We can collect general information about each object using functions that read the geometry metadata.
63
64 * :command:`ST_GeometryType(geometry)` returns the type of the geometry
65 * :command:`ST_NDims(geometry)` returns the number of dimensions of the geometry
66 * :command:`ST_SRID(geometry)` returns the spatial reference identifier number of the geometry
67
68.. code-block:: sql
69
70  SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)
71    FROM geometries;
72
73::
74
75       name       |    st_geometrytype    | st_ndims | st_srid
76 -----------------+-----------------------+----------+---------
77  Point           | ST_Point              |        2 |      -1
78  Polygon         | ST_Polygon            |        2 |      -1
79  PolygonWithHole | ST_Polygon            |        2 |      -1
80  Collection      | ST_GeometryCollection |        2 |      -1
81  Linestring      | ST_LineString         |        2 |      -1
82
83
84
85Points
86~~~~~~
87
88.. image:: ./introduction/points.png
89   :align: center
90
91A spatial **point** represents a single location on the Earth.  This point is represented by a single coordinate (including either 2-, 3- or 4-dimensions).  Points are used to represent objects when the exact details, such as shape and size, are not important at the target scale.  For example, cities on a map of the world can be described as points, while a map of a single state might represent cities as polygons. 
92
93.. code-block:: sql
94
95  SELECT ST_AsText(geom)
96    FROM geometries
97    WHERE name = 'Point';
98
99::
100
101  POINT(0 0)
102
103Some of the specific spatial functions for working with points are:
104
105 * :command:`ST_X(geometry)` returns the X ordinate
106 * :command:`ST_Y(geometry)` returns the Y ordinate
107
108So, we can read the ordinates from a point like this:
109
110.. code-block:: sql
111
112  SELECT ST_X(geom), ST_Y(geom)
113    FROM geometries
114    WHERE name = 'Point';
115
116The New York City subway stations (``nyc_subway_stations``) table is a data set represented as points. The following SQL query will return the geometry associated with one point (in the :command:`ST_AsText` column).
117
118.. code-block:: sql
119
120  SELECT name, ST_AsText(the_geom)
121    FROM nyc_subway_stations
122    LIMIT 1;
123
124
125Linestrings
126~~~~~~~~~~~
127
128.. image:: ./introduction/lines.png
129   :align: center
130
131A **linestring** is a path between locations.  It takes the form of an ordered series of two or more points.  Roads and rivers are typically represented as linestrings.  A linestring is said to be **closed** if it starts and ends on the same point.  It is said to be **simple** if it does not cross or touch itself (except at its endpoints if it is closed).  A linestring can be both **closed** and **simple**.
132
133The street network for New York (``nyc_streets``) was loaded earlier in the workshop.  This dataset contains details such as name, and type.  A single real world street may consist of many linestrings, each representing a segment of road with different attributes.
134
135The following SQL query will return the geometry associated with one linestring (in the :command:`ST_AsText` column).
136
137.. code-block:: sql
138
139  SELECT ST_AsText(geom)
140    FROM geometries
141    WHERE name = 'Linestring';
142 
143::
144
145  LINESTRING(0 0, 1 1, 2 1, 2 2)
146
147Some of the specific spatial functions for working with linestrings are:
148
149 * :command:`ST_Length(geometry)` returns the length of the linestring
150 * :command:`ST_StartPoint(geometry)` returns the first coordinate as a point
151 * :command:`ST_EndPoint(geometry)` returns the last coordinate as a point
152 * :command:`ST_NPoints(geometry)` returns the number of coordinates in the linestring
153
154So, the length of our linestring is:
155
156.. code-block:: sql
157
158  SELECT ST_Length(geom)
159    FROM geometries
160    WHERE name = 'Linestring';
161
162::
163
164  3.41421356237309
165
166
167Polygons
168~~~~~~~~
169
170.. image:: ./introduction/polygons.png
171  :align: center
172
173A polygon is a representation of an area.  The outer boundary of the polygon is represented by a ring.  This ring is a linestring that is both closed and simple as defined above.  Holes within the polygon are also represented by rings.
174
175Polygons are used to represent objects whose size and shape are important.  City limits, parks, building footprints or bodies of water are all commonly represented as polygons when the scale is sufficiently high to see their area.  Roads and rivers can sometimes be represented as polygons.
176
177The following SQL query will return the geometry associated with one linestring (in the :command:`ST_AsText` column).
178
179.. code-block:: sql
180
181  SELECT ST_AsText(geom)
182    FROM geometries
183    WHERE name LIKE 'Polygon%';
184
185.. note::
186
187  Rather than using an ``=`` sign in our ``WHERE`` clause, we are using the ``LIKE`` operator to carry out a string matching operation. You may be used to the ``*`` symbol as a "glob" for pattern matching, but in SQL the ``%`` symbol is used, along with the ``LIKE`` operator to tell the system to do globbing.
188
189::
190
191 POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))
192 POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))
193
194The first polygon has only one ring. The second one has an interior "hole". Most graphics systems include the concept of a "polygon", but GIS systems are relatively unique in allowing polygons to explicitly have holes.
195
196.. image:: ./screenshots/polygons.png
197
198Some of the specific spatial functions for working with polygons are:
199
200 * :command:`ST_Area(geometry)` returns the area of the polygons
201 * :command:`ST_NRings(geometry)` returns the number of rings (usually 1, more of there are holes)
202 * :command:`ST_ExteriorRing(geometry)` returns the outer ring as a linestring
203 * :command:`ST_InteriorRingN(geometry,n)` returns a specified interior ring as a linestring
204 * :command:`ST_Perimeter(geometry)` returns the length of all the rings
205
206We can calculate the area of our polygons using the area function:
207
208.. code-block:: sql
209
210  SELECT name, ST_Area(geom)
211    FROM geometries
212    WHERE name LIKE 'Polygon%';
213
214::
215
216  Polygon            1
217  PolygonWithHole    99
218
219Note that the polygon with a hole has an area that is the area of the outer shell (a 10x10 square) minus the area of the hole (a 1x1 square).
220
221Collections
222~~~~~~~~~~~
223
224There are four collection types, which group multiple simple geometries into sets. 
225
226 * **MultiPoint**, a collection of points
227 * **MultiLineString**, a collection of linestrings
228 * **MultiPolygon**, a collection of polygons
229 * **GeometryCollection**, a heterogeneous collection of any geometry (including other collections)
230
231Collections are another concept that shows up in GIS software more than in generic graphics software. They are useful for directly modeling real world objects as spatial objects. For example, how to model a lot that is split by a right-of-way? As a **MultiPolygon**, with a part on either side of the right-of-way.
232
233.. image:: ./screenshots/collection2.png
234
235Our example collection contains a polygon and a point:
236
237.. code-block:: sql
238
239  SELECT name, ST_AsText(geom)
240    FROM geometries
241    WHERE name = 'Collection';
242
243::
244
245  GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))
246
247.. image:: ./screenshots/collection.png
248
249Some of the specific spatial functions for working with collections are:
250
251 * :command:`ST_NumGeometries(geometry)` returns the number of parts in the collection
252 * :command:`ST_GeometryN(geometry,n)` returns the specified part
253 * :command:`ST_Area(geometry)` returns the total area of all polygonal parts
254 * :command:`ST_Length(geometry)` returns the total length of all linear parts
255
256
257
258Geometry Input and Output
259-------------------------
260
261Within the database, geometries are stored on disk in a format only used by the PostGIS program. In order for external programs to insert and retrieve useful geometries, they need to be converted into a format that other applications can understand. Fortunately, PostGIS supports emitting and consuming geometries in a large number of formats:
262
263 * Well-known text (:term:`WKT`)
264 
265   * :command:`ST_GeomFromText(text)` returns ``geometry``
266   * :command:`ST_AsText(geometry)` returns ``text``
267   * :command:`ST_AsEWKT(geometry)` returns ``text``
268   
269 * Well-known binary (:term:`WKB`)
270 
271   * :command:`ST_GeomFromWKB(bytea)` returns ``geometry``
272   * :command:`ST_AsBinary(geometry)` returns ``bytea``
273   * :command:`ST_AsEWKB(geometry)` returns ``bytea``
274   
275 * Geographic Mark-up Language (:term:`GML`)
276 
277   * :command:`ST_GeomFromGML(text)` returns ``geometry``
278   * :command:`ST_AsGML(geometry)` returns ``text``
279   
280 * Keyhole Mark-up Language (:term:`KML`)
281 
282   * :command:`ST_GeomFromKML(text)` returns ``geometry``
283   * :command:`ST_AsKML(geometry)` returns ``text``
284   
285 * :term:`GeoJSON`
286 
287   * :command:`ST_AsGeoJSON(geometry)` returns ``text``
288   
289 * Scalable Vector Graphics (:term:`SVG`)
290 
291   * :command:`ST_AsSVG(geometry)` returns ``text``
292 
293The following SQL query shows an example of :term:`WKB` representation (the call to :command:`encode()` is required to convert the binary output into an ASCII form for printing):
294
295.. code-block:: sql
296
297  SELECT encode(
298    ST_AsBinary(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)')),
299    'hex');
300
301.. image:: ./geometries/represent-04.png
302
303For the purposes of this workshop we will continue to use WKT to ensure you can read and understand the geometries we're viewing.  However, most actual processes, such as viewing data in a GIS application, transferring data to a web service, or processing data remotely, WKB is the format of choice. 
304
305Since WKT and WKB were defined in the  :term:`SFSQL` specification, they do not handle 3- or 4-dimensional geometries.  For these cases PostGIS has defined the Extended Well Known Text (EWKT) and Extended Well Known Binary (EWKB) formats.  These provide the same formatting capabilities of WKT and WKB with the added dimensionality.
306
307Here is an example of a 3D linestring in WKT:
308
309.. code-block:: sql
310
311  SELECT ST_AsEWKT(ST_GeometryFromText('LINESTRING(0 0 0,1 0 0,1 1 2)'));
312
313.. image:: ./geometries/represent-05.png
314
315.. code-block:: sql
316
317  SELECT encode(ST_AsEWKB(ST_GeometryFromText(
318      'LINESTRING(0 0 0,1 0 0,1 1 2)')), 'hex');
319
320.. image:: ./geometries/represent-06.png
321
322In addition to emitters for the various forms (WKT, WKB, GML, KML, JSON, SVG), PostGIS also has consumers for four (WKT, WKB, GML, KML). Most applications use the WKT or WKB geometry creation functions, but the others work too. Here's an example that consumes GML and output JSON:
323
324.. code-block:: sql
325
326  SELECT ST_AsGeoJSON(ST_GeomFromGML('<gml:Point><gml:coordinates>1,1</gml:coordinates></gml:Point>'));
327
328.. image:: ./geometries/represent-07.png
329
330Function List
331-------------
332
333`Populate_Geometry_Columns <http://postgis.org/docs/Populate_Geometry_Columns.html>`_: Ensures geometry columns have appropriate spatial constraints and exist in the geometry_columns table..
334
335`ST_Area <http://postgis.org/docs/ST_Area.html>`_: Returns the area of the surface if it is a polygon or multi-polygon. For "geometry" type area is in SRID units. For "geography" area is in square meters.
336
337`ST_AsText <http://postgis.org/docs/ST_AsText.html>`_: Returns the Well-Known Text (WKT) representation of the geometry/geography without SRID metadata.
338
339`ST_AsBinary <http://postgis.org/docs/ST_AsBinary.html>`_: Returns the Well-Known Binary (WKB) representation of the geometry/geography without SRID meta data.
340
341`ST_EndPoint <http://postgis.org/docs/ST_EndPoint.html>`_: Returns the last point of a LINESTRING geometry as a POINT.
342
343`ST_AsEWKB <http://postgis.org/docs/ST_AsEWKB.html>`_: Returns the Well-Known Binary (WKB) representation of the geometry with SRID meta data.
344
345`ST_AsEWKT <http://postgis.org/docs/ST_AsEWKT.html>`_: Returns the Well-Known Text (WKT) representation of the geometry with SRID meta data.
346
347`ST_AsGeoJSON <http://postgis.org/docs/ST_AsGeoJSON.html>`_: Returns the geometry as a GeoJSON element.
348
349`ST_AsGML <http://postgis.org/docs/ST_AsGML.html>`_: Returns the geometry as a GML version 2 or 3 element.
350
351`ST_AsKML <http://postgis.org/docs/ST_AsKML.html>`_: Returns the geometry as a KML element. Several variants. Default version=2, default precision=15.
352
353`ST_AsSVG <http://postgis.org/docs/ST_AsSVG.html>`_: Returns a Geometry in SVG path data given a geometry or geography object.
354
355`ST_ExteriorRing <http://postgis.org/docs/ST_ExteriorRing.html>`_: Returns a line string representing the exterior ring of the POLYGON geometry. Return NULL if the geometry is not a polygon. Will not work with MULTIPOLYGON
356
357`ST_GeometryN <http://postgis.org/docs/ST_GeometryN.html>`_: Returns the 1-based Nth geometry if the geometry is a GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE or MULTIPOLYGON. Otherwise, return NULL.
358
359`ST_GeomFromGML <http://postgis.org/docs/ST_GeomFromGML.html>`_: Takes as input GML representation of geometry and outputs a PostGIS geometry object.
360
361`ST_GeomFromKML <http://postgis.org/docs/ST_GeomFromKML.html>`_: Takes as input KML representation of geometry and outputs a PostGIS geometry object
362
363`ST_GeomFromText <http://postgis.org/docs/ST_GeomFromText.html>`_: Returns a specified ST_Geometry value from Well-Known Text representation (WKT).
364
365`ST_GeomFromWKB <http://postgis.org/docs/ST_GeomFromWKB.html>`_: Creates a geometry instance from a Well-Known Binary geometry representation (WKB) and optional SRID.
366
367`ST_GeometryType <http://postgis.org/docs/ST_GeometryType.html>`_: Returns the geometry type of the ST_Geometry value.
368
369`ST_InteriorRingN <http://postgis.org/docs/ST_InteriorRingN.html>`_: Returns the Nth interior linestring ring of the polygon geometry. Return NULL if the geometry is not a polygon or the given N is out of range.
370
371`ST_Length <http://postgis.org/docs/ST_Length.html>`_: Returns the 2d length of the geometry if it is a linestring or multilinestring. geometry are in units of spatial reference and geography are in meters (default spheroid)
372
373`ST_NDims <http://postgis.org/docs/ST_NDims.html>`_: Returns coordinate dimension of the geometry as a small int. Values are: 2,3 or 4.
374
375`ST_NPoints <http://postgis.org/docs/ST_NPoints.html>`_: Returns the number of points (vertexes) in a geometry.
376
377`ST_NRings <http://postgis.org/docs/ST_NRings.html>`_: If the geometry is a polygon or multi-polygon returns the number of rings.
378
379`ST_NumGeometries <http://postgis.org/docs/ST_NumGeometries.html>`_: If geometry is a GEOMETRYCOLLECTION (or MULTI*) returns the number of geometries, otherwise return NULL.
380
381`ST_Perimeter <http://postgis.org/docs/ST_Perimeter.html>`_: Returns the length measurement of the boundary of an ST_Surface or ST_MultiSurface value. (Polygon, Multipolygon)
382
383`ST_SRID <http://postgis.org/docs/ST_SRID.html>`_: Returns the spatial reference identifier for the ST_Geometry as defined in spatial_ref_sys table.
384
385`ST_StartPoint <http://postgis.org/docs/ST_StartPoint.html>`_: Returns the first point of a LINESTRING geometry as a POINT.
386
387`ST_X <http://postgis.org/docs/ST_X.html>`_: Returns the X coordinate of the point, or NULL if not available. Input must be a point.
388
389`ST_Y <http://postgis.org/docs/ST_Y.html>`_: Returns the Y coordinate of the point, or NULL if not available. Input must be a point.
390
391
Note: See TracBrowser for help on using the repository browser.