Enterprisedb corporation
This presentation is the property of its rightful owner.
Sponsored Links
1 / 176

EnterpriseDB Corporation PowerPoint PPT Presentation


  • 53 Views
  • Uploaded on
  • Presentation posted in: General

EnterpriseDB Corporation. EnterpriseDB is the leading provider of enterprise-class products and services based on PostgreSQL, the world's most advanced open source database. Over 600 customers including: Sony, FTD, British Telecom, TDAmeritrade. Introduction to PostGIS.

Download Presentation

EnterpriseDB Corporation

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Enterprisedb corporation

EnterpriseDB Corporation

EnterpriseDB is the leading provider of enterprise-class products and services based on PostgreSQL, the world's most advanced open source database.

Over 600 customers including: Sony, FTD, British Telecom, TDAmeritrade


Enterprisedb corporation

Introduction to PostGIS

Installation – Tutorial - Exercises


Enterprisedb corporation

What is PostGIS?

  • Like Oracle Spatial, DB2 Spatial, and SQL Server Spatial, PostGIS adds spatial capabilities to an existing relational database, in this case, PostgreSQL.

  • It could be renamed to “PostgreSQL Spatial” as it functions in the same way as proprietary database extensions.


Enterprisedb corporation

What is PostGIS?

  • Adds support for geographic objects to the PostgreSQL object-relational database

  • PostgreSQL already has “geometric types” but native geometries are too limited for GIS data and analysis


Enterprisedb corporation

What is PostGIS?

  • PostGIS adds an indexing mechanism to allow queries with spatial restrictions or filters (“within this bounding box”) to return records very quickly from large tables.


Enterprisedb corporation

What is PostGIS?

  • PostGIS adds a “geometry” data type to the usual data types (ie. varchar, integer, date, etc.)

  • PostGIS adds spatial predicates and functions that use the geometry data type.

    • ST_Distance(geometry, geometry)

    • ST_Area(geometry)

    • ST_Intersects(geometry, geometry))


Enterprisedb corporation

PostGIS Overview / History

  • Open source

    • General Public License (GPL)

    • Open development and support

  • History

    • 2001: First release, Mapserver support

    • 2002: Improved functions, indexes

    • 2003: GEOS support, many functions

    • 2004: SFSQL conformance

    • 2005: Lightweight geometries

    • 2006: OpenGIS SFSQL compliance

    • 2007: SQL/MM, curves & performance

    • 2008-2010: Performance enhancements


Enterprisedb corporation

Why PostGIS?

  • Because databases are better than files

  • Unified Storage, Management, Access

    • SQL Everywhere

  • Transactional Integrity

    • Multiple Users, Multiple Edits


Enterprisedb corporation

PostGIS

PostGIS in the Spatial Stack

LAN

Internet

Mapserver

uDig

OpenIMF

GeoServer

GRASS

WebClient

QGIS

MapGuide

ArcGIS

OpenJUMP

uDig


Enterprisedb corporation

Who is using PostGIS?

  • Lots of people …

  • 1790 mailing list members

  • 14K visits/month10K visitors/month

  • 130 source code downloads per day

  • 970K Google search results

  • Great Google trends …


Enterprisedb corporation

Who is using PostGIS?

  • North Dakota State Water Commission in Bismark uses PostGIS as the cost-effective foundation for managing the use of state-wide water resources. PostGIS tied unobtrusively into their existing desktop systems and pre-empted a costly migration to an ArcIMS/ArcSDE system.


Enterprisedb corporation

Who is using PostGIS?

  • The Ministry of Sustainable Resource Management (British Columbia, Canada) uses PostGIS to store, manage and analyze their Digital Road Atlas, a large and complex road network database.


Enterprisedb corporation

Who is using PostGIS?

  • Institut Géographique National (IGN France) uses PostGIS to underpin a system containing more than 100 million spatial objects, including a 3D national database of roads, rails, hydrography, vegetation, buildings and administrative boundaries.


Enterprisedb corporation

Who is using PostGIS?

  • InfoTerra (United Kingdom) uses PostGIS to store >600 million features of topographic data. Sits at the back-end of their online DataStore. Notable load times of 12 hours for the entire data-suite ~14000 features per second. More notable savings of ~£1000000 per annum.


Enterprisedb corporation

Workshop Summary

  • Introduction to Spatial Concepts

  • Installation (PostgreSQL and PostGIS)

  • Creating and Loading Data

  • Creating Indexes

  • Spatial Operations and Predicates

  • Spatial SQL

  • System Configuration


Introduction to spatial concepts

Introduction to Spatial Concepts

  • A Geographical Information System (GIS) is any system used for capturing, storing, analyzing, managing, or presenting geospatial data.

  • Geometric Datatypes

  • Geometry Validity


2 1 geometric datatypes

