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_advanced.rst @ 60

Revision 54, 8.8 KB checked in by djay, 13 years ago (diff)

Partie jointure spatiales avancée traduite \!

RevLine 
[1]1.. _joins_advanced:
2
[54]3Partie 19 : Plus de jointures spatiales
[50]4=======================================
[1]5
[54]6Dans la partie précédente nous avons vu les fonctions :command:`ST_Centroid(geometry)` et :command:`ST_Union([geometry])` ainsi que quelques exemples simples. Dans cette partie nous réaliseront des choses plus éllaborées.
[1]7
8.. _creatingtractstable:
9
[54]10Création de la table de traçage des recensements
11------------------------------------------------
[1]12
[54]13Dans le répertoire ``\data\`` des travaux pratiques, il y a un fichier qui contient des données attributaires, mais pas de géométries, ce fichier est nommé ``nyc_census_sociodata.sql``. La table contient des données sociaux-économiques interressantes à propos de New York : revenus financiers, éducation .... Il y a juste un problÚme, les données sont rassemblé en "trace de recensement" et nous n'avons pas de données spatiales associées !
[1]14
[54]15Dans cette partie nous allons
[1]16
[54]17 * Charger la table ``nyc_census_sociodata.sql``
18 * Créer une table spatiale pour les traces de recensement
19 * Joindre les données attributaires à nos données spatiales
20 * Réaliser certaines analises sur nos nouvelles données
[1]21 
[54]22Chargement du fichier nyc_census_sociodata.sql
23~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[1]24
[54]25 #. Ouvrez la fenêtre de requêtage SQL depuis PgAdmin
26 #. Selectionnez **File->Open** depuis le menu et naviguez jusqu'au fichier ``nyc_census_sociodata.sql``
27 #. Cliquez sur le bouton "Run Query"
28 #. Si vous cliquez sur le bouton "Refresh" depuis PgAdmin, la liste des table devrait contenir votre nouvelle table ``nyc_census_sociodata``
[1]29 
[54]30Création de la table traces de recensement
31~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[1]32 
[54]33Comme nous l'avons dans la partie précédente, nous pouvons construire des géométries de niveau suppérieur en utilisant nos blocks de base en utilisant une partie de la clef ``blkid``. Afin de calculer les traces de recensement, nous avons besoin de regrouper les blocks en uitlisant les 11 premiers caractÚres de la colonne ``blkid``.
[1]34 
35  ::
36
37    360610001009000 = 36 061 00100 9000
38
39    36     = State of New York
40    061    = New York County (Manhattan)
41    000100 = Census Tract
42    9      = Census Block Group
43    000    = Census Block
44
[54]45Création de la nouvelle table en utilisant la fonction d'agrégation :command:`ST_Union` :
[1]46 
47.. code-block:: sql
48   
[54]49   -- Création de la table
[1]50   CREATE TABLE nyc_census_tract_geoms AS
51   SELECT
52     ST_Union(the_geom) AS the_geom,
53     SubStr(blkid,1,11) AS tractid
54   FROM nyc_census_blocks
55   GROUP BY tractid;
56     
[54]57   -- Indexation du champ tractid
[1]58   CREATE INDEX nyc_census_tract_geoms_tractid_idx ON nyc_census_tract_geoms (tractid);
59     
[54]60   -- Mise à jour de la table geometry_columns
[1]61   SELECT Populate_Geometry_Columns();
62
[54]63Regrouper les données attributaires et spatiales
64~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[1]65
[54]66L'objectif est ici de regrouper les données spatiales que nous avons créé avec les donées attributaires que nous avions chargé initialement.
[1]67 
68.. code-block:: sql
69 
[54]70  -- Création de la table
[1]71  CREATE TABLE nyc_census_tracts AS
72  SELECT
73    g.the_geom,
74    a.*
75  FROM nyc_census_tract_geoms g
76  JOIN nyc_census_sociodata a
77  ON g.tractid = a.tractid;
78   
[54]79  -- Indexation des géométries
[1]80  CREATE INDEX nyc_census_tract_gidx ON nyc_census_tracts USING GIST (the_geom);
81   
[54]82  -- Mise à jour de la table geometry_columns
[1]83  SELECT Populate_Geometry_Columns();
84
85.. _interestingquestion:
86
[54]87Répondre à une question interressante
88~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
[1]89     
[54]90Répondre à une question interressante ! "Lister les 10 meilleurs quartiers ordonnées par la proportion de personne ayant acquis un diplome".
[1]91 
92.. code-block:: sql
93 
94  SELECT
95    Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
96    n.name, n.boroname
97  FROM nyc_neighborhoods n
98  JOIN nyc_census_tracts t
99  ON ST_Intersects(n.the_geom, t.the_geom)
100  WHERE t.edu_total > 0
101  GROUP BY n.name, n.boroname
102  ORDER BY graduate_pct DESC
103  LIMIT 10;
104
[54]105Nous sommons les statistiques qui nous interressent, nous les divisons ensuite à la fin. Afin d'aviter l'erreur de non-division par zero, nous ne prennons pas en compte les quartiers qui n'ont aucune personne ayant obtenu un diplome.
[1]106
107::
108 
109   graduate_pct |       name        | boroname 
110  --------------+-------------------+-----------
111           40.4 | Carnegie Hill     | Manhattan
112           40.2 | Flatbush          | Brooklyn
113           34.8 | Battery Park      | Manhattan
114           33.9 | North Sutton Area | Manhattan
115           33.4 | Upper West Side   | Manhattan
116           33.3 | Upper East Side   | Manhattan
117           32.0 | Tribeca           | Manhattan
118           31.8 | Greenwich Village | Manhattan
119           29.8 | West Village      | Manhattan
120           29.7 | Central Park      | Manhattan
121   
122 
123.. _polypolyjoins:
124
[54]125Polygones/Jointures de polygones
126---------------------------------
[1]127
[54]128Dans notre requête interressante (dans :ref:`interestingquestion`) nous avons utilisé la fonction :command:`ST_Intersects(geometry_a, geometry_b)` pour déterminer quelle entité polygonale à inclure dans chaque groupe de quartier. Ce qui nous conduit à la question : que ce passe-t-il si une entité tombe ntre deux quartier ? Il intersectera chacun d'entre eux et ainsi sera inclu dans **chacun** des résultats.
[1]129
130.. image:: ./screenshots/centroid_neighborhood.png
131
[54]132Pour éviter ce cas de double comptage il existe trois méthodes :
[1]133
[54]134 * La méthode simple consiste a s'assurer que chaque entité ne se retrouve que dans **un** seul groupe géograhique (en utilisant :command:`ST_Centroid(geometry)`)
135 * La méthode complexe consiste à disviser les parties qui se croisent en utilisant les bordures (en utilisant :command:`ST_Intersection(geometry,geometry)`)
[1]136 
[54]137Voici un exemple d'utilisation de la méthode simple pour éviter le double comptage dans notre requête précédente :
[1]138
139.. code-block:: sql
140
141  SELECT
142    Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
143    n.name, n.boroname
144  FROM nyc_neighborhoods n
145  JOIN nyc_census_tracts t
146  ON ST_Contains(n.the_geom, ST_Centroid(t.the_geom))
147  WHERE t.edu_total > 0
148  GROUP BY n.name, n.boroname
149  ORDER BY graduate_pct DESC
150  LIMIT 10;
151 
[54]152Remarquez que la requête prend plus de temps à s'exécuter, puisque la fonction :command:`ST_Centroid` doit être effectuée pour chaque entité.
[1]153
154::
155
156   graduate_pct |       name        | boroname 
157  --------------+-------------------+-----------
158           49.2 | Carnegie Hill     | Manhattan
159           39.5 | Battery Park      | Manhattan
160           34.3 | Upper East Side   | Manhattan
161           33.6 | Upper West Side   | Manhattan
162           32.5 | Greenwich Village | Manhattan
163           32.2 | Tribeca           | Manhattan
164           31.3 | North Sutton Area | Manhattan
165           30.8 | West Village      | Manhattan
166           30.1 | Downtown          | Brooklyn
167           28.4 | Cobble Hill       | Brooklyn
168 
[54]169Éviter le double comptage change le résultat !
[1]170
171
172.. _largeradiusjoins:
173
[54]174Jointures utilisant un large rayon de distance
175----------------------------------------------
[1]176
[54]177Une requête qu'il est sympat de demander est : "Comment les temps de permutation des gens proches (dans un rayon de 500 metres ) des stations de métros diffÚrent de ceuxqui en vive loin ? "
[1]178
[54]179Néanmoins, la question rencontre les même problÚme de double comptage : plusieurs personnes seront dans un rayon de 500 metres de plusieurs stations de métros différentes. Coparons la population de New York :
[1]180
181.. code-block:: sql
182
183  SELECT Sum(popn_total)
184  FROM nyc_census_blocks;
185 
186::
187
188  8008278
189 
[54]190Avec la population des gens de New York dans un rayon de 500 metres d'une station de métros :
[1]191
192.. code-block:: sql
193
194  SELECT Sum(popn_total)
195  FROM nyc_census_blocks census
196  JOIN nyc_subway_stations subway
197  ON ST_DWithin(census.the_geom, subway.the_geom, 500);
198 
199::
200
201  10556898
202
[54]203Il y a plus de personnes proches du métro qu'il y a de peronnes ! Clairement, notre requête SQL simple rencontre un gros problÚme de double comptage. Vous pouvez voir le problÚme en regardant l'image des zones tampons créées pour les stations.
[1]204
205.. image:: ./screenshots/subways_buffered.png
206
[54]207La solution est de s'assurer que nous avons seulement des blocks distincts avant de les les regrouper. Nou spouvons réaliser cela en cassant notre requête en sous-requêtes qui récupÚre les blocks distincts, regroupé ensuite pour retrouner notre réponse :
[1]208
209.. code-block:: sql
210
211  SELECT Sum(popn_total)
212  FROM (
213    SELECT DISTINCT ON (blkid) popn_total
214    FROM nyc_census_blocks census
215    JOIN nyc_subway_stations subway
216    ON ST_DWithin(census.the_geom, subway.the_geom, 500)
217  ) AS distinct_blocks;
218 
219::
220
221  4953599
222
[54]223C'est mieux ! Donc un peu plus de 50 % de la population de New York vit à proximité (50m environ 5 à 7 minutes de marche) du métro.
[1]224
225
226
Note: See TracBrowser for help on using the repository browser.