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/joins.rst @ 33

Revision 26, 12.6 KB checked in by djay, 13 years ago (diff)

Utilisation de l'encodage en UTF-8, problème avec mon nouveau emacs :)

RevLine 
[1]1.. _joins:
2
[25]3Partie 12 : les jointures spatiales
4===================================
[1]5
[26]6Les jointures saptiales est la cerise sur le gâteau des base de données spatiales. Elles vous pemettent de conbiner les informations de plusieures tables en utilisant une relation spatiale comme clause de jointure. Les plupart des "analyses SIG standards" peuvent être exprimées à l'aide de jointure spatiales.
[1]7
[26]8Dans la partie prcédente, nous avons utilisé les relations spatiales en utilisant deux étapes dans nos requêtes : nous avons dans un premier temps extrait la station de métro "Broad St" puis nous avon utilisé ce résultat dans nos autres requêtes pour répondre aux questions comme "dans quel quartier se situ la station 'Broad St' ?"
[1]9
[26]10En utilisant les jointures spatiales, nous pouvons répondre aux questions en une seule étape, récupérant les informations relatives à la station de métro et le quartie la contenant :
[1]11
12.. code-block:: sql
13
14  SELECT
15    subways.name AS subway_name,
16    neighborhoods.name AS neighborhood_name,
17    neighborhoods.boroname AS borough
18  FROM nyc_neighborhoods AS neighborhoods
19  JOIN nyc_subway_stations AS subways
20  ON ST_Contains(neighborhoods.the_geom, subways.the_geom)
21  WHERE subways.name = 'Broad St';
22
23:: 
24
25   subway_name | neighborhood_name  |  borough 
26  -------------+--------------------+-----------
27   Broad St    | Financial District | Manhattan
28
[26]29Nous avons pu regrouper chaque station de métros avec le quartier duquel elle fait partie, mais dans ce cas nous n'en voulions qu'une. Chaque fonctionn qui envoit un résultat du type vrai/faux peut être utilisée pour joindre spatialement deux tables, mais la plupart du temps on utilise : :command:`ST_Intersects`, :command:`ST_Contains`, et :command:`ST_DWithin`.
[1]30
[25]31Jointure et regroupement
32------------------------
[1]33
[26]34La combinaison de ``JOIN`` avec ``GROUP BY`` fournit le type d'analyse qui est courramment utilisé dans les systÚmes SIG.
[1]35
[26]36Par exemple : **Quelle est la population et la répartition raciale du quartier de Manhattan ?** Ici nous avons une question qui combine les informations relatives à la population recenssée et les contours des quartier, or nous ne voulons qu'un seul quartier, celui de Manhattan.
[1]37
38.. code-block:: sql
39
40  SELECT
41    neighborhoods.name AS neighborhood_name,
42    Sum(census.popn_total) AS population,
43    Round(100.0 * Sum(census.popn_white) / Sum(census.popn_total),1) AS white_pct,
44    Round(100.0 * Sum(census.popn_black) / Sum(census.popn_total),1) AS black_pct
45  FROM nyc_neighborhoods AS neighborhoods
46  JOIN nyc_census_blocks AS census
47  ON ST_Intersects(neighborhoods.the_geom, census.the_geom)
48  WHERE neighborhoods.boroname = 'Manhattan'
49  GROUP BY neighborhoods.name
50  ORDER BY white_pct DESC;
51
52::
53
54   neighborhood_name  | population | white_pct | black_pct
55 ---------------------+------------+-----------+-----------
56  Carnegie Hill       |      19909 |      91.6 |       1.5
57  North Sutton Area   |      21413 |      90.3 |       1.2
58  West Village        |      27141 |      88.1 |       2.7
59  Upper East Side     |     201301 |      87.8 |       2.5
60  Greenwich Village   |      57047 |      84.1 |       3.3
61  Soho                |      15371 |      84.1 |       3.3
62  Murray Hill         |      27669 |      79.2 |       2.3
63  Gramercy            |      97264 |      77.8 |       5.6
64  Central Park        |      49284 |      77.8 |      10.4
65  Tribeca             |      13601 |      77.2 |       5.5
66  Midtown             |      70412 |      75.9 |       5.1
67  Chelsea             |      51773 |      74.7 |       7.4
68  Battery Park        |       9928 |      74.1 |       4.9
69  Upper West Side     |     212499 |      73.3 |      10.4
70  Financial District  |      17279 |      71.3 |       5.3
71  Clinton             |      26347 |      64.6 |      10.3
72  East Village        |      77448 |      61.4 |       9.7
73  Garment District    |       6900 |      51.1 |       8.6
74  Morningside Heights |      41499 |      50.2 |      24.8
75  Little Italy        |      14178 |      39.4 |       1.2
76  Yorkville           |      57800 |      31.2 |      33.3
77  Inwood              |      50922 |      29.3 |      14.9
78  Lower East Side     |     104690 |      28.3 |       9.0
79  Washington Heights  |     187198 |      26.9 |      16.3
80  East Harlem         |      62279 |      20.2 |      46.2
81  Hamilton Heights    |      71133 |      14.6 |      41.1
82  Chinatown           |      18195 |      10.3 |       4.2
83  Harlem              |     125501 |       5.7 |      80.5
84
85
[26]86Que ce passe-t-il ici ?  Voici ce qui se passe (l'ordre d'évaluation est optimisé par la base de données) :
[1]87
[26]88#. La clause ``JOIN`` crée une table cirtuelle qui contient les colonnes à la fois des quartier et des recensements (tables neighborhoods et census).
89#. La claue ``WHERE`` filtre la table virtuelle pour ne conserver que la ligne correspondant à Manhattan.
90#. Les lignes restantes sont regroupées par le nom du quartier et sont utilisées par la fonction d'agrégation : :command:`Sum()` pour réaliser la somme des valeurs de la populations.
91#. AprÚs un peu d'arythmétiques et de formatage (ex: ``GROUP BY``, ``ORDER BY``)) ssur le nombres finaux, notre requête calcul les porcentages.
[1]92
93.. note:: 
94
[26]95   La clause ``JOIN`` combine deux parties ``FROM``.  Par défaut, nous utilisons un jointure du type :``INNER JOIN``, mais il existe quatres autres types de jointures. Pour de plus amples informations à ce sujet, consultez la partie `type_jointure <http://docs.postgresql.fr/9.1/sql-select.html>`_ de la page de la documentation officielle de PostgreSQL.
[1]96
[26]97Nous pouvons aussi utiliser le test de la distance dans notre clef de jointure, pour créer une regroupement de "tout les éléments dans un certain rayon". Essayons de regarder la géographie raciale de New York en utilisant les requêtes de distance.
[1]98
[26]99PremiÚrement, essayons d'obtenir la répartition raciale de la ville.
[1]100
101.. code-block:: sql
102
103  SELECT
104    100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
105    100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
106    Sum(popn_total) AS popn_total
107  FROM nyc_census_blocks;
108
109:: 
110
111        white_pct      |      black_pct      | popn_total
112  ---------------------+---------------------+------------
113   44.6586020115685295 | 26.5945063345703034 |    8008278
114
115
[25]116Donc, 8M de personnes de New York, environ 44% sont "blancs" et "26% sont "noirs".
[1]117
[26]118Duke Ellington chantait que "You / must take the A-train / To / go to Sugar Hill way up in Harlem." Comme nous l'avons vu précédemment, Harlem est de trÚs loin le quartier ou se trouve la plus grande concentration d'africains-américains de Manhattan (80.5%). Est-il toujours vrai qu'il faut prendre le train A dont Duke parlait dans sa chanson ?
[1]119
[26]120PremiÚrement, le contenu du champ ``routes`` de la table ``nyc_subway_stations`` va nous servir à récupérer le train A. Les valeurs de ce champs sont un peu complexes.
[1]121
122.. code-block:: sql
123
124  SELECT DISTINCT routes FROM nyc_subway_stations;
125 
126:: 
127
128 A,C,G
129 4,5
130 D,F,N,Q
131 5
132 E,F
133 E,J,Z
134 R,W
135
136.. note::
137
[26]138   Le mot clef ``DISTINCT`` permet d'éliminer les répétitions de lignes de notre résultat. Dans ce mot clef, notre requête renverrait 491 résultats au lieu de 73.
[1]139   
[26]140Donc pour trouver le train A, nous allons demander toute les lignes ayant pour ``routes`` la valeur 'A'. Nous pouvons faire cela de différentes maniÚres, mais nous utiliserons aujourd'hui le fait que la fonction :command:`strpos(routes,'A')` retourne un entier différent de 0 si la lettre 'A' se trouve dans la valeur du champs route.
[1]141
142.. code-block:: sql
143
144   SELECT DISTINCT routes
145   FROM nyc_subway_stations AS subways
146   WHERE strpos(subways.routes,'A') > 0;
147   
148::
149
150  A,B,C
151  A,C
152  A
153  A,C,G
154  A,C,E,L
155  A,S
156  A,C,F
157  A,B,C,D
158  A,C,E
159 
[26]160Essayons de regrouper la répartition racialedans un rayon de 200 mÚtres de la ligne du train A.
[1]161
162.. code-block:: sql
163
164  SELECT
165    100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
166    100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
167    Sum(popn_total) AS popn_total
168  FROM nyc_census_blocks AS census
169  JOIN nyc_subway_stations AS subways
170  ON ST_DWithin(census.the_geom, subways.the_geom, 200)
171  WHERE strpos(subways.routes,'A') > 0;
172
173::
174
175        white_pct      |      black_pct      | popn_total
176  ---------------------+---------------------+------------
177   42.0805466940877366 | 23.0936148851067964 |     185259
178
[26]179La répartition raciale le long de la ligne du train A n'est pas radicallement différente de la répartition générale de la ville de New York.
[1]180
[26]181Jointres avancées
[25]182-----------------
[1]183
[26]184Dans la derniÚre partie nous avons vu que le train A n'est pas utilisé par des populations si éloignées de la répartition totale du reste de la ville. Y-a-t-il des train qui passent par des parties de la ville qui ne sont pas dans la moyenne de la répartition raciale ?
[1]185
[26]186Pour répondre à cette question, nous ajouterons une nouvelle jointure à notre requête, de telle maniÚre que nous puissions calculer simultanément la répartition raciale de plusieures lignes de métros à la fois. Pour faire ceci, nous créerons une table qui permettra d'énumérer toutes les lignes que nous voulons regrouper.
[1]187
188.. code-block:: sql
189
190    CREATE TABLE subway_lines ( route char(1) );
191    INSERT INTO subway_lines (route) VALUES
192      ('A'),('B'),('C'),('D'),('E'),('F'),('G'),
193      ('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
194      ('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
195      ('7');
196
[26]197Maintenant nous pouvons joindre les tables des lignes de métros à notre requête précédente.
[1]198
199.. code-block:: sql
200
201    SELECT
202      lines.route,
203      Round(100.0 * Sum(popn_white) / Sum(popn_total), 1) AS white_pct,
204      Round(100.0 * Sum(popn_black) / Sum(popn_total), 1) AS black_pct,
205      Sum(popn_total) AS popn_total
206    FROM nyc_census_blocks AS census
207    JOIN nyc_subway_stations AS subways
208    ON ST_DWithin(census.the_geom, subways.the_geom, 200)
209    JOIN subway_lines AS lines
210    ON strpos(subways.routes, lines.route) > 0
211    GROUP BY lines.route
212    ORDER BY black_pct DESC;
213
214::
215
216     route | white_pct | black_pct | popn_total
217    -------+-----------+-----------+------------
218     S     |      30.1 |      59.5 |      32730
219     3     |      34.3 |      51.8 |     201888
220     2     |      33.6 |      45.5 |     535414
221     5     |      32.1 |      45.1 |     407324
222     C     |      41.3 |      35.9 |     430194
223     4     |      34.7 |      30.9 |     328292
224     B     |      36.1 |      30.6 |     261186
225     Q     |      52.9 |      26.3 |     259820
226     J     |      29.5 |      23.6 |     126764
227     A     |      42.1 |      23.1 |     370518
228     Z     |      29.5 |      21.5 |      81493
229     D     |      39.8 |      20.9 |     233855
230     G     |      44.8 |      20.0 |     138602
231     L     |      53.9 |      17.1 |     104140
232     6     |      52.7 |      16.3 |     257769
233     1     |      54.8 |      12.6 |     659028
234     F     |      60.0 |       8.6 |     438212
235     M     |      50.0 |       7.8 |     166721
236     E     |      69.4 |       5.3 |      86118
237     R     |      57.7 |       4.8 |     389124
238     7     |      42.4 |       3.8 |     107543
239
240
[26]241Comme précédemment, les jointure créent une table virtuelle de toutes les combinaisons possible disponibles à l'aide des contraintes de type ``JOIN ON`, ces lignes sont ensuite utilisées dans le regroupement ``GROUP``. La magie spatiale tiend dans l'utilisation de la fonction ``ST_DWithin`` qui s'assure que les bloques sont suffisamment proches des lignes de métros inclues dans le calcul.
[1]242
[25]243Liste de fonctions
244------------------
[1]245
[26]246`ST_Contains(geometry A, geometry B) <http://postgis.org/docs/ST_Contains.html>`_: retourne TRUE si et seulement si aucun points de B est à l'extérieur de A, et si au moins un point à l'intérieur de B  est à l'intérieur de A.
[1]247
[26]248`ST_DWithin(geometry A, geometry B, radius) <http://postgis.org/docs/ST_DWithin.html>`_: retourne TRUE si les géométries sont distantes du rayon donné.
[1]249
[26]250`ST_Intersects(geometry A, geometry B) <http://postgis.org/docs/ST_Intersects.html>`_: retourne TRUE si les géométries/géographies "s'intersectent spatialement" (partage une portiond de l'espace) et FALSE sinon (elles sont dijointes).
[1]251
[26]252`round(v numeric, s integer) <http://www.postgresql.org/docs/7.4/interactive/functions-math.html>`_: fonction de PostgreSQL qui arrondit à s décimales.
[1]253
[26]254`strpos(chaîne, sous-chaîne) <http://www.postgresql.org/docs/current/static/functions-string.html>`_: fonction de chaîne de caractÚres de PostgreSQL qui retourne la position de la sous-chaine.
[1]255
[26]256`sum(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: fonction d'agrégation de PostgreSQL qui retourne la somme d'un ensemble de valeurs.
[1]257
258.. rubric:: Footnotes
259
260.. [#PostGIS_Doco] http://postgis.org/documentation/manual-1.5/
261
Note: See TracBrowser for help on using the repository browser.