Spatial Data and Geographic/Spatial Databases

1
Spatial Data and Geographic/Spatial Databases

• Vania Bogorny
• www.inf.ufsc.br/vania
• vania_at_inf.ufsc.br

2
What is a Spatial/Geographic Database?

• Database that
• Stores spatial objects
• Provides operations to manipulate spatial objects
• Manipulates spatial objects just like other
  objects in the database

3
What is Spatial/Geographic data?

• Data which describes a location or a shapee.g.
  House, Hospital, Road, River, Forests,
  Parks, Soil
• Is something that describes objects or phenomena
  that happen on the Earth and that have
  associated a geographic position

4
What is Spatial/Geographic data?

• Three main characteristics describe a geographic
  object
• Non-spatial attributes (what) describe either
  quantitatively or qualitatively a geographic
  entity. This data may be treated by non-spatial
  databases
• Spatial attribute (where) refers to the location
  and the representation of the geographic object,
  considering geometry and coordinate system. This
  aspect requires a specific data type not
  available in conventional DBMS
• Spatial relationships (how) neighbourhood
  relationships (e.g. topology, distance). Requires
  special operations that are not available in
  conventional DBMS

5
How are spatial data represented?

• Points, Lines, Polygons, and complex geometries.
  

6
Spatial Representation object

• 0-dimensional
• representation point
• E.g. school, hospital,
• Uni-dimensional
• representation line
• E.g. river, road
• Bi-dimensional
• representation polygon
• E.g. state, city
• Tri-dimensional
• representation surface
• E.g.building

7
Spatial Representation field
Irregular points (e.g. temperature)
Regular Points
Isoline (e.g. relief)
Grid
Adjacent polygons (e.g. soil)
Triangual Network(e.g. )
8
Example Geographic Data and Geographic Database
Street Gid Name Shape 1 Ijui Multiline
(x1,y1),(x2,y2),.. 2 Lavras Multiline
(x1,y1),(x2,y2),.. WaterResource Gid Name Shape
1 Jacui Multiline (x1,y1),(x2,y2),.. 2 Guaiba M
ultiline (x1,y1),(x2,y2),.. 3 Uruguai Multiline
(x1,y1),(x2,y2),.. GasStation Gid Name
VolDiesel VolGas Shape 1 BR
20000 85000 Point(x1,y1) 2
IPF 30000 95000
Point(x1,y1) 3 Esso 25000
120000 Point(x1,y1)
9
Example of geographic data
Districts, Squares and Trees
10
Example of geographic data
Districts and Slums
11
Example of geographic data
Districts and Hospitals
12
Overlay of Geographic Data

• Combination of some
• previous data

13
Maps as Layers
14
Spatial Relationships

• Main characteristic which differs spatial data
  from non-spatial data

15
Viaduct intersects road OR Road crosses viaduct
District contains soccer field
Disjunção
16
Crosses relationship
17
Spatial Relationships
What is the average distance between industrial
repositories and water collecting points?
18
Spatial Relationships
How distant are water bodies (blue line) from
industrial repository (pink dot) ?
19
Spatial Relationships

• Which is the closest put to my house?

20
Main Spatial Relationships
A B
A B
B
B
A B
A B
A
A
overlaps
overlaps
disjoint
disjoint
touches
A B
A B
Topological
B
A
A
A B
A B
A
equals
A
equals
B
B
inside
contains
crosses
inside
contains
crosses
B
north
A
B
north
A
B
B
Order
A
A
d
d
Distance
B
B
C
C
C
C
C
southeast
A
C
southeast
A
21
Advantages of Spatial Databases

• Spatial querying using SQL
• use simple SQL expressions to determine spatial
  relationships
• distance
• order
• topology
• use simple SQL expressions to perform spatial
  operations
• area
• length
• intersection
• union
• buffer

22
Examples of Spatial Operations
Original Polygons
Union
Intersection
23
Examples of Spatial Operations
Buffered Rivers
Rivers
24
Spatial Databases

• ESRI ArcSDE (on top of several different DBs)
• Oracle Spatial
• IBM DB2 Spatial Extender
• Informix Spatial DataBlade
• MS SQL Server (with ESRI SDE)
• Geomedia on MS Access
• PostGIS / PostgreSQL

25
Spatial Query Language

• Spatial query language
• Spatial data types, e.g. point, linestring,
  polygon,
• Spatial operations, e.g. overlap, distance,
  nearest neighbor,
• SELECT S.name
• FROM States S
• WHERE area(s.the_geom) gt 300
• Standards
• OGIS is a standard for spatial data types and
  operators

26
Spatial Query Example Q1 For all rivers in the
relation River, which countries do they
cross? select r.name,c.cntry_name from river
r, country c where crosses (r.the_geom,c.the_geom
) 'True'           name           
cntry_name   -----------------------------------
------------- Pembina            
United States Pembina            
Canada Rainy                 
United States Rainy                 
Canada Souris                
United States Souris                 
Canada Red River of the North United
States Red River of the North Canada(8 rows)
27
Spatial Query Example

