Transcript Using PostgreSQL and PostGIS as a Spatial Da

Spatial Databases
DT211-4 DT228-4 Semester 2
2009-10
Pat Browne
http://www.comp.dit.ie/pbrowne/Spatial%20Databases%20SDEV4005/Spatial%20Databases%20SDEV4005.htm
Your Interest in Spatial Databases
Jobs in GIS
 Government
 Most major departments are developing GIS on an ongoing
basis: e.g. the Geological Survey of Ireland (GSI), Environment
Protection Agency (EPA), Duchas, OPW.
 Semi-states
 Many agencies have a big investment in GIS e.g. ESB.
 Local Authorities
 County councils run many GIS applications.
 Private Sector
 MAPFLOW, IMGS (Information with location).
 Post graduate research:
 DIT Digital Media Centre (DMC), The National Centre for
Geocomputation (Maynooth).
Your Interest in Spatial Databases
Technically Interesting
 Spatial databases provided the essential logic and
structure for a host interesting and creative applications
(e.g. emergency services routing, hospital placement,
game environments).
 This spatial database course brings together many
topics that you have already studied (e.g. databases,
graphics, objection orientation, statistics) and applies
them in innovative ways.
 Spatial databases can answer a range of questions from
“where is the nearest chipper?” to “is Sellafield killing
us?”.
 Spatial database work with many other technologies
(e.g. Internet ,wireless networks, and GPS.) Great
source of ideas for final year project.
What is a Spatial Database?
 A spatial database is a database system that is
optimized to store and query basic spatial
objects e.g.:
 Point: a house, a moving car
 Line: a road segment, road network
 Polygon: a county, voting area
 Which are usually augmented with spatial
relations, thematic information, and temporal
information, all expressed in a declarative way
using a appropriate languages (e.g. SQL+spatial
extensions).
Tables or layers of data1
Why Spatial Databases?
 Queries to databases are posed in high level declarative
manner (usually using SQL)
 SQL is the “lingua-franca” in the commercial database
world.
 Standard SQL operates on relatively simple data types.
 SQL3/OGIS1 supports several spatial data types and
operations. We will study the Open Geospatial
Consortium Standards (OGC).
 Additional spatial data types and operations can be
defined in spatial database. (CREATE TYPE statement)
 A DBMS is a way of storing information in a manner that
 enforces consistency,
 facilitates access,
 Allows users to relate data from multiple tables together
Spatial Databases must integrate
with other applications and data.
HTML Viewer
Java Viewer
(Internet)
GIS Desktop
Applications
Wireless
Mobile
Devices
Network
Custom
Applications
Map Renderer
Spatial
DB
Server Side
Applications
Spatial enabled DB Summary
 Database – an integrated set of data on a
particular subject. Can include spatial and nonspatial and temporal data.
 Databases offer many advantages over files.
 Relational databases dominate for non-spatial
use, ORDBMS used for spatial data.
 Databases address some limitations for
specialist or dedicated GIS.
Query 1
 “Display all counties that border Kildare”.
 This query can be implemented using the
following SQL command:
select c1.name as
name,transform(c1.the_geom,4326) as the_geom
from county c1,county c2
where
touches(c1.the_geom,c2.the_geom)
and
c2.name='Kildare';
Result 1
Query 2
 “Display all regional roads that intersect the N7 National Primary
Road within the region of Dublin Belgard” This query can be
implemented using the following SQL command:
SELECT r.class as name,transform(r.the_geom,4326) AS
the_geom
FROM regional_road r,national_primary_road n,county c
WHERE
n.class='N7'
AND
n.the_geom && r.the_geom
AND
intersects(n.the_geom,r.the_geom)
AND
c.name='Dublin Belgard'
AND
contains(c.the_geom,intersection(r.the_geom,n.the_geom));
Result 2
Querying moving objects
Find where and when will it snow given
Clouds(X, Y, Time, humidity)
Region(X, Y, Time, temperature)
(SELECT
x, y, time
FROM
Clouds
WHERE humidity >= 80)
INTERSECT
(SELECT x, y, time
FROM
Region
WHERE temperature <= 32)
Example Query
 “How many people live within 5 miles
of the toxic gas leak?”
SELECT sum(population)
FROM census_tracts
WHERE
distance(census_geom,’POINT(…)’) < 5
Image from Paul Ramsey Refractions Research
Example Query
 “What is the area of all parks inside the
Dublin postal district 1?”
SELECT sum(area(park_geom))
FROM parks, postalDistrict
WHERE
contains(postalDistrict_geom,park_geom)
AND
postalDistrict_name = ‘Dublin 1’
Based on lecture from Paul Ramsey Refractions Research
Visualization of Information
Population1
Example Query
 What is the maximum distance a student
has to travel to school?”
SELECT
max(distance(
student_location, school_location
))
FROM students, schools
Based on lecture from Paul Ramsey Refractions Research
Networking
 For enhanced network traversal and
routing PostgreSQL/PostGIS can be
extended with pgRouting software which
can perform:
 Shortest path search (with 3 different
algorithms)
 Traveling Salesperson Problem solution
