Скачать презентацию Spatial Data and Geographic Spatial Databases Vania Bogorny www Скачать презентацию Spatial Data and Geographic Spatial Databases Vania Bogorny www

6eb8b01e1b6c4164a4aece955e4f6b7f.ppt

  • Количество слайдов: 31

Spatial Data and Geographic/Spatial Databases Vania Bogorny www. inf. ufsc. br/~vania@inf. ufsc. br Spatial Data and Geographic/Spatial Databases Vania Bogorny www. inf. ufsc. br/[email protected] ufsc. br

What is a Spatial/Geographic Database? Database that: • Stores spatial objects • Provides operations 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. 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 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 How are spatial data represented? • Points, Lines, Polygons, and complex geometries. 5

Spatial Representation: object · 0 -dimensional · representation: point · E. g. : school, 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 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 Shape Example: Geographic Data and Geographic Database Street Gid 1 2 Name Ijui Lavras Shape Multiline [(x 1, y 1), (x 2, y 2), . . ] Water. Resource Gid 1 2 3 Name Jacui Guaiba Uruguai Shape Multiline [(x 1, y 1), (x 2, y 2), . . ] Gas. Station Gid Name Vol. Diesel Vol. Gas Shape 1 BR 20000 85000 Point[(x 1, y 1)] 2 IPF 30000 95000 Point[(x 1, y 1)] 3 Esso 25000 120000 Point[(x 1, y 1)] 8

Example of geographic data Districts, Squares and Trees 9 Example of geographic data Districts, Squares and Trees 9

Example of geographic data Districts and Slums 10 Example of geographic data Districts and Slums 10

Example of geographic data Districts and Hospitals 11 Example of geographic data Districts and Hospitals 11

Overlay of Geographic Data Combination of some previous data 12 Overlay of Geographic Data Combination of some previous data 12

Maps as Layers 13 Maps as Layers 13

Spatial Relationships • Main characteristic which differs spatial data from non-spatial data 14 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 aduct intersects road R oad crosses viaduct rict contains soccer field Disjunção 15

Crosses relationship 16 Crosses relationship 16

Spatial Relationships What is the average distance between industrial repositories and water collecting points? 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) 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 Spatial Relationships Which is the closest put to my house? 19

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

Advantages of Spatial Databases Spatial querying using SQL – use simple SQL expressions to 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 Original Polygons Union Intersection 22

Examples of Spatial Operations Buffered Rivers 23 Examples of Spatial Operations Buffered Rivers 23

Spatial Databases • • ESRI Arc. SDE (on top of several different DBs) Oracle Spatial Databases • • ESRI Arc. SDE (on top of several different DBs) Oracle Spatial IBM DB 2 Spatial Extender Informix Spatial Data. Blade MS SQL Server (with ESRI SDE) Geomedia on MS Access Post. GIS / Postgre. SQL 24

Spatial Query Language • Spatial query language • Spatial data types, e. g. point, 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 Q 1: For all rivers in the relation “River”, which countries Spatial Query Example Q 1: 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 Q 2: Find the names of all countries which are neighbors Spatial Query Example Q 2: Find the names of all countries which are neighbors of the United States (USA) select c 1. cntry_name as USA_Neighbors from country c 1, country c 2 where touches(c 1. the_geom, c 2. the_geom)='True' and c 2. cntry_name='United States'; usa_neighbors ------- Canada (1 row) 27

Como criar um BDG no Postgre. SQL • 1) createdb -E SQL_ASCII -T template_postgis Como criar um BDG no Postgre. SQL • 1) createdb -E SQL_ASCII -T template_postgis Uusername nome. Da. Base ou criar via PGADMIN • 2) importação dos dados geográficos (usar UTF 8): • Transformação dos arquivos shape em. sql (Fazer isso para todos os arquivos. shp) – a) shp 2 pgsql -I arquivo. shp nome. Da. Tabela > tabela. sql • Importação para o banco de dados: – b) psql -Uusuario nome. Da. Base

Exercicios 1. Escreva uma consulta que mostre o nome dos bairros que contem hospitais 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/spatial. Queries. html) Spatial Queries using Postgres/Postgis Exercise I (http: //www. spatial. cs. umn. edu/Book/labs/vania/spatial. Queries. html) Spatial Queries using Postgres/Postgis This exercise demonstrates spatial queries from chapter 3 in Prof. Shekhar's book. using Postgresql/Post. GIS 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 Post. GIS using step 3 of the process. The. gif file is the graphical representation of the shapefiles, created in Arc. Explorer, 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 shp 2 pgsql [] [. ]

> sqlfilename. sql Example: shp 2 pgsql -c street. shp street_table my_db > street. sql 3. Import the sql files into Post. GIS 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. • 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 1. Try spatial queries. Following are example of spatial queries from chapter 3 of the Spatial Database book. 2. Query: List the names of the capital cities in the CITY table (script) 3. 4. 5. 6. 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) 7. 2. 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: 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 pg 2 shp. exe converter available with postgis

References • GUTING, R. H. An Introduction to Spatial Database Systems. The International Journal 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 Open. GIS abstract specification–Open. GIS features–Version 4. 1999 a. Available at . • OPEN GIS CONSORTIUM. Open. GIS simple features specification for SQL. 1999 b. Available at . • OPEN GIS CONSORTIUM. Feature Geometry. 2001. Available at . 31