2.1 Geometric Datatypes


Enterprisedb corporation

2.1 Geometric Datatypes

  • Each geometry has a Well-Known Text representation (WKT)

    • A geometry type

    • A comma-separated list of coordinate pairs


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    POINT ( 7 6 )


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    MULTIPOINT ( 1 4, 1 5, -0.5 5.5314 )


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    LINESTRING ( 1 3, 1 1, 3 0 )


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    MULTILINESTRING (( 2 1, 2 2, 3 4 ),

    ( 4 3, 3 2, 4 1, 3 3 ))


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    POLYGON (( 2 5, 3 8, 6 8, 5 4, 2 5 ),

    ( 3 6, 4 5, 5 7, 4 6, 3 6 ))


Enterprisedb corporation

2.1 Geometric Datatypes

  • Some examples are:

    MULTIPOLYGON ((( 6 2, 6 4, 8 4, 9 1, 6 2 ),

    ( 7 3, 8 2, 8 3, 7 3 )),

    (( 9 2, 8 5, 10 4, 10 3, 9 2 )))


Enterprisedb corporation

2.1 - Exercises

  • Time for some exercises!


Enterprisedb corporation

2.3 - Geometry Validity

  • PostGIS is compliant with the Open Geospatial Consortium’s (OGC) OpenGIS Specifications

  • Most functions require / assume geometries are valid and in many cases, simple.

    • Geometry validity really only applies for areal geometries

    • Geometry simplicity applies to point and linear geometies


Enterprisedb corporation

2.3 - Geometry Validity

  • A POINT is inherently simple as a 0-dimentional geometry object.

  • A MULTIPOINT is simple if no two POINTs are the same.


Enterprisedb corporation

2.3 - Geometry Validity

  • By definition, a LINESTRING is always valid

  • It is simple if it does not pass through the same point twice.

SIMPLE

NOT

SIMPLE

SIMPLE

NOT

SIMPLE


Enterprisedb corporation

2.3 - Geometry Validity

  • MULTILINESTRINGs are simple if all its elements are simple and they only intersect at boundary points.

NOT SIMPLE

SIMPLE

SIMPLE


Enterprisedb corporation

2.3 - Geometry Validity

  • By definition, a POLYGON is always simple.

  • It is valid if

    • The boundary is made up of simpleLINEARRINGs

    • boundary rings don’t cross

    • holes are completely within the outer ring and touch the outer ring at most one point.

    • it does not contain cutlines / spikes


Enterprisedb corporation

2.3 - Geometry Validity

  • These POLYGONs are …

valid

invalid

invalid

invalid

valid

invalid


Enterprisedb corporation

2.3 - Geometry Validity

  • By definition, a MULTIPOLYGON is valid iff

    • All elements are valid

    • Element interiors cannot intersect

    • Element boundaries can touch, but only at a finite number of POINTs.


Enterprisedb corporation

2.3 - Geometry Validity

  • These MULTIPOLYGONs are …

invalid

invalid

valid


Enterprisedb corporation

3.1 – PostgreSQL Installation

  • Windows Installer

    • PostgreSQL 8.4.4

      • Automatically installs itself as a Windows Service

    • PgAdmin III

    • Psql Interactive SQL Shell


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation

  • Directories created during installation:

    • \bin - Executables

    • \include - Include files for compilation

    • \lib - DLL shared library files

    • \share - Extensions


Enterprisedb corporation

3.1 – PostgreSQL Installation

  • Tools included with the install:

  • PgAdmin III

  • psql Command Line


Enterprisedb corporation

2.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation


Enterprisedb corporation

3.1 – PostgreSQL Installation

  • Under Linux

    http://www.postgresql.org/docs/8.4/static/installation.html

  • ./configure

  • gmake

  • su

  • gmake install

  • adduser postgres

  • mkdir /usr/local/pgsql/data

  • chown postgres /usr/local/pgsql/data

  • su - postgres

  • /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data

  • /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >logfile 2>&1 &

  • /usr/local/pgsql/bin/createdb dbname

  • /usr/local/pgsql/bin/psql dbname


Enterprisedb corporation

3.2 – PostGIS Installation

  • As of PostgreSQL 8.3, an Application Stack Builder is used to obtain the latest installers for desired modules.

  • Demonstrate Installation …


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation

  • The Stack Builder has finished downloading the PostGIS installer.

  • Demonstrate PostGIS Installation …


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation


Enterprisedb corporation

3.2 – PostGIS Installation

  • Under Linux

    http://postgis.refractions.net/docs/ch02.html

    tar xvfz postgis-1.5.1.tar.gz

    cd postgis-1.5.1

    ./configure

    make

    make install

    createdb yourdatabase

    createlang plpgsql yourdatabase

    psql -d yourdatabase -f postgis.sql

    psql -d yourdatabase -f postgis_comments.sql

    psql -d yourdatabase -f spatial_ref_sys.sq


