Building a Spatial Database in PostgreSQL - WebValley

manlybluegooseΔιαχείριση Δεδομένων

27 Νοε 2012 (πριν από 4 χρόνια και 10 μήνες)

307 εμφανίσεις

Building a Spatial Database
in PostgreSQL
Claudia&Shamar
dolci@fbk.eu
droghetti@fbk.eu
source:
http://www.slideshare.net/mleslie/intro-to-postgis
http://www.slideshare.net/mleslie/introduction-to-postgis
Introduction (1)

PostGIS
is a spatial extension for PostgreSQL

adds support for geographic objects to PostgreSQL and
enables PostgreSQL server to be used as a backend
spatial database for GIS spatial operations and analysis
simply mean running a (spatial) SQL query in the
database

PostGIS
aims to be an “OpenGIS Simple Features for
SQL” compliant spatial database
http://www.postgresql.org/docs/8.4/interactive/index.html

Data from a PostgreSQL/PostGIS database can be
used as data source for spatial server software like
MapServer and GeoServer

PostGIS is licensed under the GNU GPL and
operated as a Free Software Project

PostGIS is developed by Refractions Research Inc, a
GIS and database consulting company in Victoria,
British Columbia, Canada
Introduction (2)

PostGIS
is implemented compliant to the OGC
Simple Feature Specifications for SQL standard

The OGC specification defines operations and
the SQL schema to insert, query, manipulate
and delete spatial objects

The coordinates of the spatial objects are
stored in Feature Tables

One Feature Table can contain one type of
geometry (point, line, polygon, multiple of
each and geometry collection)
Introduction (3)
Installation - Requirements

Proj4 –
library for cartographic projection routines
PROJ4 is a:

stand alone projection utility “proj”

libraries for more than 2500 projections (e.g. EPSG list)

GEOS – Geometry Engine – Open Source


provides spatial predicate functions, spatial operators and
topological functions

PostgreSQL – helpful little application
What is a Spatial
Database?
Database that:

Stores spatial objects

Manipulates spatial objects just like
other objects in the database
What is Spatial
data?
Data which describes either location
or shape

What is Spatial
data?

In the abstract, reductionist view
of the computer, these entities are
represented as
Points
,
Lines
, and
Polygons
.

Spatial Data Types
Point
a single coordinate of two to three dimensions
Examples:
XXX
Spatial Data Types
Linestring
a set of two or more coordinates
linear interpretation of path between coordinates
Linearring
a linestring with three or more coordinates
the start and end points are the same
Examples:
XXX
Spatial Data Types
Polygon
a set of one or more linearrings
one ring defines the exterior boundary
remainder defines the holes in the polygon
Examples:
XXX
Creating a PostGIS database
Create database:

createdb
<dbname>
Load PL/pgsql language for PostGIS:

createlang plpgsql
<dbname>
Load PostGIS and object definitions:

psql -d
<dbname>
-f postgis.sql
CreateTable spatial_ref_sys (coordinate system
codes):

psql -d
<dbname>
-f spatial_ref_sys.sql
Exercise 1
In PostGIS:

Point Table creation

Import GPS data

Geometry
Ex2: PostGIS Shape Loader
Create SQL input files:
shp2pgsql –s
<epsgcode> <ShapeFileName>
<TableName> <dbName>
>
<filename>
ammcom_popo
SQL Queries - Exercises
THEMATIC

Attributes
SPATIAL

Length

Area

Distance
Spatial queries
Spatial querying using SQL


use simple SQL expressions to determine spatial
relationships

distance

adjacency

containment


use simple SQL expressions to perform spatial
operations

area

length

intersection

union

buffer
Spatial Operators

ST_Contains(geomA, geomB)

ST_ContainsProperly(geomA, geomB)

ST_Covers(geomA, geomB)

ST_CoveredBy(geomA, geomB)

ST_Crosses(geomA, geomB)

ST_Disjoint(geomA, geomB)

ST_Intersects(geomA, geomB)

ST_Overlaps(geomA, geomB)

ST_Touches(geomA, geomB)

ST_Within(geomA, geomB)
Ex3: spatial queries
1)

Calcolare popolazione totale per
comprensorio e superficie del comprensorio
2)

Trovare i comuni confinanti con il comune di
Trento
3)

Trovare il fiume più lungo nella provincia di
Trento
The end