Introduction%20to%20PostGIS

1
Introduction to PostGIS

• PostGIS Basics for the New User

Paul Ramsey Chris HodgsonRefractions Research
2
PostgreSQL

• 1986 Postgres project at Berkley
• Successor to Ingres
• Relational Model
• Complex Objects
• Extensibility
• 1995 Postgres95 adds SQL Support
• 1996 Open Source Community

3
PostGIS

• PostgreSQL 7.1 (Tuple Toaster)
• Real GIS Support
• First Release in 2001
• OpenGIS Simple Features for SQL
• Current Release 0.8.2

4
Why PostGIS?

• Because Databases are Better than Files!

• Unified Storage, Management, Access
• SQL Everywhere
• Transactional Integrity
• Multiple Users, Multiple Edits

5
PostGIS Users

• Data Handlers
• Unified Access Language (SQL)
• Unified Metadata Location
• GlobeXplorer
• i-cubed
• Refractions

6
PostGIS Users

• Real Time Systems Developers
• Unified Access Language (SQL)
• Standard Access Protocols
• JDBC
• ODBC

7
Team CIMAR DARPA Grand Challenge
8
PostGIS Users

• Spatial Infrastructures

WMSClient
JUMP
Internet
WMS / WFS
LAN
PostGIS
WFSClient
QGIS
9
PostgreSQL

• Version 7.5 Native Windows Support

10
Windows PostgreSQL

• \bin Executables
• \include Include files for compilation
• \lib DLL shared library files
• \share Extensions
• env.bat Command prompt
• initdb.bat Initialize \data area
• pgstart.bat Start the database server

11
Create a Database

• createdb postgis
• Make the database
• psql postgis
• Connect to the database
• create, insert, select
• Try the database

12
Load PostGIS

• PostgreSQL Extension
• libpostgis.so
• postgis.sql
• Requires PL/PgSQL
• createlang plpgsql postgis
• psql -f postgis.sql postgis
• psql -f spatial_ref_sys.sql postgis

13
Simple Spatial SQL
POINT(0 5)
POINT(5 0)
POINT(0 0)
14
Load Shape Files

• select count() from bc_roads
• select count() from bc_voting_areas

15
bc_pubs
16
bc_roads
17
bc_hospitals
18
bc_municipality
19
bc_voting_areas
20
bc_voting_areas
21
Creating Spatial Indexes
22
Using Spatial Indexes
select gid, name from bc_roads where
crosses( the_geom, GeomFromText(LINESTRI
NG(),42102) )and the_geom
GeomFromText(LINESTRING(),42102)

• select gid, name from bc_roads where
  crosses( the_geom, GeomFromText(LINESTRI
  NG(),42102) )

23
Query Plans
24
PostgreSQL Optimization
-------------------------------------------------
-------------------------- CONNECTIONS AND
AUTHENTICATION ----------------------------------
----------------------------------------- -
Connection Settings - listen_addresses
'localhost' what IP interface(s) to listen on
defaults to localhost, '' any port
5432 max_connections 100 superuser_reserved_con
nections 2 rendezvous_name '' defaults to
the computer name - Security Authentication
- authentication_timeout 60 1-600, in
seconds ssl false password_encryption
true db_user_namespace false ----------------
--------------------------------------------------
--------- RESOURCE USAGE (except
WAL) --------------------------------------------
------------------------------- - Memory
- shared_buffers 1000 min 16, at least
max_connections2, 8KB each work_mem 1024
min 64, size in KB maintenance_work_mem
16384 min 1024, size in KB max_stack_depth
2048 min 100, size in KB vacuum_cost_page_hit
1 0-10000 credits vacuum_cost_page_miss
10 0-10000 credits
25
Data Integrity
Valid
Invalid
26
Distance Queries

• select sum(upbc) as unity_voters from
  bc_voting_areas where the_geom setsrid(
  expand(POINT()geometry,2000) 42102 )
  and distance( the_geom,
  geomfromtext(POINT(), 42102) ) lt 2000

27
Spatial Joins

• Associate two tables based on a spatial
  relationship, rather than an attribute
  relationship.

28
select m.name, sum(v.ndp) as ndp,
sum(v.lib) as liberal, sum(v.gp) as green,
sum(v.upbc) as unity, sum(v.vtotal) as total
from bc_voting_areas v, bc_municipality m,
where v.the_geom m.the_geom and
intersects(v.the_geom, m.the_geom) group by
m.name order by m.name
29
Overlays

• Table on table overlays are possible with the
  Intersection() function.
• Our example will only overlay one polygon with
  another table.

30
create table pg_voting_areas as select
intersection(v.the_geom, m.the_geom) as
intersection_geom, area(v.the_geom) as
va_area, v., m.name from bc_voting_areas
v, bc_municipality m where v.the_geom
m.the_geom and intersects(v.the_geom,
m.the_geom) and m.name PRINCE GEORGE
31
Coordinate Projection
SRID42102MULTILINESTRING((1004687.04355194
594291.053764096,1004729.74799931
594258.821943696))
SRID4326MULTILINESTRING((-125.9341
50.3640700000001,-125.9335 50.36378))
32
Exercises Questions