Enterprisedb corporation

3.3 – Environment Setup

  • In order for PostgreSQL to be accessible from the command prompt

    • Create a PGHOME environment system variable

    • Add %PGHOME%\bin to the system path


Enterprisedb corporation

3.3 – Environment Setup


Enterprisedb corporation

3.3 – Environment Setup


Enterprisedb corporation

3.3 – Environment Setup


Enterprisedb corporation

3.4 – Create Spatial Database

  • From the command line

    createdb -T template_postgis dbname


Enterprisedb corporation

3.4 – Create Spatial Database

  • Launch pgAdminIII


Enterprisedb corporation

3.4 – Create Spatial Database

  • Create a new database

    • Select “template_postgis” as the template

  • Verify the installation

    • Check for the spatial system tables

      • spatial_ref_sys – stores unique coordinate references

      • geometry_columns – stores metadata for a spatial column


Enterprisedb corporation

3.4 – Create Spatial Database


Enterprisedb corporation

3.4 – Create Spatial Database


Enterprisedb corporation

3.5 –Spatially-Enable an Existing DB

  • From the command line

    psql -f "%PGHOME%\share\contrib\postgis-1.5\postgis.sql" dbname

    psql -f "%PGHOME%\share\contrib\postgis-1.5\spatial_ref_sys.sql" dbname


Enterprisedb corporation

3.5 –Spatially-Enable an Existing DB

  • Using a User Interface

    • Connect to your existing database

    • Open the “Execute Arbitrary SQL Queries” window

    • Load/run the PostGIS extension (postgis.sql)

    • Load/run the PostGIS spatial reference systems (spatial_ref_sys.sql)


Enterprisedb corporation

4.1 - Simple Spatial SQL

  • “Manually” create geometries

    CREATETABLE points

    (pt geometry, name varchar);

    INSERTINTO points VALUES

    ('POINT(0 0)', 'Origin');

    INSERTINTO points VALUES

    ('POINT(5 0)', 'X Axis');

    INSERTINTO points VALUES

    ('POINT(0 5)', 'Y Axis');

    SELECT name, ST_AsText(pt),

    ST_Distance(pt, 'POINT(5 5)')

    FROM points;


Enterprisedb corporation

4.1 - Simple Spatial SQL

name | st_astext | st_distance

--------+------------+------------------

Origin | POINT(0 0) | 7.07106781186548

X Axis | POINT(5 0) | 5

Y Axis | POINT(0 5) | 5

(3 rows)


Enterprisedb corporation

4.1.1 - PostGIS Reference

  • Function References:

    http://postgis.refractions.net/docs/ch07.html

    “Appendix A: Data Dictionaries”

    • (included in ‘PostGIS Workshop.doc’)

      Or online PostGIS Documentation

      http://postgis.refractions.net/docs


Enterprisedb corporation

4.1.2 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.2 - Loading Shape Files

  • Shape File (Misnomer! 3+ Files!)

    • .shp = geometry

    • .dbf = attributes (string, number, date)

    • .shx = utility index

  • PostGIS/PostgreSQL Table

    • Columns can be geometry

    • Columns can be attributes

  • One Shape File = One PostGIS Table


Enterprisedb corporation

4.2 - Loading Shape Files

  • shp2pgsql [opts] shapefile tablename

    • shp2pgsql –i –s 3005 –D bc_pubs.shp bc_pubs > bc_data.sql

  • Read in .shp file

  • Write out .sql file

  • Load .sql file into PostgreSQL

    • using psql

    • using PgAdmin


Enterprisedb corporation

4.2 - Loading Shape Files

  • As mentioned in the install process, to make life easier, include %PGHOME%\bin\ in your system path:

  • Run  cmd.exe

    • > cd %WORKSHOP%\data\

    • > dir *.shp

    • > shp2pgsql -s 3005 bc_pubs.shp public.bc_pubs > bc_data.sql


Enterprisedb corporation

4.2 - Loading Shape Files

  • > notepad bc_data.sql


Enterprisedb corporation

4.2 - Loading Shape Files

  • Load the data via psql

  • > psql –d postgis –U postgres –f bc_data.sql


Enterprisedb corporation

4.2 - Command Line Options


Enterprisedb corporation

4.2 - Exercises

  • Time for some exercises!


Enterprisedb corporation

bc_pubs100s of points


Enterprisedb corporation

bc_roads100s of thousands of lines


Enterprisedb corporation

bc_hospitals10s of points


Enterprisedb corporation

bc_municipality100s of polygons


Enterprisedb corporation

bc_habitat_areas100s of polygons


Enterprisedb corporation

