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 are represented as green polygons Slums
are represenetd as yellow polygons Water bodies
are represented as blue lines
10
Example of geographic data
Districts, Squares and Trees
11
Example of geographic data
Districts and Slums
12
Example of geographic data
Districts and Hospitals
13
Overlay of Geographic Data

• Combination of some
• previous data

14
Maps as Layers
15
Spatial Relationships

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

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

• Which is the closest put to my house?

21
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
22
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

23
Examples of Spatial Operations
Original Polygons
Union
Intersection
24
Examples of Spatial Operations
Buffered Rivers
Rivers
25
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

26
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

27
Spatial Query Example Q1 For all rivers in the
relation River, which countries do they
pass? 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)
28
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)

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

30
Exercicios

• 1) Escreva uma consulta que conte as pracas do
  bairro Botafogo
• 2) Escreva uma consulta que retorne as favelas do
  bairro Tijuca mostre o resultado no mapa
• 3) Escreva uma consulta que retorne os rios que
  cruzam o bairro Barra da Tijuca
• 4) Escreva uma consulta que retorne as Favelas
  que contem escolas, mostrando-as no mapa

31
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)

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