Spatial Databases

1
Spatial Databases

• DT211-4 DT228-4 Semester 2
• 2008-9
• Pat Browne

http//www.comp.dit.ie/pbrowne/Spatial20Databases
20SDEV4005/Spatial20Databases20SDEV4005.htm
2
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).
• Several DT249 students have found new career
  opportunities as a direct result of completing a
  spatial database project.

3
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).
• The spatial database course brings together many
  topics that you have already studied (e.g.
  databases, graphics, objection orientation) 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.

4
My Interest in Spatial Databases

• Worked at Ordnance Survey Ireland (OSI).
• Started working in SOC 2000.
• Supervised 5 GIS undergraduate projects, (3
  firsts)
• Supervised one GIS masters (a first).
• One of my students, Declan Lynch, had a poster
  accepted for FOSS4G 2006, the worlds premier
  conference on open source software for GIS.
• Together with Mark Foley I wrote the two GIS
  syllabi for DT228/DT249.
• I hope to complete my own PhD on a framework for
  spatial representation in 2009.

5
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 the SQL language.

6
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
• 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

7
Spatial Databases must integrate with other
applications and data.
HTML Viewer Java Viewer
GIS Desktop Applications
(Internet)
Wireless Mobile Devices
Network
Map Renderer
Server Side Applications
Spatial DB
8
Spatial enabled DB Summary

• Database an integrated set of data on a
  particular subject. Can include spatial and
  non-spatial 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 GIS

9
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'

10
Result 1
11
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))

12
Result 2
13
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 gt 80)
• INTERSECT
• (SELECT x, y, time
• FROM Region
• WHERE temperature lt 32)

14
Example Query

• How many people live within 5 miles of the
  toxic gas leak?SELECT sum(population) FROM
  census_tracts WHERE distance(census_geom,POIN
  T()) lt 5

Image from Paul Ramsey Refractions Research
15
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
16
(No Transcript)
17
Visualization of Information Population1
18
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
19
Networking

• For enhanced network traversal and routing
  PostgreSQL/PostGIS can be extended with pgRouting
  which can perform
• Shortest path search (with 3 different
  algorithms)
• Traveling Salesperson Problem solution (TSP)
• Driving distance geometry calculation

http//pgrouting.postlbs.org/
20
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.
21
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.

22
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.
  Geometric and thematic information.

23
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 algorithms are provided as
  database extensions (e.g. PostGIS) or desktop
  GIS (e.g. OpenJump or uDig)

24
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 these representations are provided by
  the DBMS extensions (via APIs) and GIS.

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

26
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
  object-oriented 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, ArgoCaseGE,
  uDIG .

27
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 simple feature model
• 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 constraint databases and
  temporal information

28
Main Topics

• The focus is on generic system issues rather than
  applications.
• Database systems PostgreSQL
• Spatial databases PostGIS
• OGC Simple Features for SQL
• SQL for spatial and non-spatial data
• Modelling for spatial databases
• Geographic data on the web (e.g. Geoserver,
  Mapbuilder, Open Layers, WMS,WFS)
• Spatial Data Mining using Weka

29
Course Text
The course text is   Spatial Databases With
Application to GIS by Philippe Rigaux, Michel
Scholl, and Agnès Voisard Publisher
Elsevier Google Books  
30
Good Reference
Spatial Database Book Project http//www.spatial.c
s.umn.edu/Book/
31
Good Reference for Data Mining
Data Mining Practical Machine Learning Tools
and Techniques with Java Implementations Ian H.
Witten, Eibe Frank, Jim Gray (Editor)
32
Assignment

• A class project to build 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.

33
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,
  Travellers.
• 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