4.3.1 – Indexes and Query Plans

  • Databases are fancy engines for speeding up random access to large chunks of data.

  • Query plans are the rules used by databases to convert a piece of SQL into a strategy for reading the data.


Enterprisedb corporation

4.3.1 – Indexes and Query Plans

  • In PostgreSQL, these plans can be viewed by prefacing your query with “EXPLAIN”.

  • To view the actual run time along side the estimated query plan, preface with “EXPLAIN ANALYZE”.


Enterprisedb corporation

4.3.1 – Indexes and Query Plans

  • In PgAdminIII, view a simple filtered query by pressing the “Explain” button


Enterprisedb corporation

4.3.1 – Indexes and Query Plans

CREATEINDEX bc_roads_name_idx ON bc_roads (name);

ANALYZE bc_roads;


Enterprisedb corporation

4.3.2 – When Query Plans Go Bad

  • As previously mentioned, a database attempts to minimize disk accesses.

  • Indexes can lower disk accesses for queries that only return a few rows, but can actually increase accesses for queries that return a large number of rows.


Enterprisedb corporation

4.3.2 – When Query Plans Go Bad

  • The database builds “plans” based on statistics about data distribution sampled from the tables

    • Always tries to be “selective”, to pull the fewest number of records necessary to move on to the next step

  • The database builds bad plans when it has bad statistics

  • Make sure your database has up-to-date statistics by running ANALYZE


Enterprisedb corporation

4.3.2 – When Query Plans Go Bad

  • It’s important to tune PostgreSQL’s configuration parameters so it can properly chose the best query plan for any given query.

    • IE. Is it faster to perform an index scan from a minimally selective query than it is to perform a sequential scan through a table since most of the data is in memory anyway?


Enterprisedb corporation

Same bounding box, different selectivity!


Enterprisedb corporation

4.3.3 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.4.1 – Creating Spatial Indexes

  • PostGIS implements R-Tree indexes on top of the GiST indexing system

  • Organizes data into nesting rectangles for fast searching


Enterprisedb corporation

4.4.1 – Creating Spatial Indexes

  • An example index creation command is

    CREATEINDEX bc_pubs_gidx ON bc_pubs

    USINGGIST (the_geom);


Enterprisedb corporation

4.4.2 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

  • Spatial index operator is “&&”

    • “Bounding Boxes Interact”

A&&B = TRUE

A&&B = FALSE


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

  • Bounding Boxes are not enough!

  • Two pass system required

    • Use bounding boxes to reduce candidates

    • Use real topological test to get final answer

A&&B = TRUE_ST_Intersects(A, B) = FALSE


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

ST_Intersects(A, B)

A && B AND _ST_Intersects(A, B)


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

A && B


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

A && B


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

_ST_Intersects(A,B)


Enterprisedb corporation

4.4.3 - Using Spatial Indexes

  • Index operations (&&) are built into common functions for automatic use, but you can use them separately if you like.

    • ST_Intersects(G1,G2)

      • G1 && G2 AND _ST_Intersects(G1,G2)

    • ST_Contains(G1,G2)

    • ST_Within(G1,G2)

    • ST_Touches(G1,G2)

    • ST_DWithin(G1,G2,D)

      • G1 && ST_Expand(G2,D) AND ST_Distance(G1,G2) > D


Enterprisedb corporation

4.4.4 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.5 - Spatial Analysis in SQL

  • A surprising number of traditional GIS analysis questions can be answered using a spatial database and SQL.

  • GIS analysis is generally about filtering spatial objects with conditions, and summarizing the results – and that is exactly what databases are very good at.


Enterprisedb corporation

4.5.1 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.6 – Data Integrity

  • PostGIS permits a user to store invalid or non-simple geometries in a spatial database, unlike Oracle, which require validity upon insertion.


Enterprisedb corporation

4.6 – Data Integrity

  • However, most spatial predicates or functions in PostGIS expect the data to conform to the OGC Simple Features for SQL standard

    • Polygon rings don’t cross other rings or self-intersect

    • Interior rings on polygons can touch exteriors, but only at a point

    • Multi-polygons are non-overlapping


Enterprisedb corporation

4.6 – Data Integrity

  • PostGIS offers ST_IsSimple() and ST_IsValid() functions, useful in determining if geometries conform to the specification.

    SELECT count(*)

    FROM bc_habitat_areas

    WHERENOTST_IsValid(the_geom);


Enterprisedb corporation

4.6.1 - Exercises

  • Time for some exercises!


Enterprisedb corporation

4.7 – Distance Queries

  • It’s easy to write inefficient queries for distances in PostGIS because it’s not always obvious how to use the index in a distance query.


Enterprisedb corporation

4.7 – Distance Queries

Question: How many wildlife habitats are within 20 kilometers of the municipality of Oliver?

