Bienvenue sur

Bienvenue sur , 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_exercises.rst @ 1

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

Initial import of the svn tree

Section 13: Spatial Joins Exercises

Here's a reminder of some of the functions we have seen. Hint: they should be useful for the exercises!

Also remember the tables we have available:

  • nyc_census_blocks
    • name, popn_total, boroname, the_geom
  • nyc_streets
    • name, type, the_geom
  • nyc_subway_stations
    • name, routes, the_geom
  • nyc_neighborhoods
    • name, boroname, the_geom


  • "What subway station is in 'Little Italy'? What subway route is it on?"

    SELECT, s.routes
    FROM nyc_subway_stations AS s
    JOIN nyc_neighborhoods AS n
    ON ST_Contains(n.the_geom, s.the_geom)
    WHERE = 'Little Italy';
       name    | routes
     Spring St | 6
  • "What are all the neighborhoods served by the 6-train?" (Hint: The routes column in the nyc_subway_stations table has values like 'B,D,6,V' and 'C,6')

    SELECT DISTINCT, n.boroname
    FROM nyc_subway_stations AS s
    JOIN nyc_neighborhoods AS n
    ON ST_Contains(n.the_geom, s.the_geom)
    WHERE strpos(s.routes,'6') > 0;
            name        | boroname
     Midtown            | Manhattan
     Hunts Point        | The Bronx
     Gramercy           | Manhattan
     Little Italy       | Manhattan
     Financial District | Manhattan
     South Bronx        | The Bronx
     Yorkville          | Manhattan
     Murray Hill        | Manhattan
     Mott Haven         | The Bronx
     Upper East Side    | Manhattan
     Chinatown          | Manhattan
     East Harlem        | Manhattan
     Greenwich Village  | Manhattan
     Parkchester        | The Bronx
     Soundview          | The Bronx


    We used the DISTINCT keyword to remove duplicate values from our result set where there were more than one subway station in a neighborhood.

  • "After 9/11, the 'Battery Park' neighborhood was off limits for several days. How many people had to be evacuated?"

    SELECT Sum(popn_total)
    FROM nyc_neighborhoods AS n
    JOIN nyc_census_blocks AS c
    ON ST_Intersects(n.the_geom, c.the_geom)
    WHERE = 'Battery Park';
  • "What are the population density (people / km^2) of the 'Upper West Side' and 'Upper East Side'?" (Hint: There are 1000000 m^2 in one km^2.)

      Sum(c.popn_total) / (ST_Area(n.the_geom) / 1000000.0) AS popn_per_sqkm
    FROM nyc_census_blocks AS c
    JOIN nyc_neighborhoods AS n
    ON ST_Intersects(c.the_geom, n.the_geom)
    WHERE = 'Upper West Side'
    OR = 'Upper East Side'
    GROUP BY, n.the_geom;
          name       |  popn_per_sqkm
     Upper East Side | 47943.3590089405
     Upper West Side | 39729.5779474286
Note: See TracBrowser for help on using the repository browser.