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 @ 14

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

Initial import of the svn tree

RevLine 
[1]1.. _simple_sql_exercises:
2
3Section 7: Simple SQL Exercises
4===============================
5
6Using the ``nyc_census_blocks`` table, answer the following questions (don't peak at the answers!).
7
8Here is some helpful information to get started.  Recall from the :ref:`About Our Data <about_data>` section our ``nyc_census_blocks`` table definition.
9
10.. list-table::
11   :widths: 20 80
12
13   * - **blkid**
14     - A 15-digit code that uniquely identifies every census **block**. Eg: 360050001009000
15   * - **popn_total**
16     - Total number of people in the census block
17   * - **popn_white**
18     - Number of people self-identifying as "white" in the block
19   * - **popn_black**
20     - Number of people self-identifying as "black" in the block
21   * - **popn_nativ**
22     - Number of people self-identifying as "native american" in the block
23   * - **popn_asian**
24     - Number of people self-identifying as "asias" in the block
25   * - **popn_other**
26     - Number of people self-identifying with other categories in the block
27   * - **hous_total**
28     - Number of housing units in the block
29   * - **hous_own**
30     - Number of owner-occupied housing units in the block
31   * - **hous_rent**
32     - Number of renter-occupied housing units in the block
33   * - **boroname**
34     - Name of the New York borough. Manhattan, The Bronx, Brooklyn, Staten Island, Queens
35   * - **the_geom**
36     - Polygon boundary of the block
37
38And, here are some common SQL aggregation functions you might find useful:
39
40 * avg() - the average (mean) of the values in a set of records
41 * sum() - the sum of the values in a set of records
42 * count() - the number of records in a set of records
43
44Now the questions:
45
46 * **"What is the population of the City of New York?"**
47 
48   .. code-block:: sql
49   
50     SELECT Sum(popn_total) AS population
51       FROM nyc_census_blocks;
52     
53   :: 
54   
55     8008278
56   
57   .. note:: 
58   
59       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``.
60       
61 * **"What is the population of the Bronx?"**
62
63   .. code-block:: sql
64 
65     SELECT Sum(popn_total) AS population
66       FROM nyc_census_blocks
67       WHERE boroname = 'The Bronx';
68     
69   :: 
70   
71     1332650
72   
73 * **"What is the average number of people living in each housing unit in New York City?"**
74 
75   .. code-block:: sql
76
77     SELECT Sum(popn_total)/Sum(hous_total) AS popn_per_house
78       FROM nyc_census_blocks;
79
80   :: 
81   
82     2.6503540522400804
83   
84 * **"For each borough, what percentage of the population is white?"**
85
86   .. code-block:: sql
87
88     SELECT
89         boroname,
90         100 * Sum(popn_white)/Sum(popn_total) AS white_pct
91       FROM nyc_census_blocks
92       GROUP BY boroname;
93
94   :: 
95   
96        boroname    |      white_pct     
97     ---------------+---------------------
98      Brooklyn      | 41.2005552206888663
99      The Bronx     | 29.8655310846808990
100      Manhattan     | 54.3594013771837665
101      Queens        | 44.0806610271290794
102      Staten Island | 77.5968611401579346
103 
104Function List
105-------------
106
107`avg(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the average value of a numeric column.
108
109`count(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the number of records in a set of records.
110
111`sum(expression) <http://www.postgresql.org/docs/8.2/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the sum of records in a set of records.
Note: See TracBrowser for help on using the repository browser.