(TSP)
 Driving distance geometry calculation
http://pgrouting.postlbs.org/
Querying a non-spatial attribute
Find the countries of western Europe with population greater than 50
million. This is a projection on the attribute population. Unlike a
conventional database query we often want the query result and the
original context, in this case Europe.
Course Overview
 This course focuses on the use of database
management systems (DBMS) to store spatial
information. A spatially enabled DBMS is a
central component of a Geographical
Information System (GIS). GIS has a major role
to play in managing the national physical and
informational infrastructure. An understanding of
spatially enabled DBMS is vital in implementing
any information system where geographic data
is required. This course focuses on the role of
the DBMS in geographical applications.
Course Description 1
 Foundations Fundamental geographic
concepts for GIS The world in spatial
terms, how natural and man made
features can be stored in a DBMS.
Qualitative and quantitative location e.g.
geo-referencing and coordinate systems.
Maps as representation of the world and of
information. We focus on topological,
geometric, and thematic information.
Course Description 2
 Algorithms for GIS: Intersection of lines,
operations on polygons, network traversal,
auto-correlation, statistical operations,
searching. We focus on the use of
algorithms, not their design. The actual
algorithms are provided as database
extensions (e.g. PostGIS) or desktop GIS
(e.g. OpenJump or uDig)
Course Description 3
 Spatial representations: Raster, vector,
TIN, quadtrees, R-trees, scan orders,
polygon coverage, discrete objects,
networks, time, connections and topology,
networks, distance and direction, flow and
diffusion, spatial hierarchies, boundaries,
spatial patterns, attributes of relationships.
As with the algorithms the actual
representations are provided by the DBMS
extensions (via APIs) and GIS.
Course Description 4
 Applications of geospatial data:
Transportation networks, natural
resources, soil data, oceanography, land
cover, geology, climate, terrain, land
records, administrative boundary data,
demographic studies, decision support
and health data.
Course Description 5
 Spatial databases Spatial data: definitions,
formats, models, queries the relational model,
advanced SQL, data modelling techniques,
implementing a simple database, post relational
database models, object-relational and objectoriented models, spatial data structures, spatial
indexing e.g. R-Tree, networking, database
issues in GIS. The course will involve practical
work on a range of appropriate software e.g.
PostgreSQL/PostGIS, MLPQ/Presto, Rstatistical package, ArgoCaseGE, uDIG,
OpenJump, GeoServer, Openlayers.
Learning Outcomes
 On completion of the spatial database module,
you will be able to:
 Use a database to store and query spatial data
 Develop applications that use a spatially enabled
DBMS
 Understand and use the OGC standards GML,
SFSQL1, WMS, WFS, WPS.
 Distinguish and use appropriate database models
 Understand the DBMS extensions and APIs required
by application programs to handle spatial data.
 Understand some issues in spatial database research
such as ontologies.
Main Topics in a nutshells
 Spatial databases
 Web mapping
 Spatial Statistics
 Geographical Ontologies.
Main Topics
 Spatial database systems: PostgreSQL




PostGIS.
OGC “Simple Features for SQL”
Geographic Markup Language (GML)
Spatial statistics; spatial R
Geographic data on the web (e.g.
Geoserver, Mapbuilder, Open Layers,
Open Street Map, WMS,WFS,WPS)
Spatial Ontologies using Protege.
References
http://www.spatial.cs.umn.edu/Book/
Spatial Databases:
With Application to GIS
Rigaux, Scholl, Voisard
Geography Mark-Up
Language:
Foundation for the
Geo-Web
GIS: A Computing Perspective
Michael Worboys, Matt Duckham
http://www.pragprog.com/titles/sdgis/gis-for-web-developers
References
Lloyd: Spatial Data Analysis
Applied Spatial Data Analysis with R
Bivand, Pebesma, Gómez-Rubio
Theories of geographic concepts:
ontological approaches to semantic
integration By Marinos Kavouras, Margarita
Kokla
Assignment
 A project to build and document a geographic






web site using:
Open Street map data (OSM)
Central statistics office (CSO)
PostgreSQL/PostGIS spatial DB
Geoserver
OGC clients OpenJump, and client APIs
Mapbuilder, OpenLayers
Overall web site will contain several themes.
Assignment
 The web site will
 Contain several CSO themes
 Demographic : Total Persons, Persons by Age, Sex and Marital Status.
 Household : Type of Household, Household Composition, Household Size,





Family Nucleus, Life Cycle, etc.
Employment : Economic Status, Industry, Occupation, Carers and Disabled.
Socio-Economic Status : Socio-Economic Group, Social Class.
Social/Ethnic : Education, Irish Speakers, Religion, Nationality, Place of
Birth, Travelers.
Transportation : Means of Transport, Commuting Times.
Housing : Building Type, Age of Buildings, Nature of Occupancy, Rent,
Number of Rooms, Heating, Water Supply, Utilities, Computers.
 Routing functionality.
 Focus on Dublin area
 Census 2006 Results