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

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

Initial import of the svn tree

RevLine 
[1]1.. _spatial_relationships_exercises:
2
3Section 11: Spatial Relationships Exercises
4===========================================
5
6Here'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
21Also 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
39Exercises
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   
Note: See TracBrowser for help on using the repository browser.