INTRODUCTION TO POSTGIS
Installation - Tutorial - Exercises
03 September 2011
Document Subtitle (From Properties Subject)
Suite 300 – 1207 Douglas Street Victoria – British Columbia CANADA – V8W 2E7
Phone: (250) 383-3022 Fax: (250) 383-2140
Created: March 6, 2007 Printed: September 3, 2011
1 Introduction 3 1.1 Requirements & Set-up 3 1.2 Spatial Databases 4 1.3 Conventions 4 1.4 Other Downloads 4 2 Database Installation 5 2.1 PostgreSQL Installation 5 2.2 PostGIS Installation 14 2.3 Spatially-Enable PostgreSQL 17
2.3.1 Enabling PostGIS Without template_postgis 22 3 Using PostGIS 24 3.1 Simple Spatial SQL 24
3.1.1 Examples of Well-Known Text 25 3.2 OGC Metadata Tables 25 3.3 Loading Shape Files 26
3.3.1 SHP2PGSQL Command Line Options 28 3.4 Viewing Data in PostGIS 29 3.5 Spatial Indexes 30
3.5.1 Creating Spatial Indexes 30
3.5.2 Using Spatial Indexes 30
3.5.3 Spatial Index Test 31
3.5.4 Indexes and Query Plans 32
3.5.5 When Query Plans Go Bad 34 3.6 PostgreSQL Optimization 35 3.7 Spatial Analysis in SQL 37
3.7.1 Exercises 37 3.8 Basic Exercises 38 4 Advanced PostGIS 40 4.1 Data Integrity 40
4.2 Distance Queries 41 4.2.1 Distance Not Buffer 42 4.3 Spatial Joins 43 4.4 Overlays 45 4.5 Coordinate Projection 46 4.6 Advanced Exercises 48 5 MapServer & PostGIS 51 5.1 Basic MapServer Configuration 51 5.2 MapServer Filters and Expressions 52 5.3 MapServer with SQL 54
Introduction to PostGIS – Page 2
PostGIS is a spatial database add-on for the PostgreSQL relational database server. It includes support for all of the functions and objects defined in the OpenGIS “Simple Features for SQL” specification. Using the many spatial functions in PostGIS, it is possible to do advanced spatial processing and querying entirely at the SQL command-line.
This workshop will cover:
； Installation and setup of PostgreSQL,
； Installation of the PostGIS extension,
； Loading sample data,
； Spatial and attribute indexing,
； Performance tuning the database,
； Basic spatial SQL, and
； Some best practices for spatial SQL.
1.1 REQUIREMENTS & SET-UP
This workshop will use the latest Windows native PostgreSQL installer, the latest PostGIS installer, and a data package of shape files – all of these components are included on the
Copy the \postgis-workshop directory from the CDROM to your C: drive. Highlight the
\postgis-workshop CDROM directory, and hit CTRL-C, then move to your C: drive, and
Introduction to PostGIS – Page 3
1.2 SPATIAL DATABASES
Like Oracle Spatial, DB2 Spatial, and SQL Server Spatial, PostGIS adds capabilities to an existing relational database engine, in this case PostgreSQL. In fact, PostGIS could be re-named as “PostgreSQL Spatial”, as it functions in the same way as the proprietary spatial database extensions:
； It adds a “geometry” data type to the usual database types (e.g. “varchar”, “char”,
“integer”, “date”, etc).
； It adds new functions that take in the “geometry” type and provide useful information
back (e.g. ST_Distance(geometry, geometry), ST_Area(geometry),
ST_Length(geometry), ST_Intersects(geometry, geometry), etc).
； It adds an indexing mechanism to allow queries with spatial restrictions (“within this
bounding box”) to return records very quickly from large data tables.
The core functionalities of a spatial database are easy to list: types, functions, and indexes. What is impressive is how much spatial processing can be done inside the database once those simple capabilities are present: overlay analyses, re-projections, massive seamless spatial tables, proximity searches, compound spatial/attribute filters, and much more.
Session instructions that require user-interaction are presented in this document inside of grey boxes – the text to be entered is in boldface, and the results are in normal text. In general,
directions to be performed will be presented in boldface.
1.4 OTHER DOWNLOADS
The PostgreSQL source code is available from: http://www.postgresql.org/.
The PostGIS source code is available from: http://postgis.refractions.net/.
The GEOS source code is available from: http://geos.refractions.net/.
The Proj4 source code is available from: http://proj.maptools.org/.
The PgAdmin administration tool is available from: http://www.pgadmin.org.
Introduction to PostGIS – Page 4
2 DATABASE INSTALLATION
2.1 POSTGRESQL INSTALLATION
The PostgreSQL installation package is on the CDROM in:
Further information on installing PostgreSQL can be found on the PostgreSQL website:
1. Double-click the file postgresql-8.2.msi
2. Select your language:
Introduction to PostGIS – Page 5
3. The installer will start up, recommend that you close all Windows programs, and then
display the license agreement.
Introduction to PostGIS – Page 6
4. When the “Installation options” selector comes up, do not enable installation of the
The PostGIS Spatial Extension included in the windows installer is often a few versions behind the current stable release. We will install a current PostGIS version separately after completing the PostgreSQL install.
Introduction to PostGIS – Page 7
5. Install PostgreSQL as a service and select the account to run the service under.
Installing PostgreSQL as a service installs it as a windows service so it starts up
automatically when your machine is restarted.
； Service name – Name associated with PostgreSQL service.
； Account name – User name to run the service under. The default postgres is standard.
You can enter an existing account or a new one. If you enter a new account the installer
will automatically create an account for you. If you enter an existing account the
account must not be an administrator account.
Administrator accounts are not allowed for security reasons. If a hacker were to gain entry
to the computer using PostgreSQL they would gain the permissions of account name the
service is run under. Ensuring this is a non-administrator account limits the potential harm
they can inflict on the system.
； Account Domain – This should be the name of your computer.
If your windows system is setup for domain authentication you will need to enter the
domain here. However for our purposes the computer name will suffice.
Introduction to PostGIS – Page 8