Transcript postgis-introduction

Introduction to PostGIS
PostGIS Basics for the New User
Paul Ramsey & Chris Hodgson
Refractions Research
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostgreSQL
• 1986 : Postgres project at Berkley
–
–
–
–
Successor to Ingres
Relational Model
Complex Objects
Extensibility
• 1995 : Postgres95 adds SQL Support
• 1996 : Open Source Community
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostGIS
•
•
•
•
•
PostgreSQL 7.1 (Tuple Toaster)
Real GIS Support
First Release in 2001
OpenGIS “Simple Features for SQL”
Current Release 0.8.2
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Why PostGIS?
• Because Databases are Better than
Files!
• Unified Storage, Management, Access
– SQL Everywhere
• Transactional Integrity
– Multiple Users, Multiple Edits
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostGIS Users
• Data Handlers
• Unified Access Language (SQL)
• Unified Metadata Location
– GlobeXplorer
– i-cubed
– Refractions
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostGIS Users
• Real Time Systems Developers
• Unified Access Language (SQL)
• Standard Access Protocols
– JDBC
– ODBC
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Team CIMAR : DARPA Grand Challenge
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostGIS Users
• Spatial Infrastructures
LAN
QGIS
www.refractions.net
PostGIS
WMS / WFS
JUMP
WMS
Client
Internet
WFS
Client
OpenSource GIS 2004
Ottawa, Canada
PostgreSQL
• Version 7.5 Native Windows Support
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
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
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Create a Database
• createdb postgis
– Make the database
• psql postgis
– Connect to the database
• create, insert, select
– Try the database
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
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
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Simple Spatial SQL
POINT(0 5)
POINT(0 0)
www.refractions.net
POINT(5 5)
POINT(5 0)
OpenSource GIS 2004
Ottawa, Canada
Load Shape Files
• select count(*) from bc_roads
• select count(*) from bc_voting_areas
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
bc_pubs
$
$
$
$
$
$
$$
$
$$
$$
$$$
$
$$
$$
$
$
$$
$$$ $
$
$$
$$
$
$$
$
$
www.refractions.net
$
$
$
$$$
$
$
$$$
$$$$$
$ $ $$
$$$
$
$ $$
$$$
$$
$
$$
$
$$$$
$$
$
$$$
$$$
$$$
$$$$
$
$$
$
$$$
$$$ $$$ $$$
$$$$$$
$$$$$$$
$
$$
$$
$$
$ $
$$$$
$
$
$$
$ $$$
$
$$
$$
$$
$
$
$$ $$
$
$
$$$
$
$$
$
$$
$
$
OpenSource GIS 2004
Ottawa, Canada
bc_roads
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
bc_hospitals
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
#
####### #
#
# # #
# #
#
#
#
#
#
www.refractions.net
#
OpenSource GIS 2004
Ottawa, Canada
bc_municipality
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
bc_voting_areas
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
bc_voting_areas
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Creating Spatial Indexes
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Using Spatial Indexes
select
select
gid,
gid,name
name
from bc_roads
from bc_roads
where
where
crosses(
crosses(
the_geom,
the_geom,
GeomFromText(‘LINESTRING(…)’,42102)
) GeomFromText(‘LINESTRING(…)’,42102)
and
)
the_geom &&
GeomFromText(‘LINESTRING(…)’,42102)
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Query Plans
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
PostgreSQL Optimization
#--------------------------------------------------------------------------# CONNECTIONS AND AUTHENTICATION
#--------------------------------------------------------------------------# - Connection Settings #listen_addresses = 'localhost'
#port = 5432
max_connections = 100
#superuser_reserved_connections = 2
#rendezvous_name = ''
# what IP interface(s) to listen on;
# defaults to localhost, '*' = any
# defaults to the computer name
# - Security & Authentication #authentication_timeout = 60
#ssl = false
#password_encryption = true
#db_user_namespace = false
# 1-600, in seconds
#--------------------------------------------------------------------------# RESOURCE USAGE (except WAL)
#--------------------------------------------------------------------------# - Memory shared_buffers = 1000
# min 16, at least max_connections*2, 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
#vacuum_cost_page_miss = 10
www.refractions.net
# 0-10000 credits
# 0-10000 credits
OpenSource GIS 2004
Ottawa, Canada
Data Integrity
Valid
www.refractions.net
Invalid
OpenSource GIS 2004
Ottawa, Canada
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)
) < 2000;
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Spatial Joins
• Associate two tables based on a spatial
relationship, rather than an attribute
relationship.
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
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;
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Overlays
• Table on table overlays are possible
with the Intersection() function.
• Our example will only overlay one
polygon with another table.
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
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’;
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Coordinate Projection
SRID=42102;MULTILINESTRING((
1004687.04355194 594291.053764096,
1004729.74799931 594258.821943696))
SRID=4326;MULTILINESTRING((
-125.9341 50.3640700000001,
-125.9335 50.36378))
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada
Exercises &
Questions
www.refractions.net
OpenSource GIS 2004
Ottawa, Canada