PostGIS Spatial Database Engine UMN Mapserver Boston Geographic Information Systems    Checkout our PostGIS in Action book.  First chapter is a free download   PostGreSQL Object Relational Database Management System
GIS Books  Home   Consulting Services  About Boston GIS   Boston GIS Blog  Postgres OnLine Journal
PostGIS in Action is out in hard-copy,
download the first chapter
and SQL Primer for free. Tips and Tricks for PostGIS
Get $15 off on with our Halloween Sale and Coupon TREAT - Shop Now
GIS Article comments Comments Rss
Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2)

What Is PostGIS?

PostGIS is an open source, freely available, and fairly OGC compliant spatial database extender for the PostgreSQL Database Management System. In a nutshell it adds spatial functions such as distance, area, union, intersection, and specialty geometry data types to the database. PostGIS is very similar in functionality to SQL Server Spatial support, ESRI ArcSDE, Oracle Spatial, and DB2 spatial extender except it has more functionality and generally better performance than all of those (and it won't bankrupt you). The latest release version now comes packaged with the PostgreSQL DBMS installs as an optional add-on. As of this writing PostGIS 2.2.0 is the latest stable release. Noteable features in this release:

  • now packages along new 3D measurement functions, 3D spatial index support and 3D surface area types
  • seamless raster/vector analysis support including an easy to use command line raster database loader that supports various types and can load whole folders of raster files with one commandline statement, and really jazzy image export functions to output both raster and geometries as PNG/TIFF and other raster formats.
  • SQL/MM topology support: now improved using native C functions.
  • the graphical gui loader,which is packaged with the Windows Application Stack builder and some other desktop distros, now includes batch file uploading as well as exporting. This feature can be enabled as a plugin in pgAdmin III
  • Ability to install using CREATE EXTENSION postgis, ALTER EXTENSION postgis ..
  • KNN distance functionality now works with geography and 3D geometries. You get KNN true distances (not just bounding box distances), if you are using PostgreSQL 9.5+
  • It also includes as an extra: Tiger geocoder upgraded to work with Tiger 2015 data.
  • The very first version of PostGIS windows to include SFCGAL Enhanced 3D geometry and other geometry processing functions.
  • SFCGAL now supports extension model so can be installed with: CREATE EXTENSION postgis_sfcgal; after you have installed postgis.
  • The very first version of PostGIS to include address_standardizer extension for parsing addresses into parts, useful for geocoding addresses.

The PostGIS windows 2.2.0 bundle also includes cool PostGIS related extensions that augment your spatial enjoyment. In the bundle you will also find:

  • pgRouting 2.1.0 and osm2pgrouting 2.1 alpha loader - for building routing applications.
  • ogrfdw for querying remote spatial data sources. This includes curl support so you can query web services such as WFS services as well.
  • pgpointcloud for storing LIDAR data in compress POINTPATCH and also performing various operations on it.

We will assume a windows environment for this tutorial, but most of the tutorial will apply to other supported platforms such as Linux, Unix, BSD, Mac etc. We will also be using Massachusetts/Boston data for these examples. For desktop users, the EnterpriseDB one-click installer exists as well for Mac/OSX and Linux desktops, so you should be able to follow along without too much fuss.

Installing PostgreSQL with PostGIS Functionality

We will not go into too much detail here since the install wizard (at least the windows one) is pretty good. Below are the basic steps.

  1. Download the install for your specific platform from the PostgreSQL Binary Download ( ) . As of this writing the latest version is PostgreSQL 9.5.0beta1 and we will be assuming PostGIS 2.2. The minimum support PostgreSQL for PostGIS 2.2.0 is PostgreSQL 9.1 (for windows we only build installers for 9.3-9.5 for the 2.2.0 series) and to get the full fancy smancy features like KNN true distance indexable operators (prior versions are bounding box distance only) you need PostgreSQL 9.5. So use 9.5 when it comes out and test now on the beta. The below options follow the basic sequence of the postgresql windows installer.
  2. Launch exe to install PostgreSQL
  3. Once PostgreSQL is installed, launch Application Stack Builder from (Start->Programs->PostgreSQL 9.5->Applciation Stackbuilder and pick the version of PostgreSQL you want to install PostGIS on and the version of PostGIS to install. NOTE: PostGIS 2.1, 2.2, 1.5 can coexist on the same server. You can upgrade from 2.0, 2.1 to 2.2 without having to backup and restore your db. and ALTER EXTENSION postgis UPDATE; will do the trick after you've installed the latest PostGIS. (In prior versions of the installer, we had a template database. We took that out since creating a database from template doesn't maintain search paths set by postgis_tiger_geocoder and postgis_topology extensions. There is still a postgis sampler option) which creates a regular spatial db. The dumper,loader commandline and GUI tools in PostgreSQL bin folder will get overwritten by the last PostGIS you installed so be careful. Generally speaking PostGIS 2.2.0 should work just fine everywhere you were using PostGIS 2.0-2.1 before, but you may need install the legacy_minimal.sql or legacy.sql if you have old applications or tools that use old functions (removed in PostGIS 2.0 and 2.2)
  4. Navigate to spatial extensions and pick PostGIS 2.2. Download , install. Please note the PostGIS windows installer, no longer creates a template database. Using CREATE EXTENSION postgis; for enabling PostGIS in a database is the recommended way.

    The create spatial database checkbox is optional, and we generally uncheck it. It creates a spatial database for you to experiment with and has all the extensions packaged with PostGIS Bundle pre-installed.

  5. -- Do you want to register the GDAL_DATA prompt is new for PostGIS 2.0. This is because in order to do operations that require raster transformations or other rater warping / clipping etc, PostGIS uses GDAL epsg files. The windows build, makes a local copy of these in the PostgreSQL install\gdal-data folder and saying yes will automatically add an GDAL_DATA environment variable putting this path in for you. If you use GDAL already (or you are running both PostGIS 32-bit and 64-bit, chances are you already have this environment variable set and you may not want to overwrite it. PostGIS will work happily with an existing one, but just remember if you uninstall a PostGIS 2.0 or your GDAL, these functions may stop working and you'll need to repoint the environment variable.
  6. -- Enable Raster drivers. In 2.0.6, 2.1.3 all raster drivers were disabled by default. Saying yes to this prompt allows the most common drivers (that are considered safe). E.g. they don't call out to web services etc. If you are not content with the list shown there, you may want to explicitly enable additional drivers using the new PostGIS 2.2. GUC raster features PostGIS GDAL Enabled Raster Drivers GUC, that can either be set using ALTER SYSTEM (for 9.4+), or ALTER DATABASE for specific databases.
  7. -- Enable Out of database rasters. In 2.0.6, 2.1.3 out of database rasters were disabled by default. If you need them, say yes to this prompt. Again if you want each database to have different settings, you can opt for the new PostGIS 2.2 GUC route. Enable Out of Database rasters GUC
  8. For those of you who want to try experimental builds -- e.g. We have experimental Windows builds made weekly or as frequently as anything interesting happens in the PostGIS code base. These can be downloaded from

Creating a spatial database using SQL

You can use something like psql or the pgAdmin query window to create a database and spatially enable your database. This is the way to go if you have only a terminal interface to your db or you have a butt load of extensions you want to enable. Your psql steps would look something like this:

To install a bunch of extensions, just open up the pgAdmin SQL Query window (which we'll cover shortly) or psql and run this including only the extensions you want.

\connect gisdb;
-- Enable PostGIS (includes raster)
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D 
-- and other geoprocessing algorithms
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
-- routing functionality
-- spatial foreign data wrappers

-- LIDAR support
-- LIDAR Point cloud patches to geometry type cases
CREATE EXTENSION pointcloud_postgis;

Creating a spatial database using pgAdmin GUI

PostgreSQL comes packaged with a fairly decent admin tool called PgAdmin3. If you are a newbie or not sure what extensions you want, it's best just to use that tool to create a new database and look at the menu of extensions you have available to you.

  1. On windows PgAdmin III is under Start->Programs->PostgreSQL 9.5->PgAdmin III
  2. Login with the super user usually postgres and the password you chose during install. If you forgot it, then go into pg_hba.conf (just open it with an editor such as notepad or a programmer editor). Set the line
    host all all md5


    host all all trust

    If you are on a newer windows (say 2008 or Windows 7), you may see an additional option
    host all all ::1/128 trust

    The ::1/128 is usually the controlling one and is what localhost resolves to in IPV6 so you'll want to set this one.

    This will allow any person logging locally to the computer that PostgreSQL is installed on to access all databases without a password. ( means localhost only (32 is the bit mask). Note you can add additional lines to this file or remove lines to allow or block certain ip ranges. The lines highest in the file take precedence.

    So for example if you wanted to allow all users logging in access as long as they successfully authenticate with an md5 password, then you can add the line

    host all all md5
    . If it is below, you will still be able to connect locally without a password but non-local connections will need a valid username and password.

  3. Note: - The newer versions of PgAdmin III (1.7 something on) allow editing Postgresql.conf and pg_hba.conf using the PgAdmin III tool. These are accessible from Tools->Server Configuration and provide a fairly nice table editor to work with. This feature is only available if you have installed the adminpack.sql (this is located in C:\Program Files\PostgreSQL\9.x\share\contrib) (Admin Pack) in the postgres database .

    To install it --- switch to postgres database

    and run this command in the sql window of PgAdmin or using psql: CREATE EXTENSION adminpack;

    (NOTE: you can also use the extensions gui of PgAdmin to install in the postgres db)

  4. Now for the fun part - Create your database. Call it gisdb or whatever you want.
  5. It's generally a good idea to create a user too that owns the database that way you don't need to use your superuser account to access it.
  6. UPDATE: - The remaining steps in this section are not needed if you created a spatial database using the Create spatial database checkbox for your new database. However if you are trying to spatially enable an existing database. Do the remaining steps.

    Expand your database and you should see an extensions section PgAdmin III extensions, right-mouse click on that and select the New Extension option.

    Note the many options highlighted in yellow -- ain't that a beaut. The ones highlighted in yellow are the ones packaged in the PostGIS 2.2.0 windows bundle. Using PgAdmin will list all extensions available on your PostgreSQL server even if your PostgreSQL server is not on Windows. So you might see much less than we have highlighted if your distribution doesn't offer these or you didn't do the binary installation for them on the server. PostGIS extensions Address standardizer PostGIS extensions. Select postgis (this includes geometry, geography, and raster support). Then if you want to use topology or any of the other PostGIS family of extensions then repeat the step picking the desired extension. Also note the Definition tab, PostGIS extensions definition tab which allows you to install postgis in the non-default schema and will also allow you a painless way to upgrade to PostGIS 2.3.0 when that comes out by changing the version number from drop down or to upgrade to PostGIS 2.2.0 from an earlier version.

  7. If PostGIS Bundle install, you should see the PostGIS Shapefile and DBF loader in the plugins menu. .

    If the Plugins green is disabled (and says No Plugins installed) then most likely you have another PgAdmin or PostgreSQL install getting in the way. An easy fix is to open up PgAdmin III go under File->Options and make sure your PG bin path is pointing at the locations of your PostgreSQL bin (e.g. C:\Program Files\PostgreSQL\9.5\bin) and click the ... to repoint it if it is not.
  8. Verify you have the newest PostGIS with this query:

    SELECT postgis_full_version();

    Should output something of the form

    POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4090" SFCGAL="1.2.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY RASTER

Loading GIS Data Into the Database

Now we have a nice fully functional GIS database with no spatial data. So to do some neat stuff, we need to get some data to play with.

Get the Data

Download data from the MassGIS site.
For this simple exercise just download Towns
Extract the files into some folder. We will only be using the _POLY files for this exercise.

NOTE: Someone asked how you extract the file if you are on a linux box.


If you are on Linux/Unix, I find the exercise even easier. If you are on linux or have Wget handy - you can do the below to download the file after you have cded into the folder you want to put it in.


Now to extract it simply do the following from a shell prompt



Figure out SRID of the data

You will notice one of the files it extracts is called BOUNDARY_POLY.prj. A .prj is often included with ESRI shape files and tells you the projection of the data. We'll need to match this descriptive projection to an SRID (the id field of a spatial ref record in the spatial_ref_sys table) if we ever want to reproject our data.

  • Open up the .prj file in a text editor. You'll see something like NAD_1983_StatePlane_Massachusetts_Mainland_FIPS_2001 and UNIT["Meter",1.0]
  • Open up your PgAdmin III query tool and type in the following statement select srid, srtext, proj4text from spatial_ref_sys where srtext ILIKE '%Massachusetts%' And then click the green arrow. This will bring up about 10 records.
  • Note the srid of the closest match. In this case its 26986. NOTE: srid is not just a PostGIS term. It is an OGC standard so you will see SRID mentioned a lot in other spatial databases, gis webservices and applications. Most of the common spatial reference systems have globally defined numbers. So 26986 always maps to NAD83_StatePlane_Massachusetts_Mainland_FIPS_2001 Meters. Most if not all MassGIS data is in this particular projection.

Loading the Data

The easiest data to load into PostGIS is ESRI shape data since PostGIS comes packaged with a nice command line tool called shp2pgsql which converts ESRI shape files into PostGIS specific SQL statements that can then be loaded into a PostGIS database.

This file is located in the PostgreSQL bin folder which default location in Windows is Program Files/PostGreSQL/9.5/bin

Make a PostGIS mini toolkit

Since these files are so embedded, it is a bit annoying to navigate to. To create yourself a self-contained toolkit you can carry with you anywhere, copy the following files from the bin folder into say c:\pgutils:

 comerr32.dll krb5_32.dll libeay32.dll
libiconv-2.dll libintl-2.dll libpq.dll pgsql2shp.exe psql.exe
pg_dump.exe pg_restore.exe shp2pgsql.exe ssleay32.dll libproj-9.dll geos_c.dll geos

Note: The GUI loader is packaged as a self-contained postgisgui folder in the bin of your PostgreSQL install. If you prefer the GUI interface, you can copy that folder and run the shp2pgsql-gui.exe file from anywhere even an external file network path.

Load Towns data

  1. Open up a command prompt.
  2. Cd to the folder you extracted the towns data
  3. Run the following command:
    c:\pgutils\shp2pgsql -s 26986 BOUNDARY_POLY towns > towns.sql
  4. Load into the database with this command:
    psql -d gisdb -h localhost -U postgres -f towns.sql
    If you are on another machine different from the server, you will need to change localhost to the name of the server. Also you may get prompted for a password. For the above I used the default superuser postgres account, but its best to use a non-super user account.
  5. Alternatively you can use the gui to load the data and when you do, your screen will look something like this. PostGIS shapefile GUI loader Which is a little different from the PostGIS 1.5 loader, because it allows uploading multiple files at ones. To edit any of the settings for each file, click into the cell and the cell will become editable. In this case we replaced the default table name boundary_poly with towns.
    prepare etc
  6. This particular dataset is only polygons. You can override the behavior of bringing in as multipolgons by clicking the Options button checking the Generate simple geometries ... .

    One thing you can do with the shp2pgsql command line version pacakged with PostGIS 2.0-2.2, that you can't do with the GUI is to do a spatial transformation from one coordiante system to another. So witht eh command line, we can transform to 4326 (WGS 84) and load to geography type with a single command. Hopefully we'll see this in the GUI in a future release.

Indexing the data

Table indexes are very important for speeding up the processing of most queries. There is also a downside to indexes and they are the following

  1. Indexes slow down the updating of indexed fields.
  2. Indexes take up space. You can think of an index as another table with bookmarks to the first similar to an index to a book.

Given the above, it is often times tricky to have a good balance.  There are a couple general rules of thumb to go by that will help you a long way.

  1. Never put indexes on fields that you will not use as part of a where condition or join condition.
  2. Be cautious when putting index fields on heavily updated fields.  For example if you have a field that is frequently updated and is frequently used for updating, you'll need to do benchmark tests to make sure the index does not cause more damage in update situations than it does for select query situations.  In general if the number of records you are updating at any one time for a particular field is small, its safe to put in an index.
  3. Corrollary to 2.  For bulk uploads of a table - e.g. if you are loading a table from a shape, its best to put  the indexes in place after the data load because if an index is in place, the system will be creating indexes as its loading which could slow things down considerably.
  4. If you know a certain field is unique in a table, it is best to use a unique or primary index. The reason for this is that it tells the planner that once its found a match, there is no need to look for another.  It also prevents someone from accidentally inserting a duplicate record as it will throw an error.
  5. For spatial indexes - use a gist index. A gist basically stores the bounding box of the geometry as the index. For large complex geometries unfortunately, this is not too terribly useful.

The most common queries we will be doing on this query are spatial queries and queries by the town field. So we will create 2 indexes on these fields. NOTE: The loader has an option to create the spatial index which we took advantage of, so the spatial index one is not necessary but we present it here, just so if you ever need to create a spatial index, like for csv loaded data, you know how to.

CREATE INDEX idx_towns_geom
ON towns
USING gist(geom);

CREATE INDEX idx_towns_town
ON towns
USING btree(town);

Querying Data

Go back into PgAdmin III and refresh your view. Verify that you have a towns database now.

Test out the following queries from the query tool

SELECT ST_Extent(geom) FROM towns WHERE town = 'BOSTON';

SELECT ST_Area(ST_Union(geom)) FROM towns WHERE town = 'BOSTON';

Viewing the Data

If you are a GIS newbie, I highly recommend using QGIS. QGIS has ability to view PostGIS data both geometry and raster directly, do simple filters on it, is free, is cross-platform (Linux, Windows, MacOSX,Unix) and is the least threatening of all the GIS Viewers I have seen out there for people new to GIS.

Post Comments About Part 1: Getting Started With PostGIS: An almost Idiot's Guide (PostGIS 2.2)

This Document is available under the GNU Free Documentation License 1.2 & for download at the BostonGIS site

Boston GIS      Copyright 2015      Paragon Corporation