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

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

Initial import of the svn tree

Section 7: Simple SQL Exercises

Using the nyc_census_blocks table, answer the following questions (don't peak at the answers!).

Here is some helpful information to get started. Recall from the :ref:`About Our Data <about_data>` section our nyc_census_blocks table definition.

System Message: ERROR/3 (<string>, line 8); backlink

Unknown interpreted text role "ref".
blkid A 15-digit code that uniquely identifies every census block. Eg: 360050001009000
popn_total Total number of people in the census block
popn_white Number of people self-identifying as "white" in the block
popn_black Number of people self-identifying as "black" in the block
popn_nativ Number of people self-identifying as "native american" in the block
popn_asian Number of people self-identifying as "asias" in the block
popn_other Number of people self-identifying with other categories in the block
hous_total Number of housing units in the block
hous_own Number of owner-occupied housing units in the block
hous_rent Number of renter-occupied housing units in the block
boroname Name of the New York borough. Manhattan, The Bronx, Brooklyn, Staten Island, Queens
the_geom Polygon boundary of the block

And, here are some common SQL aggregation functions you might find useful:

  • avg() - the average (mean) of the values in a set of records
  • sum() - the sum of the values in a set of records
  • count() - the number of records in a set of records

Now the questions:

  • "What is the population of the City of New York?"

    SELECT Sum(popn_total) AS population
      FROM nyc_census_blocks;
    
    8008278
    

    Note

    What is this AS? You can give a table or a column another name by using an alias. Aliases can make queries easier to both write and to read. So instead of our outputted column name as sum we write it AS the more readable population.

  • "What is the population of the Bronx?"

    SELECT Sum(popn_total) AS population
      FROM nyc_census_blocks
      WHERE boroname = 'The Bronx';
    
    1332650
    
  • "What is the average number of people living in each housing unit in New York City?"

    SELECT Sum(popn_total)/Sum(hous_total) AS popn_per_house
      FROM nyc_census_blocks;
    
    2.6503540522400804
    
  • "For each borough, what percentage of the population is white?"

    SELECT
        boroname,
        100 * Sum(popn_white)/Sum(popn_total) AS white_pct
      FROM nyc_census_blocks
      GROUP BY boroname;
    
       boroname    |      white_pct
    ---------------+---------------------
     Brooklyn      | 41.2005552206888663
     The Bronx     | 29.8655310846808990
     Manhattan     | 54.3594013771837665
     Queens        | 44.0806610271290794
     Staten Island | 77.5968611401579346
    

Function List

avg(expression): PostgreSQL aggregate function that returns the average value of a numeric column.

count(expression): PostgreSQL aggregate function that returns the number of records in a set of records.

sum(expression): PostgreSQL aggregate function that returns the sum of records in a set of records.

Note: See TracBrowser for help on using the repository browser.