Spatial Data and Geographic/Spatial
Databases
Vania Bogorny
www.inf.ufsc.br/~vania
[email protected]
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
2
What is Spatial/Geographic data?
• Data which describes a location or a
shape
e.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
3
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 nonspatial 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;
4
How are spatial data represented?
• Points, Lines, Polygons, and complex
geometries.
5
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
6
Spatial Representation: field
Irregular points (e.g. temperature)
Isoline (e.g. relief)
Regular Points
Grid
célula
Adjacent polygons (e.g. soil)
Triangual Network(e.g. )
7
Example: Geographic Data and Geographic Database
Street
Gid
1
2
Name
Ijui
Lavras
WaterResource
Gid
Name
1
Jacui
2
Guaiba
3
Uruguai
GasStation
Gid Name
1
BR
2
IPF
3
Esso
Shape
Multiline [(x1,y1),(x2,y2),..]
Multiline [(x1,y1),(x2,y2),..]
Shape
Multiline [(x1,y1),(x2,y2),..]
Multiline [(x1,y1),(x2,y2),..]
Multiline [(x1,y1),(x2,y2),..]
VolDiesel VolGas
20000
85000
30000
95000
25000
120000
Shape
Point[(x1,y1)]
Point[(x1,y1)]
Point[(x1,y1)]
8
Example of geographic data
Districts, Squares and
Trees
9
Example of geographic data
Districts and Slums
10
Example of geographic data
Districts and
Hospitals
11
Overlay of Geographic Data
Combination of some
previous data
12
Maps as Layers
13
Spatial Relationships
• Main characteristic which differs spatial
data from non-spatial data
14
aduct intersects road
R
oad crosses viaduct
rict contains soccer field
Disjunção
15
Crosses relationship
16
Spatial Relationships
What is the average distance
between industrial repositories
and water collecting points?
17
Spatial Relationships
How distant are water bodies (blue
line) from industrial repository
(pink dot) ?
18
Spatial Relationships
Which is the closest put to my house?
19
Main Spatial Relationships
A
B
A
disjoint
Topological
B
touches
B
A
A
B
overlaps
A
B
contains
inside
A
B
A
B
equals
crosses
B north A
B
Distance
B
Order
d
C
A
C
C southeast A
20
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
21
Examples of Spatial Operations
Original Polygons
Union
Intersection
22
Examples of Spatial Operations
Buffered Rivers
Rivers
23
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
24
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) > 300
• Standards
• OGIS is a standard for spatial data types and operators
25
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)
26
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)
27
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 > tabela.sql
•Importação para o banco de dados:
– b) psql -Uusuario nomeDaBase <tabela.sql
28
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
29
Exercise I
(http://www.spatial.cs.umn.edu/Book/labs/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
1.
Download suitable spatial datasets in shapefile format from ESRI's web-site : http://www.esri.com/data/download/basemap/index.html
2.
Convert shapefiles into sql files shp2pgsql [<options>] <shapefile> [<schema>.]<table> > sqlfilename.sql Example: shp2pgsql c street.shp street_table my_db > street.sql
3.
Import the sql files into PostGIS psql [options]... [dbname [username]] Example: psql -d my_db -f country.sql
4.
After import the data login into Postgresql pgsql my_db Example: (sample session output)
5.
List all tables. Example: sample session.
6.
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
7.
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)
8.
•
•
Query: Find the names of all countries which are neighbors of the United States (USA) in the Country Table (script)
Query: For all the rivers listed in the River table, find the countries through which they pass (script)
•
•
Query: Which city listed in the City table is closest to each river listed in the River table? (script
Query: List the name, population, and area of each country listed in the Country table (script)
•
Query: List the length of the rivers in each of the countries they pass through (script)
30
One may consider taking the results of a query back to Arc Explorer for visualization using pg2shp.exe converter available with postgis
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 specification–
OpenGIS features–Version 4. 1999a. Available at
<http://www.OpenGIS.org/techno/specs.htm>.
•
OPEN GIS CONSORTIUM. OpenGIS simple features specification for SQL.
1999b. Available at <http://www.opengeospatial.org/docs/99-054.pdf>.
•
OPEN GIS CONSORTIUM. Feature Geometry. 2001. Available at
<http://www.opengeospatial.org/specs>.
31