Introduction to PostGIS

Contents

Installation - Tutorial - Exercises

23 February 2013

1 Introduction

1.1 Requirements & Set-up

1.2 Spatial Databases

1.3 Conventions

1.4 Other Downloads

2 Database Installation

2.1 PostgreSQL Installation

2.2 PostGIS Installation

2.3 Spatially-Enable PostgreSQL

3 Using PostGIS

3.1 Simple Spatial SQL

3.2 OGC Metadata Tables

3.3 Loading Shape Files

3.4 Viewing Data in PostGIS

3.5 Spatial Indexes

3.6 PostgreSQL Optimization

3.7 Spatial Analysis in SQL

3.8 Basic Exercises

4 Advanced PostGIS

4.1 Data Integrity

4.2 Distance Queries

4.3 Spatial Joins

4.4 Overlays

4.5 Coordinate Projection

4.6 Advanced Exercises

5 MapServer & PostGIS

5.1 Basic MapServer Configuration

5.2 MapServer Filters and Expressions

5.3 MapServer with SQL

1 Introduction

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.1Requirements & 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 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.

1.2Spatial 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.

1.3Conventions

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.4Other 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.

2Database Installation

2.1PostgreSQL Installation

The PostgreSQL installation package is on the CDROM in:

\postgis-workshop\software\postgresql-8.2.msi

Further information on installing PostgreSQL can be found on the PostgreSQL website:

http://www.postgresql.org/docs/

Steps:

1. 1.Double-click the file postgresql-8.2.msi 
2. 2.Select your language: 

1. 3.The installer will start up, recommend that you close all Windows programs, and then display the license agreement. 

1. 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.

1. 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.

• .Account password / Verify password – Password associated with the account name (entered twice for verification).

• .If the user account does not already exist on the computer you will be prompted to create the account.

• .Depending on the “strength” of the password you entered the following message might appear – Click ‘No’.

1. 6.Initialize database cluster. This sets up a database cluster with the given connection information.

A database cluster is a group of databases managed by a single PostgreSQL server instance.

• .Port Number:  Port Number the database cluster is to reside on. 
• .Addresses:  Check the ‘Accept connection on all addresses, not just localhost’ if you want other machines to be able to access the database from any other machine. Even with this selected you will still have to specifically indicate which hosts or networks you will accept connections from by editing a configuration file. This is covered in the administrative section of the workshop. 
• .Locale: Select a locale. This will become the default locale of all databases. The C and POSIX locales will cause the system to behave as if it has no locale support. For the purposes of this demonstration we will use the C locale. 
• .Encoding: Character encoding to use. For this demonstration we will use SQL_ASCII. 
• .Superuser Name: This username is used to connect to the database. We will use postgres, although it is not recommended that you use the same name/password as the service account.

This username/password is not associated at all with the service account name/password. The service account name and password is the account the PostgreSQL process is executed within and resides as an operating system user. The Superuser Name/Password is used to connect to the database and perform operations on the database.

• .Password / Password(again): The password associated with the superuser name. Again we will use postgres. 

• .On selecting ‘Next’ you will be presented with the following information dialog.

1. 7.The PL/PgSQL language is required for PostGIS, so ensure it is selected.

PL/Perl is handy for Perl programmers who want to write triggers and functions in Perl, but not required by PostGIS.

