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.rst @ 17

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

Initial import of the svn tree

RevLine 
[1]1.. _simple_sql:
2
3Section 6: Simple SQL
4=====================
5
6:term:`SQL`, or "Structured Query Language", is a means of asking questions of, and updating data in, relational databases. You have already seen SQL when we created our first database.  Recall:
7
8.. code-block:: sql
9
10   SELECT postgis_full_version();
11
12But that was a question about the database.  Now that we've loaded data into our database, let's use SQL to ask questions of the data! For example,
13
14  "What are the names of all the neighborhoods in New York City?"
15 
16Open up the SQL query window in pgAdmin by clicking the SQL button
17
18.. image:: ./screenshots/pgadmin_05.png
19
20then enter the following query in to the query window
21
22.. code-block:: sql
23
24  SELECT name FROM nyc_neighborhoods;
25 
26and click the **Execute Query** button (the green triangle).
27 
28.. image:: ./screenshots/pgadmin_08.png 
29
30The query will run for a few (mili)seconds and return the 129 results.
31
32.. image:: ./screenshots/pgadmin_09.png 
33
34But what exactly happened here?  To understand, let's begin with the four "verbs" of SQL,
35
36 * ``SELECT``, returns rows in response to a query
37 * ``INSERT``, adds new rows to a table
38 * ``UPDATE``, alters existing rows in a table
39 * ``DELETE``, removes rows from a table
40 
41We will be working almost exclusively with ``SELECT`` in order to ask questions of tables using spatial functions.
42
43SELECT queries
44--------------
45
46A select query is generally of the form:
47
48  SELECT some_columns FROM some_data_source WHERE some_condition;
49 
50.. note::
51
52    For a synopsis of all ``SELECT`` parameters, see the PostgresSQL `documentation  <http://www.postgresql.org/docs/8.1/interactive/sql-select.html>`_.
53   
54The ``some_columns`` are either column names or functions of column values. The ``some_data_source`` is either a single table, or a composite table created by joining two tables on a key or condition. The ``some_condition`` is a filter that restricts the number of rows to be returned.
55
56  "What are the names of all the neighborhoods in Brooklyn?"
57
58We return to our ``nyc_neighborhoods`` table with a filter in hand.  The table contains all the neighborhoods in New York, but we only want the ones in Brooklyn.
59
60.. code-block:: sql
61
62  SELECT name
63    FROM nyc_neighborhoods
64    WHERE boroname = 'Brooklyn';
65
66The query will run for even fewer (mili)seconds and return the 23 results.
67
68Sometimes we will need to apply a function to the results of our query. For example,
69
70  "What is the number of letters in the names of all the neighborhoods in Brooklyn?"
71 
72Fortunately, PostgreSQL has a string length function, :command:`char_length(string)`.
73
74.. code-block:: sql
75
76  SELECT char_length(name)
77    FROM nyc_neighborhoods
78    WHERE boroname = 'Brooklyn';
79
80Often, we are less interested in the individual rows than in a statistic that applies to all of them. So knowing the lengths of the neighborhood names might be less interesting than knowing the average length of the names. Functions that take in multiple rows and return a single result are called "aggregate" functions. 
81
82PostgreSQL has a series of built-in aggregate functions, including the general purpose :command:`avg()` for average values and :command:`stddev()` for standard deviations.
83
84  "What is the average number of letters and standard deviation of number of letters in the names of all the neighborhoods in Brooklyn?"
85 
86.. code-block:: sql
87
88  SELECT avg(char_length(name)), stddev(char_length(name))
89    FROM nyc_neighborhoods
90    WHERE boroname = 'Brooklyn';
91 
92::
93
94           avg         |       stddev       
95  ---------------------+--------------------
96   11.7391304347826087 | 3.9105613559407395
97
98The aggregate functions in our last example were applied to every row in the result set. What if we want the summaries to be carried out over smaller groups within the overall result set? For that we add a ``GROUP BY`` clause. Aggregate functions often need an added ``GROUP BY`` statement to group the result-set by one or more columns. 
99
100  "What is the average number of letters in the names of all the neighborhoods in New York City, reported by borough?"
101
102.. code-block:: sql
103
104  SELECT boroname, avg(char_length(name)), stddev(char_length(name))
105    FROM nyc_neighborhoods
106    GROUP BY boroname;
107 
108We include the ``boroname`` column in the output result so we can determine which statistic applies to which borough. In an aggregate query, you can only output columns that are either (a) members of the grouping clause or (b) aggregate functions.
109 
110::
111
112     boroname    |         avg         |       stddev       
113  ---------------+---------------------+--------------------
114   Brooklyn      | 11.7391304347826087 | 3.9105613559407395
115   Manhattan     | 11.8214285714285714 | 4.3123729948325257
116   The Bronx     | 12.0416666666666667 | 3.6651017740975152
117   Queens        | 11.6666666666666667 | 5.0057438272815975
118   Staten Island | 12.2916666666666667 | 5.2043390480959474
119 
120Function List
121-------------
122
123`avg(expression) <http://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE>`_: PostgreSQL aggregate function that returns the average value of a numeric column.
124
125`char_length(string) <http://www.postgresql.org/docs/current/static/functions-string.html>`_: PostgreSQL string function that returns the number of character in a string.
126
127`stddev(expression) <http://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-AGGREGATE-STATISTICS-TABLE>`_: PostgreSQL aggregate function that returns the standard deviation of input values.
128 
129 
Note: See TracBrowser for help on using the repository browser.