PostGIS Workshop

By Cynthia Lawrence,2014-09-03 11:24
10 views 0
how to use postgis


Installation - Tutorial - Exercises

03 September 2011

Document Title

    Document Subtitle (From Properties Subject)

    Prepared By

    Paul Ramsey

    Refractions Research

    Suite 300 1207 Douglas Street Victoria British Columbia CANADA V8W 2E7

    Phone: (250) 383-3022 Fax: (250) 383-2140

    Document Tracking


    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.


    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

    workshop CDROM.

    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

    hit CTRL-V.

    Introduction to PostGIS Page 3


    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.


    The PostgreSQL source code is available from:

    The PostGIS source code is available from:

    The GEOS source code is available from:

    The Proj4 source code is available from:

    The PgAdmin administration tool is available from:

    Introduction to PostGIS Page 4



    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

    PostGIS extension.

    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

Report this document

For any questions or suggestions please email