First, find out where Oliver is located.

SELECT ST_AsText(ST_Centroid(the_geom))

FROM bc_municipalities

WHERE name = 'Oliver';

POINT(1470065.29710885 484600.794443135)


Enterprisedb corporation

4.7 – Distance Queries

Then, use that location to sum all habitats within 20km. The “obvious” way does not use any indexes.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_Distance( the_geom,ST_GeomFromText('POINT(1470065 484600)',3005) ) < 20000;


Enterprisedb corporation

4.7 – Distance Queries

The “optimized” way is to use ST_DWithin()which silently adds an index into the mix.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_DWithin( the_geom,ST_GeomFromText(

'POINT(1470065 484600)',3005),

20000 );


Enterprisedb corporation

4.7 – Distance Queries

Here’s the definition of ST_DWithin().

CREATEFUNCTION ST_DWithin(geometry, geometry, float8)RETURNS boolean AS' SELECT $1 && ST_Expand($2,$3) AND $2 && ST_Expand($1,$3) AND ST_Distance($1, $2) < $3 'LANGUAGE'SQL'IMMUTABLE;


Enterprisedb corporation

4.7.1 – Distance Not Buffer

For complete, here’s how you should never answer a distance query.

SELECT count(*) AS num_habitats

FROM bc_habitat_areas

WHERE

ST_Contains(

ST_Buffer(the_geom, 20000),

ST_GeomFromText('POINT(1470065 484600)',3005)

);


Enterprisedb corporation

5.1 – OGC Specifications


Enterprisedb corporation

5.1 – OGC Specifications

  • SRID(): Integer - Returns the Spatial Reference System ID for this geometric object. This will normally be a foreign key to an index of reference systems stored in either the same or some other datastore.

  • Is3D(): Integer - Returns 1 (TRUE) if this geometric object has z coordinate values.


Enterprisedb corporation

5.1 – OGC Specifications

  • Disjoint(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially disjoint” from anotherGeometry.

  • Within(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially within” from anotherGeometry.

  • Contains(geometry):Integer - Returns 1 (TRUE) if this geometric object is “spatially contain” from anotherGeometry.


Enterprisedb corporation

5.1 – OGC Specifications

  • Distance(geometry):Double - Returns the shortest distance between any two Points in the two geometric objects as calculated in the spatial reference system of this geometric object. Because the geometries are closed, it is possible to find a point on each geometric object involved, such that the distance between these 2 points is the returned distance between their geometric objects.

  • Intersection(geometry): Geometry – Returns the geometric object that represents the Point set intersection of this geometric object with another geometry.


5 2 de 9im the dimensionally extended nine intersection model

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Approach

make pair-wise tests of the intersections between the Interiors, Boundaries, and Exteriors of two geometries and to represent these relationships in an “intersection” matrix


5 2 de 9im the dimensionally extended nine intersection model1

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Possible values:

{T, F, *, 0, 1, 2}

Where:

T == {0,1,2}

F == empty set

* == don’t care

0 == dimensional 0 – point

1 == dimensional 1 – line

2 == dimensional 2 - area


5 2 de 9im the dimensionally extended nine intersection model2

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Boundary

the set of geometries of the next lower dimension

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)


5 2 de 9im the dimensionally extended nine intersection model3

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Interior

the points that are left when the boundary points are removed

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)


5 2 de 9im the dimensionally extended nine intersection model4

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

Geometry Topology

Exterior

consists of points not in the interior and boundary

Point

(dim-0)

Polygon

(dim-2)

Line

(dim-1)


5 2 de 9im the dimensionally extended nine intersection model5

5.2 - DE-9IM The Dimensionally Extended – Nine Intersection Model

(b)

(a)

2

1

2

1

0

1

2

1

2

ST_Relate(a, b) = ‘212101212’


Enterprisedb corporation

5.3 - Exercises

  • Time for some exercises!


Enterprisedb corporation

5.4 – Spatial Predicates

Crosses(geometry):Integer

Crosses relation applies to P/L, P/A, L/L and L/A situations.

Case aP, bL or Case aP, bA or Case aL, bA:

a.Crosses(b)

 (I(a) ∩ I(b) ≠∅)  (I(a) ∩ E(b) ≠∅)

 a.Relate(b, ‘T*T******’)

Case aL, bL:

a.Crosses(b)

 dim(I(a)∩I(b)) = 0

 a.Relate(b, ‘0********’);


Enterprisedb corporation

5.4 – Spatial Predicates

Overlaps(geometry):Integer

The Overlaps relation is defined for A/A, L/L and P/P situations.

Case aP, bP or Case aA, bA:

a.Overlaps(b)

 (I(a) ∩I(b)≠∅)  (I(a) ∩E(b)≠∅)  (E(a) ∩I(b)≠∅)

 a.Relate(b, ‘T*T***T**’)