• Q2 Find the names of all countries which are
  neighbors of the United States (USA)
• select c1.cntry_name as USA_Neighbors
• from country c1, country c2
• where touches(c1.the_geom,c2.the_geom)'True'
  and  c2.cntry_name'United States'
•  usa_neighbors --------------- Canada(1 row)

28
Como criar um BDG no PostgreSQL

• 1) createdb -E SQL_ASCII -T template_postgis
  -Uusername nomeDaBase ou criar via PGADMIN
• 2) importação dos dados geográficos (usar UTF8)
• Transformação dos arquivos shape em .sql (Fazer
  isso para todos os arquivos .shp)
• a) shp2pgsql -I arquivo.shp nomeDaTabela gt
  tabela.sql
• Importação para o banco de dados
• b) psql -Uusuario nomeDaBase lttabela.sql

29
Exercicios

1. Escreva uma consulta que mostre o nome dos
   bairros que contem hospitais
2. Escreva uma consulta que faca um buffer de 100
   metros nos hospitais. Mostre no mapa os hospitais
   e os hospitais com buffer
3. Escreva uma consulta que retorne o nome das
   vilas que contem hospitais e postos de saude em
   um entorno de 1000 metros.
4. Escreva uma consulta que retorne o nome dos
   bairros (atributo do ponto de coleta) que contem
   pontos de coleta de agua localizados a menos de
   500 metros de postos de combustivel ou pocos de
   residuos industriais
5. Escreva uma consulta que retorne as vilas que
   contem escolas, mostrando-as no mapa

30
Exercise I (http//www.spatial.cs.umn.edu/Book/lab
s/vania/spatialQueries.html)

• Spatial Queries using Postgres/Postgis
• This exercise demonstrates spatial queries from
  chapter 3 in Prof. Shekhar's book. using
  Postgresql/PostGIS SDBMS and a spatial dataset
  downloaded from ESRI's web-site.
• It provides a multi-step process to download
  spatial datasets, convert those to sql, load into
  postgres/postgis SDBMS, and run queries.
• Note that one may skip the first two steps for a
  quick start using the following sql files
• Cities (city.sql, city.gif)
• Countries(country.sql, country.sql)
• Rivers (river.sql, river.gif)
• Water (water.sql, water.gif)
• Streets (street.sql. street.gif)
• Each sample .sql file is the converted shapefile
  ready to import into PostGIS using step 3 of the
  process. The .gif file is the graphical
  representation of the shapefiles, created in
  ArcExplorer, which is a free software for
  geographic data visualization. Complete
  Multi-step Process
• Download suitable spatial datasets in shapefile
  format from ESRI's web-site http//www.esri.com/
  data/download/basemap/index.html
• Convert shapefiles into sql files shp2pgsql
  ltoptionsgt ltshapefilegt ltschemagt.lttablegt gt
  sqlfilename.sql Example shp2pgsql -c 
  street.shp  street_table my_db gt street.sql
• Import the sql files into PostGIS psql
  options... dbname username Example psql -d
  my_db -f country.sql
• After import the data login into Postgresql pgsql
  my_db Example (sample session output)
• List all tables. Example sample session.
• You may look at the schema and contents of
  various table using other postgres commands.
  Example Query List all cities and the country
  they belong to in the CITY table Example Postgis
  expression and output script
• Try spatial queries. Following are example of
  spatial queries from chapter 3 of the Spatial
  Database book.
• Query List the names of the capital cities in
  the CITY table (script)
• Query Find the names of all countries which are
  neighbors of the United States (USA) in the
  Country Table (script)

31
References

• GUTING, R. H. An Introduction to Spatial Database
  Systems. The International Journal on Very Large
  Data Bases, S.l., v.3, n.4, p. 357 399, Oct.
  1994.
• RIGAUX, P. SCHOLL, M. VOISARD, A. Spatial
  Databases With Application to GIS. San
  Francisco Morgan Kaufmann, 2002.
• SHEKHAR, S., CHAWLA, S. Spatial databases a
  tour. Upper Saddle River, NJ Prentice Hall,
  2003.
• OPEN GIS CONSORTIUM. Topic 5, the OpenGIS
  abstract specificationOpenGIS featuresVersion
  4. 1999a. Available at lthttp//www.OpenGIS.org/tec
  hno/specs.htmgt.
• OPEN GIS CONSORTIUM. OpenGIS simple features
  specification for SQL. 1999b. Available at
  lthttp//www.opengeospatial.org/docs/99-054.pdfgt.
• OPEN GIS CONSORTIUM. Feature Geometry. 2001.
  Available at lthttp//www.opengeospatial.org/specsgt
  .