PostGIS is a powerful open source tool that allows us to develop robust spatial databases. In this chapter, we will learn some useful basic concepts through practical examples. We will also set up our working environment so we can get started with it quickly. However, before we can continue, let me tell you about a very important concept that we need to fully understand first: what is a GIS application?
Geographic Information Systems (GIS) are systems that were designed to manage and analyze spatial data, showing it to the user in a graphical way; the main aspects of a GIS system are as follows:
It represents spatial data as a set of layers: A GIS application groups data of the same kind (streets, landmarks, and so on) on layers. These layers are shown to the user one above the other, thereby creating a unified view of the data. The following image shows an example of how these layers are displayed:
We now have an integrated view of the data, so the user can extract information generated by the combination of them; as an example, using this integrated view, we can measure the distance between the landmark 1 and the vegetation area 2.
It shows the spatial data above a map: This helps users to get a better understanding of the displayed data, putting it in the context of the area of interest.
It has a comprehensive set of analytic and data transformation tools: As mentioned in the last example, a GIS application must give users a way to analyze, process, and transform such spatial data, so they can get new information from the input data.
It is quite likely that you have experience and some basic knowledge of relational databases and computer programming. It will also be desirable if you have basic knowledge about geography, but if you don't, then there's no need to worry; one of this book's objectives is to explain to you some of the most important and useful concepts of geography in a practical and simplified way, so that you can understand them through the development of the book, and focus on the practical application of them. This book will also show you how powerful PostGIS can be and how much it can help you in creating strong and sometimes complex databases. By the end of the book, you should be able to develop amazing spatial projects.
In this chapter, we will cover the following topics:
Learning about some basic GIS concepts
Setting up the PostgreSQL database manager
Setting up the PostGIS spatial extension
Setting up the QGIS spatial data viewer
PostGIS is the geographic extension of the database management system, PostgreSQL, which allows us to store geographic objects as part of our data tables. A geographic object is a special type of data that allows us to store a geographic position or a set of them as part of a line or a polygon. Essentially, PostGIS is a powerful tool that enables you to handle complex geographical data and visually explore this data when you use it along with graphical tools, such as QGIS.
PostGIS is an extension of the powerful PostgreSQL, one of the most reliable open source DBMS available, which has spent a lot of time on the market, but what makes it such a good platform for PostGIS is the fact that it implements something called Generic Index Structure (GIST), which allows it to build indexes in almost any kind of data type. Given this flexibility and the fact that the structure of PostgreSQL gives you the chance to build custom functions very tightly to the core, PostGIS could by developed in a natural way. This is because it basically adds spatial data, spatial indexes, and spatial functions to the existing features of PostgreSQL without any complicated, intermediate, or special conversion processes. The other important features of it are:
It has a lot, and I mean a lot, of useful spatial functions to search, analyze, convert, and manage spatial data
It has both vectorial and raster data support; these data types will be described in Chapter 6, Management of Vectorial and Raster Data with PostGIS
It's based on open standards as defined by the Open Geospatial Consortium. You can visit http://www.osgeo.org/ to know more
For the last decade, PostGIS has been used, proved, and improved by a lot of public and private organizations all around the world
It's supported by other well-proven open source projects (such as Proj4, GEOS, and GDAL)
Almost all (if not all), both open and closed GIS software, have compatibility with it (such as ETL and desktop and server Geotracks)
With a GIS database, we can handle geographic data more efficiently because it contains functions and algorithms that make it easier to manipulate and analyze it.
We might come across a hypothetical situation where we are the IT department of a real estate company, and are required to develop a computational system to control the data of the houses it's selling. Currently, the company only has an electronic sheet where all this data is stored. The sheet looks similar to the following table:
|
Town |
Postal code |
Street |
Number |
|---|---|---|---|
|
London |
N7 6PA |
Holloway Road |
32 |
|
West Berkshire |
RG12 1DF |
Charles Square |
45 |
|
Bristol |
BS1 4UZ |
St Augustine's Parade |
39 |
Additionally, the company wants to be able to show the geographic position of every property it has assigned on a map via its website. Suppose we don't know anything about spatial databases, we have a tight schedule, and we just want to use a traditional relational approach. The first thing we could do is to build a data table, taking the electronic sheet as a base, and adding two more fields. These fields will be doubles and will store the latitude and longitude values of the geographical position of every property. That table must be created through a command as shown in the following code snippet:
CREATE TABLE tbl_properties ( town character(30), postal_code character(10), street character(30), "number" integer, latitude double precision, longitude double precision );
In Chapter 2, Creating Your First Spatial Database, we will see how to run these kinds of commands. The created table must look similar to the following table:
|
Town |
Postal code |
Street |
Number |
Latitude |
Longitude |
|---|---|---|---|---|---|
|
London |
N7 6PA |
Holloway Road |
32 |
51.556173 |
-0.116190 |
|
West Berkshire |
RG12 1DF |
Charles Square |
45 |
51.381320 |
-1.344165 |
|
Bristol |
BS1 4UZ |
St Augustine's Parade |
39 |
51.453462 |
-2.598348 |
Has the problem been solved or not? What if our boss wants to know which houses are located at least 10 miles away from the local supermarket of some specific town? Well, we could develop a software in our favorite programming language that reads the position of every house in the table and calculates the distance between the supermarket and the house. The main problem with this approach is that we have to write our own function to calculate the distance, but these functions can have errors that show us the wrong results. Another problem here is the fact that when we need to run a radius-based search (looking for all the objects located at x distance from a specific position), we will need to go through the entire table, making comparisons with every register; there is no way to use an optimization mechanism as an index to hasten the query.
If, instead of doing this, we use a geographical field to store these positions, we will only have to make a spatial query to achieve the result we're looking for. Here is an example of how we could create our table with the spatial field instead of the doubles:
CREATE TABLE tbl_properties ( town character(30), postal_code character(10), street character(30), "number" integer, the_geom geometry );
Now, in order to insert the spatial data into the table we must use the following query:
INSERT INTO tbl_properties (town, postal_code, street, "number", the_geom) VALUES ('London', 'N7 6PA', 'Holloway Road', 32, ST_GeomFromEWKT('SRID=4326;POINT(-0.116190, 51.556173)'));The result will be similar to the following:
|
Town |
Postal code |
Street |
Number |
the_geom |
|---|---|---|---|---|
|
London |
N7 6PA |
Holloway Road |
32 |
POINT( -0.116190, 51.556173) |
|
West Berkshire |
RG12 1DF |
Charles Square |
45 |
POINT( -1.344165, 51.381320) |
|
Bristol |
BS1 4UZ |
St Augustine's Parade |
39 |
POINT(-2.598348, 51.453462) |
The following query asks the database to bring all the registers that are located at 10 statute miles (the distance, in this case, must be given in degrees, so, we have divided 10 miles by 69.047, the equivalent of one degree in statute miles) from the geographical position where the latitude is 51.56 and the longitude is -0.117. Supposing that this is the geographic position of our fictitious supermarket, this position is stored and is a point geometry object on the database:
SELECT * FROM tbl_properties WHERE ST_DWithin(the_geom, ST_GeomFromText('POINT(-0.117, 51.56)',4326), (10.0 / 69.047) );In the following chapters, we will explain how to make spatial queries. This is an example of exactly how useful spatial databases can be; taking some time to understand how to use them will certainly be useful.
Before we can start working on spatial databases, we have to prepare our working environment. In the following sections of this chapter, we will see how to do this step by step.
PostgreSQL is a powerful Object-Relational Database Management System (ORDBMS). We need this because PostGIS is just an extension of it. It's open source and free of cost.
The first thing we have to do is download and install the PostgreSQL database management system. In this book, we will assume that you are working on a 64-bit Windows machine with a version of Windows 7 or later. We will use the most recent stable version available at the time of the writing, which is the Version 9.3. Perform the following steps:
First, we have to navigate to the official site, http://www.postgresql.org.
Then, we have to go to the Download section at http://www.postgresql.org/download/, as shown in the following screenshot:
After this, we need to go to the Binary packages section of the page and select the Windows hyperlink:
Now, we have to choose the download hyperlink in the paragraph Download the installer from EnterpriseDB for all supported versions. as shown in the preceding screenshot.
Similar to the preceding screenshot, we will select the most recent stable version and the Win 86-64 installer.
When the download finishes, we will get the
postgresql-9.3.x-x-windows-x64.exefile; this is the PostgreSQL installer for our operating system. Now, we will double-click on this file.Immediately, we can see a dialog box where the operating system asks us for permission to run this file. We must allow this file to be executed as a system manager, as shown in the following screenshot:
We will choose an installation folder, or just leave the default one (
C:\Program Files\PostgreSQL\9.3), and click on the Next button.Next, we need to select a folder where all the data of our databases will be installed. You can just select the default (
C:\Program Files\PostgreSQL\9.3\data), but it would be very desirable to select a folder from another partition different from the one that uses the operating system; this can help you to avoid losing your data if you have to format your system for any reason.We will see a screen with two text areas where we will need to type and retype a password for the Postgres user. This password can be anything that you want, but it cannot be an empty string. Be careful with the password you choose because if you forget it, there is no way to get it back; we will have to reinstall everything again!
Now, we have to choose a listening port.
Then, we will see a dialog screen that asks you to select a Regional Configuration. We can leave the default option, which means the same language configuration that you have on your operating system, and then click on the Next button.
Once the installer has finished copying the files, it will show a screen that will tell you that the installation of PostgreSQL has finished. Here is something important to keep in mind: the screen has a checkbox inside that asks you whether you want to execute the Stack Builder at the end of the installation. At this moment, we will choose not to do so. This checkbox must be unchecked. In the next section of this chapter, we will properly explain what the Stack Builder is, and what it is for. Now, we can click the Finish button.
Finally, PosgreSQL is installed on your machine. You can check whether the installation was successful by executing a graphical tool for the DBMS called PGAdmin III. Then you will see the main window. On the left-hand side is the list of servers; in this case we just have one server:
Double-click on the PostgreSQL 9.3 (localhost 5432) server and a dialog box called Connect to Server will be shown. In this box, you must type your Postgres user password and click on the OK button. If you wish, you can check the Store password checkbox; it stores your password in this machine so you don't have to type it every time you log in to your database:
In the previous section of this chapter, we mentioned the Stack Builder; it's an application that allows us to install several additional options or extensions for PostgreSQL. Using this tool, we will install PostGIS in our database server. The following procedure applies even if you have previously installed a version of PostgreSQL higher than 8.X:
First, you have to execute the application Stack Builder tool installed with PostgreSQL.
We will see a dialog box that asks us for permission to execute this app, you have to click on the Yes button.
Now, you can see a window with a combo box in the center, where you have to choose the server that you want to configure. In this case, we only have one installed in our computer; you must select the PostgreSQL 9.3 (x64) on port 5432 option and click on the Next button:
Next, we will see the same window as before, but with a list of available applications for installation grouped by categories; we will select the Spatial Extensions category:
Now, you must click on PostGIS 2.1 Bundle for PostgreSQL 9.3 (64 bit) v2.1.x and click on the Next button.
Then, you will see a window that shows the packages you have selected. In another area positioned after that, we can select a folder where all the required files will be downloaded. This is not the installation folder; if you don't have any trouble with the default directory, you can click on the Next button.
When it finishes, you will see a window telling you that the application has downloaded the required file and it can start to install it in your computer. Leave the Skip Installation checkbox unchecked and click on the Next button:
Now, you can see another window that shows the license agreement. It's not necessary that you read the entire document; you can to click the I Agree button.
Once you have done this, you will see a window that shows the components that need to be selected and installed. In the middle, it has a checkbox, where PostGIS is checked, and another with the text, Create spatial database, which is unchecked. Check this one and click on the Next button:
Now, you have to choose a destination folder for the installation; you can leave the default one and click on the Next button.
Then, a window appears where you have to log in to the database. Just type your Postgres user password, defined in the previous section, and click on the Next button.
In the next window, you will have to add a name for your spatial database. In this case, I named it
spatial_db1, but you can name it whatever you want; type the name and click on the Install button, as shown in the following screenshot:
Next, it will show you a dialog box that asks you for permission to set the
GDAL_DATAenvironment variable. At this moment, I will only tell you that GDAL is a very useful and important library that PostGIS uses internally. You have to click on the Yes button:
Now another dialog box appears, asking you to decide whether you want to set the
POSTGIS_GDAL_ENABLED_DRIVERS; you will have to click on the Yes button. In later chapters, I will tell you more about these system variables:
Another dialog box appears, asking you for permission to set
POSTGIS_ENABLE_OUTDB_RASTERS; click on the Yes button, as shown in the following screenshot. We will see what a raster data is in Chapter 6, Management of Vectorial and Raster Data with PostGIS:
Lastly, the installer shows a window that tells you that the installation is completed. Now you just have to click on the Close button.
Now, maximize the Application Stack Builder window and click on the Finish button.
Congratulations!, Now you have PostgreSQL with the PostGIS extension installed on your machine. You can check whether everything is okay by running PGAdmin III, and then running the following query in the Postgres database:
SELECT name, default_version,installed_version FROM pg_available_extensions WHERE name LIKE 'postgis%' ;
You must get a result dataset as shown in the following table:
|
name |
default_version |
installed_version |
|---|---|---|
|
postgis |
2.1.5 | Â |
|
Postgis tiger geocoder |
2.1.5 | Â |
|
Postgis topology |
2.1.5 | Â |
This means that you have the PostGIS extension available on your database server and you can include it in any new databases that you create.
Finally, we will install the QGIS application. It's an open source project that allows us to graphically represent the geographical data stored in several formats, including the data that is stored in our database.
QGIS is a free and open source geographic information system. It's very user friendly and it can read a lot of spatial formats, and it's an excellent tool for seeing the data we generated graphically. You can visit the project's official site at http://www.qgis.org/en/site/. Perform the following steps for the installation:
For installation, go to the project website in the For Users section at http://www.qgis.org/en/site/forusers/index.html.
Then, you have to click on the Download QGIS button.
You will need to select the Windows version section. Once inside the section, you will find a QGIS standalone Installer Version 2.4 (64-bit) link; click on it.
The installer will start to download; when it finishes, you need to double-click on the file for the installation to begin.
Then, you will see a welcome window; click on the Next button:
After this, you will see a window with the agreement license; click on the I accept button.
Then, select a destination folder for the installation. You can just leave the default and click on the Next button. The installation requires 1.2 GB of disk free space; make sure that you have it in your disk partition.
Now, you can select which components will be installed. By default, the QGIS option is selected; leave it the way it is, and click on the Install button:
When it finishes, the installation will be complete and you will see a window that confirms it; now just click on the Finish button.
Once QGIS is installed, we can execute the QGIS Desktop 2.4.0 application. If the application was correctly installed, you will see the following window:
At this moment, we have learned how to install and set up our working environment in order to use the PostGIS extension, and through this we have also learned some useful geographic concepts that will allow you to get a better understanding of how PostGIS works. So, we have installed the QGIS application that will allow us to graphically explore our databases and check that everything is okay with the data.
Now, we are ready to start developing our first spatial database, for this we will continue with a practical example at the beginning of the next chapter.