Case aL, bL:

a.Overlaps(b)

 (dim(I(a) ∩I(b) = 1)  (I(a) ∩E(b)≠∅)  (E(a) ∩I(b)≠∅)

 a.Relate(b, ‘1*T***T**’)


Enterprisedb corporation

6.1 - Spatial Joins

  • Standard joins use a common key

    SELECT a.var1, b.var2

    FROM a, b

    WHERE a.id = b.id

  • Spatial joins are based on a spatial relationship

    SELECT a.var1, b.var2

    FROM a, b

    WHEREST_Intersects(a.geom, b.geom)


Enterprisedb corporation

6.1 - Spatial Joins

  • The previous distance query can be expressed using a single spatial join.

    SELECT count(*) AS num_habitats

    FROM

    bc_municipalities a,

    bc_habitat_areas b

    WHERE

    ST_DWithin(

    a.the_geom,

    b.the_geom,

    20000)

    AND

    a.name ILIKE 'Oliver%';


Enterprisedb corporation

6.1 - Spatial Joins

Question: Find all pubs located within 250 meters of a hospital


Enterprisedb corporation

6.1 - Spatial Joins

SELECT bc_hospitals.name, bc_pubs.name

FROM bc_hospitals, bc_pubs

WHEREST_DWithin(

bc_hospitals.the_geom, bc_pubs.the_geom, 250);

name | name

--------------------------------+-----------------------------------

British Columbia Cancer Agency | Holiday Inn Vancouver Centre

The Richmond Hospital | Executive Airport Plaza Hotel ...

St. Paul's Hospital | Holiday Inn Hotel & Suites ...

St. Paul's Hospital | Bosnman's Hotel

St. Paul's Hospital | Sheraton Vancouver Wall Centre ...

St. Paul's Hospital | Burrard Motor Inn

St. Paul's Hospital | Century Plaza Hotel and Spa

St. Paul's Hospital | The Fountainhead Pub

Nanaimo Regional General Hos... | Windward Pub

(9 rows)


Enterprisedb corporation

6.1 - Spatial Joins

Question: Determine the density of roads per municipality.


Enterprisedb corporation

6.1 - Spatial Joins

SELECT

m.name,

(sum(ST_Length(ST_Intersection(

m.the_geom, r.the_geom))) /

sum(ST_Area(m.the_geom))) AS road_density

FROM

bc_municipalities m,

bc_roads r

WHERE m.the_geom && r.the_geom

GROUPBY m.name

ORDERBY road_density DESC;

name | road_density

----------------------------+-----------------------

Silverton | 0.000246331953966186

Slocan | 0.000131565932828081

Montrose | 0.000118947787979122

New Denver | 0.000114169586372941

Clinton | 9.92704482763833e-005

. . . . . . .


Enterprisedb corporation

6.2 - Overlays

  • Table-on-table overlays are possible with the ST_Intersection() function

    • ST_Intersects(a,b) returns BOOLEAN

    • ST_Intersection(a,b) returns GEOMETRY

ST_Intersects() = TRUE

ST_Intersection() =


Enterprisedb corporation

6.2 - Overlays

Question: Create a new table of habitat areas clipped by the Chilliwack municipal boundary.


Enterprisedb corporation

6.2 - Overlays

CREATETABLE ch_habitat_areas AS

SELECT

ST_Intersection(h.the_geom, m.the_geom)

AS intersection_geom,

ST_Area(h.the_geom) AS va_area,

h.*,

m.name AS municipality_name

FROM

bc_habitat_areas h,

bc_municipalities m

WHEREST_Intersects(h.the_geom, m.the_geom)

AND m.name = 'Chilliwack';


Enterprisedb corporation

6.3 – Coordinate Projection

  • Every geometry in PostGIS has a “spatial referencing identifier” or “SRID” attached to it.

  • The SRID indicates the spatial reference system the geometry coordinates are in.


Enterprisedb corporation

6.3 – Coordinate Projection

  • View the SRID of geometries using the ST_SRID() function

    SELECTST_SRID(the_geom)

    FROM bc_roads LIMIT 1;

    3005

  • What’s 3005?

    SELECT srtext

    FROM spatial_ref_sys

    WHERE srid = 3005;

    PROJCS["NAD83 / BC Albers",…

  • Ah, it’s “BC Albers”


Enterprisedb corporation

6.3 – Coordinate Projection

  • What’s 3005 again?

    SELECT proj4text

    FROM spatial_ref_sys

    WHERE srid = 3005;

    "+proj=aea +lat_1=50 +lat_2=58.5 +lat_0=45 +lon_0=-126 +x_0=1000000 +y_0=0 +ellps=GRS80 +datum=NAD83 +units=m +no_defs“

  • PROJ4 is coordinate re-projection library used by PostGIS


Enterprisedb corporation

6.3 – Coordinate Projection

  • Coordinate Re-projection is done using the ST_Transform() function

    SELECT ST_AsEWKT(the_geom)

    FROM bc_roads

    LIMIT 1;

    "SRID=3005;MULTILINESTRING((

    1004687.04355194 594291.053764096,

    1004729.74799931 594258.821943696,

    1004808.0184134 594223.285878035,

    1004864.93630072 594204.422638658,

    1004900.50302171 594200.005856311

    ))"