1. 8.
   This form allows you to select additional modules to be installed. Ensure Adminpack is selected. The Fuzzy String Match (soundex, etc), and Tsearch2 (full text indexing) modules might come in handy if you work with PostgreSQL enough to become a power user.
   
   
   
   

   2. 9.That is it for the tough decisions!  Click ‘Next’ to install PostgreSQL.

   
   

   

   
   

   PostgreSQL is now installed on your computer!

   2.2PostGIS Installation

   1. 1.Double-click the postgis-pg82-setup-1.3.1.exe file found on the CD at \postgis-workshop\software\ . 

   
   

   1. 2.De-select the “Create Database” option.
       

   The Create Database option will create a new database with PostGIS installed to the database. Because you will often want to create PostGIS enabled databases without running the installer we will show how to create a PostGIS enabled database manually.

   

   1. 3.Accept the default install location.
      
       
   2. 4.Accept the default values. 
       

   Because the “Create Database” option was not selected the information on this form has no effect on the PostGIS install. All these fields can be left blank. However if you selected the “Create Database” option you will need to enter the user name, password, database name, and port. If you do not enter valid values the installer will hang.

   

   

   PostGIS is now installed on your computer.

   2.3Spatially-Enable PostgreSQL

   The installer will add a PostgreSQL menu to your Start menu.

   • .Navigate to the PostgreSQL menu item and run PgAdmin III.  
   • .Double click on the “PostgreSQL Database Server” tree entry. You will be prompted for the super user password to connect to the “template1” database.  

   

   • .Navigate to the “Databases” section of the database tree and open “Edit  New Object  New Database”. Add a new database named “postgis”, with “postgres” as the owner “template_postgis” as the template.
     
     By using the “template_postgis” database as the template, you get a new database with spatial capabilities already installed and enabled. 

   

   Note on table-spaces:  PostgreSQL installs with two default table-spaces, pg_default and pg_global.  The pg_global table-space is used for system tables and other objects that need cluster-wide visibility, do not use it for your working databases, use pg_default (which is what will be chosen if you do not specify a table-space explicitly).

   • .Open up the new “postgis” database. 

   

   Note on encodings:  Encodings are used to consistently store “special characters”, to associate a particular byte (201) with a particular character (É).  Different encodings map the same bytes to different characters, so if you are using any special characters, it is important to be explicit about what encoding they are in.  

   The “SQL_ASCII” encoding is not actually an encoding at all, it is an “I do not know or care what the encoding is” encoding.  If you use an explicit encoding, like “UTF-8”, the database can transparently convert your data into different encodings for you.  If you use “SQL_ASCII”, you will have to do the translations yourself.  

   “UTF-8” is the recommended database encoding because it can handle every known character.

   • .Navigate to postgis  Schemas  public  Tables and see what tables exist.  You should see “geometry_columns” and “spatial_ref_sys” tables, that are standard tables created by PostGIS.  The tables starting in “pg_ts_” are used by the full-text-search module and can be ignored. 

   

   2.3.1Enabling PostGIS Without template_postgis

   If your database does not have a template_postgis to use as a database creation template, you can load PostGIS manually, by invoking a pair of SQL scripts that install the PostGIS functions and types.

   • .In PgAdmin, open up the SQL window by clicking the SQL button (the one with the pencil). 

   

   • .Choose “File  Open…” and navigate to 

   C:\Program Files\PostgreSQL\8.2\share\contrib\lwpostgis.sql

   • .Press the “Run” button. (The green triangle.) The lwpostgis.sql file will execute, loading the PostGIS functions and objects into the “postgis” database. 
   • .Choose “File  Open…” and navigate to  

   C:\Program Files\PostgreSQL\8.2\share\contrib\spatial_ref_sys.sql

   • .Press the “Run” button again. The spatial_ref_sys.sql file will execute, loading the EPSG coordinate reference systems into the PostGIS spatial reference table.  

   The “postgis” database is now set up and ready for data to be loaded. There are a few things that we should remember for other applications:

    Database Name:  postgis
    User Name:   postgres

   Administration Note:  We have created our database as the “postgres” super-user. In a real multi-user system, you will probably have different users with different access privileges to various tables and functions. Setting up users and access permissions can be a complicated DBA exercise, so for workshop purposes we are using the all-powerful super-user.

   
   

   Unix Note:  If you install PostgreSQL and PostGIS on a Unix server, you can still use PgAdmin to administer your database. However, when loading the lwpostgis.sql file, you must use the lwpostgis.sql file from your Unix distribution, the one created during the build and install of PostGIS. This is because the lwpostgis.sql file includes references to machine library files that include the spatial functions. The Windows version of the file will not make sense to a Unix machine because the references will not point to the right files.

   3Using PostGIS

   Connect to the “postgis” database with PgAdmin, and open up the query tool window, using the Tools  Query Tool or the button with the “SQL” icon on it.

   3.1Simple Spatial SQL

   Now we will test creating a table with a geometry column, adding some spatial objects to the table, and running a spatial function against the table contents.

   Paste the following SQL into the query tool window, and then hit the green triangle “Execute” button (or the F5 shortcut key).

Terimakasih Anda telah membaca tentang
Judul: Introduction to PostGIS
Rating: 100% based on 90 ratings. 376 user reviews.
Ditulis Oleh aa
Semoga informasi Introduction to PostGIS bisa memberikan manfaat bagi Anda. Jangan lupa komentar Anda bila ingin bertanya. Sukses untuk kalian