I propose you a series of articles to address some ideas received on the use of the SQL language and its possibilities in the spatial analysis. To summarize the most widespread idea, the SQL language is a very good tool for querying GIS tables, but you need GIS software such as QGis or ArcGis to perform spatial analysis tasks. As for the first part of this assertion, we will analyse it in the second article of this series. Let’s start with an example to debunk the second part of this sentence. We will see an example from a PostgreSQL / Postgis database containing two tables: a plots table and a flood zones table.
Simple SQL queries
Now let’s look at some SQL queries, starting with an extremely simple example and, gradually, making it more complex. Suppose that the ultimate goal is to know the price of the plots, by type of property class, which are located in the flood zone.
Here, firstly, a simple request on the plots table
With the query SELECT * FROM plots we obtain as result all the records with all the fields of the table.
A little more complex, select only two fields of the table: the type of property and the price of the land:
Therefore, for each plot we have the value of the land and the type of property class.
Let’s add a little more: we can get the land total price with the query SELECT sum (land_price) FROM plots;
For a more untestandable display you can convert the result to a monetary number with the following modification of the query: SELECT sum (prix_terrain) :: numeric :: money FROM parcels;
Now we have the total price of the plots in euros.
By modifying the query and adding a GROUP BY clause we can have the total price of each property class:
The result is the total price per property class, for all records in the parcel table.
But what if we wanted this result only for plots located in the flood zones?
SQL request “spatial”
The request:
SELECT sum (prize_terrain) :: numeric :: money, propertyclass
FROM plots
GROUP BY propertyclass;
can be modified by adding the flood zones table to the FROM clause
SELECT sum (prize_terrain) :: numeric :: money, propertyclass
FROM plots, uninhabited areas
GROUP BY propertyclass;
and a plot selection clause to retain only those whose geometry intersects the flood zones:
SELECT sum (prize_terrain) :: numeric :: money, propertyclass
FROM parcels, uninhabited areas
WHERE st_intersects (parcels.geometry, zones_inondables.geometry)
GROUP BY propertyclass;
We have the result we were looking for. Now I leave you the task to see all the operations to do with your favorite GIS software to get the same result.
You will agree that it’s much faster by writing this query.
An indispensable notion
Before going further, we must stop on an essential notion to perform the spatial analysis with SQL. What’s more complicated with this notion is that we all think we have it but, if we think it over, we realize how far we are from applying it in our everyday work.
If we list the possible data types in a database, we immediately think of numbers, strings, date type, and so on.
If I tell you now “geometry”, you will say, yes, of course, there is also this type of data. But somehow we place it a little apart. Yet it’s good when we understand that it is exactly one type of data as any other related to spatial analysis.
You will be able to perform a full spatial analysis with SQL when you discover that:
- if you can add two numbers you can just as well intersect two geometries;
- If you can cut (trim) a string, you can cut a geometry with another geometry;
- If you can find a date between two other dates, you can find a geometry covered by another.
When you want to perform an operation between numbers in SQL, the different operators come to you right away. Similarly, when you want to process a text string, a set of operations will appear to you. The purpose of this series of articles is to provide you with the equivalent for dealing with the geometries: a series of similar operations to text functions or arithmetic operators.
If you can clearly see all the operations that can be performed on the geometry type, you are ready to do spatial analysis with SQL and save valuable time.