Enterprisedb corporation

6.3 – Coordinate Projection

  • Coordinate Re-projection is done using the ST_Transform() function

    SELECT ST_AsEWKT(ST_Transform(the_geom,4326))

    FROM bc_roads

    LIMIT 1;

    "SRID=4326;MULTILINESTRING((

    -125.9341 50.3640700000001,

    -125.9335 50.36378,

    -125.9324 50.36346,

    -125.9316 50.36329,

    -125.9311 50.36325

    ))"


Enterprisedb corporation

6.4 – Extracting Spatial Data

  • One of the utility applications that accompany a typical PostGIS installation is the pgsql2shp command-line utility program that can extract a table, view, or any custom query that contains a geometry column into a shapefile.

    USAGE:

    pgsql2shp [<options>] <database> [<schema>.]<table>

    pgsql2shp [<options>] <database> <query>


Enterprisedb corporation

6.5 – Clustering

  • Clustering is a concept in PostgreSQL where one physically orders the rows in a table based on some index

  • CLUSTER [VERBOSE]<tablename> [USING indexname]

  • CLUSTER [VERBOSE]


Enterprisedb corporation

6.5 – Clustering


Enterprisedb corporation

6.5 – Clustering


Enterprisedb corporation

7 – PostgreSQL Optimization

  • PostgreSQL ships with conservative settings

    • Uses very little memory

    • Runs on very limited hardware

  • Database use cases play an important role in configuring certain parameters

    • A read-only webserver with 100s of connections vs. a production system with a few users but high write activity.


Enterprisedb corporation

7 – PostgreSQL Optimization

  • Disk access is slow, so higher performance can be gained by using more memory to cache data!

    • shared_buffers

    • effective_cache_size


Enterprisedb corporation

7 – PostgreSQL Optimization

  • Sorting is faster in memory

    • Increase work_mem

  • Disk clean-up is faster with more memory

    • Increase maintenance_work_mem

  • Allocated per connection

  • Also

    • Increase checkpoint_segments

    • Decrease random_page_cost


Enterprisedb corporation

8.1 – Basic Exercises

1. What is the perimeter of the municipality of Vancouver?

SELECTST_Perimeter(the_geom)

FROM bc_municipalities

WHERE name = 'Vancouver';

57321.7782018085


Enterprisedb corporation

8.1 – Basic Exercises

2. How many points were used to digitize the road named “Cartmell Rd”?

SELECTST_NPoints(the_geom)

FROM bc_roads

WHERE name = 'Cartmell Rd';

28


Enterprisedb corporation

8.1 – Basic Exercises

3. How many holes or interior rings does the habitat area with hab_id = 891 (“Marbled Murrelet”) have?

SELECTST_NumInteriorRings(the_geom)

FROM bc_habitat_areas

WHERE hab_id = 891;

5


Enterprisedb corporation

8.1 – Basic Exercises

4. What is the length in kilometers of all roads named ‘Main St’?

SELECTsum(ST_Length(the_geom)) / 1000

AS total_length

FROM bc_roads

WHERE name = 'Main St';

44.8489926296202


Enterprisedb corporation

8.1 – Basic Exercises

5. What is the total area of all municipalities in hectares?

SELECTsum(ST_Area(the_geom)) / 10000

AS total_length

FROM bc_municipalities;

1186482.22780945


Enterprisedb corporation

8.1 – Basic Exercises

6. What is the average road length (treat every entry in the bc_roads table as a separate road)?

SELECTavg(ST_Length(the_geom))

FROM bc_roads;

434.230644730819


Enterprisedb corporation

8.2 – Intermediate Exercises

1. How many roads are completely within the municipality of 'Hope‘?

SELECT count(*)

FROM bc_roads r,

bc_municipalities m

WHEREST_Within(r.the_geom, m.the_geom)

AND m.name = 'Hope';

280


Enterprisedb corporation

8.2 – Intermediate Exercises

2. What is the latitude of the most southerly hospital in the province?

(Hint: consider using a lat/long projection)

SELECT min(ST_Y(

ST_Transform(the_geom, 4269)

)) AS lat

FROM bc_hospitals;

48.4657953714625


Enterprisedb corporation

8.2 – Intermediate Exercises

