PostGIS

No book on Postgres would be complete without at least mentioning PostGIS, one of the biggest reasons for the success of Postgres. This one simple addition has put Postgres at the forefront of most of the world’s open source (and many closed source) mapping and geographical information systems.

In fact, next to Postgres and PostGIS, the next most used database technology is Oracle Spatial, which costs much more and is generally out of reach of most small to medium-sized enterprises.

I can't possibly cover everything that PostGIS is capable of in this chapter, so it's going to be fairly brief. For a more in-depth look at GIS (and PostGIS), see my previous book in the Succinctly series, GIS Succinctly, which covers GIS as its main topic.

So what does PostGIS bring to the table?

First of all, PostGIS adds many new data types. Some are an extension of the geometric types we've already seen, and others are completely new.

It also adds many (hundreds) of processing functions covering everything from handling vector-based graphics and input shapes, to coordinate conversion based on different grid systems in use around the world.

The most recent versions of PostGIS have been extended to provide many new raster analysis functions and are now so powerful that many of them can take raw bitmap imagery of a scanned paper map or aerial photo and actually extract vector details into a database table from it.

Let's create a new database that's set up to use PostGIS, and then take a quick look at what is added. Open pgAdmin, and follow the instructions in previous chapters for creating a database.

When it comes to selecting the template, make sure you select the template that the PostGIS install added for you.

New Database Properties

New Database Definition

This template should be called template_postgis_20 if you followed the previous installation instructions.

Click OK and let Postgres create your new database.

Once your database is created, expand the object tree and take a look at what's been added.

Object tree

One table containing spatial reference information, two trigger functions used to enforce dimensions and geometry types, four views to help manage your GIS database's metadata, and 892 new functions and routines have been added.

If you scroll down through the list of functions you'll see that many of them start with st_. These functions are Open Geo Consortium (OGC) compatible functions that follow the simple features standard.

With these functions present, any software that is written to use OSG standards can use Postgres as a GIS store with little or no effort. It also means that a lot of software that works with systems such as Oracle Spatial will have no problems working with Postgres.

You should also see that you have many translation functions such as ST_Transform(), which can convert worldwide geometry from one coordinate space to another.

There are many different output functions such as ST_AsGeoJson(), ST_AsKml(), and ST_AsGml(), which allow you to produce GeoJson, Google Earth KML, and industry-standard GML files directly from mapping data stored in your database.

Finally on top of all of this, if you look in the installation folder where you stored Postgres on your hard drive, you should also find a number of extra tools such as shp2pgsql:

Installed Postgres tools

This tool converts industry-standard shapefiles into SQL scripts so they can be inserted into Postgres.

If you look in the postgisgui folder, you should also find a graphical version of these tools, which allows you to simply choose GIS data files from your local hard drive and automate the insertion process.

Postgisgui folder

PostGIS Shapefile Import/Export Manager

Getting started using GIS has never been easier. No more reading dozens of books on the subject and attending expensive courses required.

With the liberal Postgres open source license, the PostGIS add-on, plus many of the free geospatial applications that exist these days such as Quantum GIS, there's no reason why a good GIS system is a difficult thing to find.

I encourage you to go further on this topic; it’s something that I find exceptionally interesting, and I'm sure you will too.