Spatial databases l.jpg
This presentation is the property of its rightful owner.
Sponsored Links
1 / 33

Spatial Databases PowerPoint PPT Presentation

  • Uploaded on
  • Presentation posted in: General

Spatial Databases. DT211-4 DT228-4 Semester 2 2009-10 Pat Browne. Your Interest in Spatial Databases Jobs in GIS. Government

Download Presentation

Spatial Databases

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

Spatial databases l.jpg

Spatial Databases

DT211-4 DT228-4 Semester 2


Pat Browne

Your interest in spatial databases jobs in gis l.jpg

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 l.jpg

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 l.jpg

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 data 1 l.jpg

    Tables or layers of data1

    Why spatial databases l.jpg

    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 l.jpg

    Custom Applications

    Spatial Databases must integrate with other applications and data.

    HTML Viewer Java Viewer

    GIS Desktop Applications


    Wireless Mobile Devices


    Map Renderer

    Server Side Applications



    Spatial enabled db summary l.jpg

    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 specialist or dedicated GIS.

    Query 1 l.jpg

    Query 1

    • “Display all counties that border Kildare”.

    • This query can be implemented using the following SQL command:

      select as name,transform(c1.the_geom,4326) as the_geom

      from county c1,county c2




    Result 1 l.jpg

    Result 1

    Query 2 l.jpg

    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




      n.the_geom && r.the_geom



      AND'Dublin Belgard'



    Result 2 l.jpg

    Result 2

    Querying moving objects l.jpg

    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)


    (SELECT x, y, time

    FROM Region

    WHERE temperature <= 32)

    Example query l.jpg

    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 query15 l.jpg

    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

    Slide17 l.jpg

    Visualization of Information


    Example query18 l.jpg

    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 l.jpg


    • 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

    Querying a non spatial attribute l.jpg

    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 l.jpg

    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 nationalphysical 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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    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 l.jpg

    Course Description 5

    • Spatial databasesSpatial 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, R-statistical package, ArgoCaseGE, uDIG, OpenJump, GeoServer, Openlayers.

    Learning outcomes l.jpg

    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 l.jpg

    Main Topics in a nutshells

    • Spatial databases

    • Web mapping

    • Spatial Statistics

    • Geographical Ontologies.

    Main topics l.jpg

    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 l.jpg


    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

    References31 l.jpg


    Applied Spatial Data Analysis with R

    Bivand, Pebesma, Gómez-Rubio

    Lloyd: Spatial Data Analysis

    Theories of geographic concepts: ontological approaches to semantic integration By Marinos Kavouras, Margarita Kokla

    Assignment l.jpg


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

    Assignment33 l.jpg


    • 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

  • Login