3. The residents in the municipality of “Sicamous” should have a watchful eye out for what native wildlife animal?

SELECT b.name

FROM bc_municipalities a,

bc_habitat_areas b

WHERE a.name = 'Sicamous'

ANDST_Intersects(a.the_geom, b.the_geom);

Grizzly Bear


Enterprisedb corporation

8.2 – Intermediate Exercises

4a. According to the datasets, is there a hospital in “Sicamous”?

SELECT count(*)

FROM bc_municipalities a,

bc_hospitals b

WHERE a.name = 'Sicamous'

ANDST_Contains(a.the_geom, b.the_geom);

0


Enterprisedb corporation

8.2 – Intermediate Exercises

4b. Is there a pub?

SELECT count(*)

FROM bc_municipalities a,

bc_pubs b

WHERE a.name = 'Sicamous'

ANDST_Contains(a.the_geom, b.the_geom);

1


Enterprisedb corporation

8.2 – Intermediate Exercises

5. Extract the bc_municipalities polygonal table to a shapefile, except add a new attribute called “perimeter” populated accordingly.

C:\> pgsql2shp -f municipalities.shp -u postgres postgis "SELECT *, ST_Perimeter (the_geom) AS perimeter FROM bc_municipalities"


Enterprisedb corporation

8.3 – Advanced Exercises

1. What is the name of the closest hospital from Sicamous and how far away is it?

SELECT a.name, b.name,

ST_Distance(a.the_geom, b.the_geom)

FROM bc_municipalities a,

bc_hospitals b

WHERE a.name = 'Sicamous'

ORDER BY ST_Distance ASC

LIMIT 1;

name | name | st_distance

---------+-------------------------------+------------

Sicamous | Shuswap Lake General Hospital | 22943.88318


Enterprisedb corporation

3.3 – Advanced Exercises

2. What are the names of the pubs located within 100 meters from “Granville St”, located in downtown Vancouver?

SELECTDISTINCT c.name

FROM bc_municipalities a,

bc_roads b,

bc_pubs c

WHERE a.name = 'Vancouver'

AND b.name = 'Granville St'

AND ST_Contains(a.the_geom, b.the_geom)

AND ST_DWithin(b.the_geom, c.the_geom, 100)

ORDER BY c.name;


Enterprisedb corporation

8.3 – Advanced Exercises

2. What are the names of the pubs located within 100 meters from “Granville St”, located in downtown Vancouver?

name

----------------------

Cecil Hotel

Dufferin Hotel

Fraser Arms Hotel

Howard Johnson Hotel

St. Helen's Hotel

St. Regis Hotel

The Lennox Pub

Yale Hotel

(8 rows)


Enterprisedb corporation

8.3 – Advanced Exercises

3. What is the total length (in kilometers) of roads named “Douglas St” in Victoria?

(Hint: sum the intersected length)

SELECT sum(ST_Length(

ST_Intersection(r.the_geom, m.the_geom)

)) / 1000 AS sum

FROM bc_roads r,

bc_municipalities m

WHERE r.name = 'Douglas St'

AND m.name = 'Victoria'

ANDST_Intersects(r.the_geom, m.the_geom);

4.47269806771667


Enterprisedb corporation

8.3 – Advanced Exercises

4. What road would you travel on in BC if you wanted to sight-see BC’s native “Spotted Owl”?

(Hint: try to find the name of the road with the longest intersecting length)

SELECT a.name, sum(ST_Length(

ST_Intersection(a.the_geom, b.the_geom)))

FROM bc_roads a, bc_habitat_areas b

WHERE b.name = 'Spotted Owl'

ANDST_Intersects(a.the_geom, b.the_geom)

GROUPBY a.name

ORDERBY sum DESC;


Enterprisedb corporation

8.3 – Advanced Exercises

4. What road would you travel on in BC if you wanted to sight-see BC’s native “Spotted Owl”?

(Hint: try to find the name of the road with the longest intersecting length)

name | sum

-----------------------+------------------

Pemberton-Lillooet Rd | 25798.0268482952

Chilliwack Lake Rd | 4937.85768862766

Highway 1 | 4401.56275978918

W Paulsen Rd | 1411.2595495739

E Paulsen Rd | 1226.83009075469

Z005 | 277.194003115705

...


Enterprisedb corporation

8.3 – Advanced Exercises

5. In the municipality of Kelowna, are the road segments named “Hillaby Ave” disjoint or continuous?

(Hint: consider merging the lines together)

SELECTST_NumGeometries(

ST_LineMerge(ST_Union(the_geom)))

FROM bc_roads

WHERE name = 'Hillaby Ave';

st_numgeometries

------------------

2


Enterprisedb corporation

Thank You …

  • Kevin [email protected]

  • PostGIShttp://www.postgis.org


  • Login