With PostgreSQL 9.3 Database Server now running on my Ubuntu machine, the final things to do for now is to make the PostGIS Extensions available.
PostGIS is a layer, or more precisely: an extension, that adds geospatial capabilities to PostgreSQL. The installation is actually easy – if you have followed the appropriate steps in the previous post, you only need to issue one line:
sudo apt-get install postgresql-9.3-postgis
And that is it…let it run, then – just to be on the safe side – reboot the system.
Creating the Database
So far, we have been dealing with the Database Server, the backbone so to speak. With all bits and pieces in place now, it is time to create the actual database.
A first set of tables is dealing with Airfields – eventually, it will hold a list of historic airfields. So creating the database is easy – the standard SQL Command is
CREATE DATABASE [DatabaseName];
which, of course, can be extended by several parameters. This creates a database in any SQL-based RDBMS – but it does not make it “spatial”. With PostGIS installed, you can now connect to the database and issue the following SQL command:
CREATE EXTENSION postgis;
That makes the database “spatial”. Want to see? Take a look at the table definition below and note the “Coordinates” column definition – it says “geometry(Point, 4326)“.
“Geometry” is a spatial data type – I am not going to dig into the details now – let’s just stick to the fact that this spatial data type is what we have installed PostGIS for.
Converting the Coordinates
Remember my “Airfields” table – I have actually filled it (with a SQL Script, not manually) and it now contains round about 4000 records of European Airfields. The data contains information such as the Name of the airfield, the country, the type and – of course – longitude and latitude.
But one thing easily forgotten is that longitude and latitude are just numbers – in the “spatial” world, they mean nothing. So one things that needs to be done is to create a “spatial” value that applications such as QGIS can work with. This value will be stored in the Coordinates column. The SQL Statement to perform the transformation is – at best – something to get used to, certainly something to nor forget (or your data will not be displayed).
UPDATE "Airfields" SET "Coordinates" = ST_SetSRID(ST_MakePoint("Longitude","Latitude"),4326);
What this does it to update the table “Airfields” and calculate the value for “Coordinates” using the values from “Longitude” and “Latitude“. Running the SQL Statement against the table produces the “spatial” values.
Mapping the Data – QGIS
Having data in a spatial database is one thing – the ability to do something with it is a completely other thing. One of the best – free! – GIS Programs I have come across is Quantum GIS – or short: QGIS.
In QGIS, it is quite easy to make use of the spatial data – two things I do:
- Register a plugin that provides me with access to Google Maps data (and other sources)
- Create a PostGIS Layer, pointing to my database table (or rather a view I have created from the table).
The result? Quite impressive, I would say – spatial data has never been so “accessible”… and that at no cost at all.
Now it is time to improve on this…