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.


Ignore:
Timestamp:
17/03/2012 00:49:40 (13 years ago)
Author:
thomasg
Message:

Fin correction typo et orthographe V2 du document

File:
1 edited

Legend:

Unmodified
Added
Removed

  • trunk/workshop-foss4g/joins_advanced.rst

    r54 r62  
    44======================================= 
    55 
    6 Dans 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. 
     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éaliserons des choses plus élaborées. 
    77 
    88.. _creatingtractstable: 
    … …  
    1111------------------------------------------------ 
    1212 
    13 Dans 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 ! 
     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 intéressantes à propos de New York : revenus financiers, éducation .... Il y a juste un problÚme, les données sont rassemblées en "trace de recensement" et nous n'avons pas de données spatiales associées ! 
    1414 
    1515Dans cette partie nous allons 
    … …  
    1818 * Créer une table spatiale pour les traces de recensement 
    1919 * Joindre les données attributaires à nos données spatiales 
    20  * Réaliser certaines analises sur nos nouvelles données 
    21   
     20 * Réaliser certaines analyses sur nos nouvelles données 
     21 
    2222Chargement du fichier nyc_census_sociodata.sql 
    2323~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    2424 
    2525 #. 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`` 
     26 #. Sélectionnez **File->Open** depuis le menu et naviguez jusqu'au fichier ``nyc_census_sociodata.sql`` 
    2727 #. 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`` 
    29   
     28 #. Si vous cliquez sur le bouton "Refresh" depuis PgAdmin, la liste des tables devrait contenir votre nouvelle table ``nyc_census_sociodata`` 
     29 
    3030Création de la table traces de recensement 
    31 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    32   
    33 Comme 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``.  
    34   
     31~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
     32 
     33Comme nous l'avons fait dans la partie précédente, nous pouvons construire des géométries de niveau suppérieur en utilisant nos blocs de base en utilisant une partie de la clef ``blkid``. Afin de calculer les traces de recensement, nous avons besoin de regrouper les blocs en uitlisant les 11 premiers caractÚres de la colonne ``blkid``. 
     34 
    3535  :: 
    3636 
    3737    360610001009000 = 36 061 00100 9000 
    3838 
    39     36     = State of New York  
     39    36     = State of New York 
    4040    061    = New York County (Manhattan) 
    4141    000100 = Census Tract 
    … …  
    4444 
    4545Création de la nouvelle table en utilisant la fonction d'agrégation :command:`ST_Union` : 
    46   
    47 .. code-block:: sql 
    48     
     46 
     47.. code-block:: sql 
     48 
    4949   -- Création de la table 
    5050   CREATE TABLE nyc_census_tract_geoms AS 
    51    SELECT  
    52      ST_Union(the_geom) AS the_geom,  
     51   SELECT 
     52     ST_Union(the_geom) AS the_geom, 
    5353     SubStr(blkid,1,11) AS tractid 
    5454   FROM nyc_census_blocks 
    5555   GROUP BY tractid; 
    56       
     56 
    5757   -- Indexation du champ tractid 
    5858   CREATE INDEX nyc_census_tract_geoms_tractid_idx ON nyc_census_tract_geoms (tractid); 
    59       
     59 
    6060   -- Mise à jour de la table geometry_columns 
    6161   SELECT Populate_Geometry_Columns(); 
    … …  
    6464~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    6565 
    66 L'objectif est ici de regrouper les données spatiales que nous avons créé avec les donées attributaires que nous avions chargé initialement. 
    67    
    68 .. code-block:: sql 
    69    
     66L'objectif est ici de regrouper les données spatiales que nous avons créé avec les données attributaires que nous avions chargé initialement. 
     67 
     68.. code-block:: sql 
     69 
    7070  -- Création de la table 
    7171  CREATE TABLE nyc_census_tracts AS 
    72   SELECT  
     72  SELECT 
    7373    g.the_geom, 
    7474    a.* 
    … …  
    7676  JOIN nyc_census_sociodata a 
    7777  ON g.tractid = a.tractid; 
    78      
     78 
    7979  -- Indexation des géométries 
    8080  CREATE INDEX nyc_census_tract_gidx ON nyc_census_tracts USING GIST (the_geom); 
    81      
     81 
    8282  -- Mise à jour de la table geometry_columns 
    8383  SELECT Populate_Geometry_Columns(); 
    … …  
    8585.. _interestingquestion: 
    8686 
    87 Répondre à une question interressante 
    88 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
    89       
    90 Répondre à une question interressante ! "Lister les 10 meilleurs quartiers ordonnées par la proportion de personne ayant acquis un diplome".  
    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)  
     87Répondre à une question intéressante 
     88~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
     89 
     90Répondre à une question intéressante ! "Lister les 10 meilleurs quartiers ordonnés par la proportion de personnes ayant acquis un diplÃŽme". 
     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) 
    100100  WHERE t.edu_total > 0 
    101101  GROUP BY n.name, n.boroname 
    … …  
    103103  LIMIT 10; 
    104104 
    105 Nous 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. 
    106  
    107 :: 
    108    
    109    graduate_pct |       name        | boroname   
     105Nous sommons les statistiques qui nous intéressent, nous les divisons ensuite à la fin. Afin d'éviter l'erreur de non-division par zéro, nous ne prenons pas en compte les quartiers qui n'ont aucune personne ayant obtenu un diplÃŽme. 
     106 
     107:: 
     108 
     109   graduate_pct |       name        | boroname 
    110110  --------------+-------------------+----------- 
    111111           40.4 | Carnegie Hill     | Manhattan 
    … …  
    119119           29.8 | West Village      | Manhattan 
    120120           29.7 | Central Park      | Manhattan 
    121      
    122    
     121 
     122 
    123123.. _polypolyjoins: 
    124124 
    125125Polygones/Jointures de polygones 
    126 --------------------------------- 
    127  
    128 Dans 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.  
     126-------------------------------- 
     127 
     128Dans notre requête intéressante (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 entre deux quartiers ? Il intersectera chacun d'entre eux et ainsi sera inclut dans **chacun** des résultats. 
    129129 
    130130.. image:: ./screenshots/centroid_neighborhood.png 
    … …  
    132132Pour éviter ce cas de double comptage il existe trois méthodes : 
    133133 
    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)`) 
     134 * La méthode simple consiste a s'assurer que chaque entité ne se retrouve que dans **un** seul groupe géographique (en utilisant :command:`ST_Centroid(geometry)`) 
    135135 * La méthode complexe consiste à disviser les parties qui se croisent en utilisant les bordures (en utilisant :command:`ST_Intersection(geometry,geometry)`) 
    136   
     136 
    137137Voici un exemple d'utilisation de la méthode simple pour éviter le double comptage dans notre requête précédente : 
    138138 
    139139.. code-block:: sql 
    140140 
    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))  
     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)) 
    147147  WHERE t.edu_total > 0 
    148148  GROUP BY n.name, n.boroname 
    149149  ORDER BY graduate_pct DESC 
    150150  LIMIT 10; 
    151    
     151 
    152152Remarquez que la requête prend plus de temps à s'exécuter, puisque la fonction :command:`ST_Centroid` doit être effectuée pour chaque entité. 
    153153 
    154154:: 
    155155 
    156    graduate_pct |       name        | boroname   
     156   graduate_pct |       name        | boroname 
    157157  --------------+-------------------+----------- 
    158158           49.2 | Carnegie Hill     | Manhattan 
    … …  
    166166           30.1 | Downtown          | Brooklyn 
    167167           28.4 | Cobble Hill       | Brooklyn 
    168    
     168 
    169169Éviter le double comptage change le résultat ! 
    170170 
    … …  
    175175---------------------------------------------- 
    176176 
    177 Une 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 ? " 
    178  
    179 Né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 : 
     177Une requête qu'il est "sympa" de demander est : "Comment les temps de permutation des gens proches (dans un rayon de 500 mÚtres ) des stations de métro diffÚrent de ceux qui en vivent loin ? " 
     178 
     179Néanmoins, la question rencontre les mêmes problÚmes de double comptage : plusieurs personnes seront dans un rayon de 500 mÚtres de plusieurs stations de métro différentes. Comparons la population de New York : 
    180180 
    181181.. code-block:: sql 
    … …  
    183183  SELECT Sum(popn_total) 
    184184  FROM nyc_census_blocks; 
    185    
     185 
    186186:: 
    187187 
    188188  8008278 
    189    
    190 Avec la population des gens de New York dans un rayon de 500 metres d'une station de métros : 
     189 
     190Avec la population des gens de New York dans un rayon de 500 mÚtres d'une station de métro : 
    191191 
    192192.. code-block:: sql 
    … …  
    196196  JOIN nyc_subway_stations subway 
    197197  ON ST_DWithin(census.the_geom, subway.the_geom, 500); 
    198    
     198 
    199199:: 
    200200 
    201201  10556898 
    202202 
    203 Il 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. 
     203Il y a plus de personnes proches du métro qu'il y a de personnes ! 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. 
    204204 
    205205.. image:: ./screenshots/subways_buffered.png 
    206206 
    207 La 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 : 
     207La solution est de s'assurer que nous avons seulement des blocs distincts avant de les regrouper. Nous pouvons réaliser cela en cassant notre requête en sous-requêtes qui récupÚrent les blocs distincts, les regroupent pour ensuite retourner notre réponse : 
    208208 
    209209.. code-block:: sql 
    … …  
    216216    ON ST_DWithin(census.the_geom, subway.the_geom, 500) 
    217217  ) AS distinct_blocks; 
    218    
     218 
    219219:: 
    220220 
    221221  4953599 
    222222 
    223 C'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. 
    224  
    225  
    226  
     223C'est mieux ! Donc un peu plus de 50 % de la population de New York vit à proximité (500m, environ 5 à 7 minutes de marche) du métro. 
     224 
Note: See TracChangeset for help on using the changeset viewer.