Spatial Databases by Open Standards and Software 6.

(1)

Spatial Databases by Open Standards and Software 6.

Complex queries in PostGIS

Gábor Nagy

(2)

Created by XMLmind XSL-FO Converter.

Spatial Databases by Open Standards and Software 6.: Complex queries in PostGIS

Gábor Nagy Lector: Zoltán Siki

This module was created within TÁMOP - 4.1.2-08/1/A-2009-0027 "Tananyagfejlesztéssel a GEO-ért"

("Educational material development for GEO") project. The project was funded by the European Union and the Hungarian Government to the amount of HUF 44,706,488.

v 1.0

Publication date 2010

Copyright © 2010 University of West Hungary Faculty of Geoinformatics

Abstract

Advanced features and comlex queries in PostGIS

The right to this intellectual property is protected by the 1999/LXXVI copyright law. Any unauthorized use of this material is prohibited. No part of this product may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage and retrieval system without express written permission from the author/publisher.

(3)

6. Complex queries in PostGIS ... 1

1. 6.1 Introduction ... 1

2. 6.2 Joining tables by geospatial conditions ... 1

3. 6.3 Constraints in geometry columns ... 2

3.1. 6.3.1 Default constraints ... 2

3.2. 6.3.2 User defined constraints ... 2

4. 6.4 Sample queries ... 2

4.1. 6.4.1 Sample datasets ... 2

4.2. 6.4.2 Queries ... 2

4.3. 6.4.3 Modifying the data structure ... 7

(4)

(5)

Chapter 6. Complex queries in PostGIS

1. 6.1 Introduction

This module contains examples for complex queries and advanced database features of PostGIS.

Many query examples can be found at the end of the module. This queries use a data set with Hungarian settlements and geocaches.

2. 6.2 Joining tables by geospatial conditions

Two or more tables can be joined by normal attributes:

SELECT invent.tool_name, invent.price, empl.name FROM invent JOIN empl ON invent.empl_id=empl.id;

or an equivalent solution:

SELECT invent.tool_name, invent.price, empl.name FROM invent, empl

WHERE invent.empl_id=empl.id;

These queries make row-pairs from the source tables, where the given logical expression returns true value. A lot of potential row-pairs may exist, the product of the number of rows in the two tables. If the joining columns are indexed, the database server can produce the row-pairs more quickly, the query will be faster.

We can join two tables by a spatial condition:

SELECT district.name, poi.name

FROM district JOIN poi ON contains(district.geom, poi.geom);

or the condition may be in the

WHERE

statement:

SELECT district.name, poi.name FROM district, poi

WHERE contains(district.geom, poi.geom)

This query is slow, because the database server has to check each pairing that may be very lengthy. For example if 1000 districts and 50000 poi exist, the database server will check 50 million district-poi pairs by the contains function.

We can make it faster by the pre-screening operators, which check the bounding box of the objects. These operators use the GIST indexes, accordingly return fast the row-pairs. Another condition joined by an

AND

operator can specify the strict spatial relationship.

SELECT district.name, poi.name FROM district JOIN poi

ON district.geom ~ poi. geom AND contains(district.geom, poi.geom);

or the equivalent solution:

SELECT district.name, poi.name FROM district, poi

WHERE district.geom ~ poi.geom AND contains(district.geom, poi.geom)

The

~

operator returns true, if the bounding box of the left side geometry contains the bounding box of the right side geometry. This is a necessary but not sufficient condition for the contains condition. The database server checks the spatial relationship by the contains function only where the condition for the bounding boxes is true.

The row-pairs will be created faster (even with a great number of lines), if the geometry columns are indexed.

(6)

Complex queries in PostGIS

2 3. 6.3 Constraints in geometry columns

The tables can contain constraints related to the geometry columns.

3.1. 6.3.1 Default constraints

The AddGeometryColumns function defines three constraints for the created geometry column:

• check the dimension of the geometry

• check the type of the geometry

• check the SRID of the geometry

These constraints prevent storing geometries in this column with different types, dimensions or spatial reference systems.

3.2. 6.3.2 User defined constraints

The users of the database (if he has the necessary privileges) can add more constraints related to the geometry columns.

For example the next command defines a constraint that prevents creating larger polygons than 10000 square meters:

ALTER TABLE buildings ADD CHECK (area(geom)<=10000);

If there are any rows in the table, which have larger area than 10000 square meters, when this command is executed, the command will be rejected.

4. 6.4 Sample queries

4.1. 6.4.1 Sample datasets

The

settlements

table contains the identification number (

id

), the name (

name

), the statistical code (

stat_code

), the identification number of the county (

county_id

) and 2D MultiPolygon geometry (

geom

) of Hungarian settlements.

The

counties

table contains the identification number (

county_id

) and the name (

name

) of Hungarian counties.

The

geocaches

table contains the identification number (

id

), the name (

name

), the short name (

short_name

) and 3D Point geometry (

geom

) of geocaches from the http://www.geocaching.hu/ site.

All the geometry columns use the Hungarian projection system. (Hungarian abbrevatabbreviation is EOV, SRID is 23700)

4.2. 6.4.2 Queries

The 10 largest settlements of Hungary:

gisdb=# SELECT name, (Area(geom)/1E6)::numeric(10,2) gisdb-# FROM settlements

gisdb-# ORDER BY 2 DESC LIMIT 10;

(7)

Complex queries in PostGIS

Kecskemét | 298.58 Mezötúr | 296.85 Hortobágy | 295.51 Szeged | 285.85 (10 rows)

The name of county, the name of settlement and the area of settlement in square kilometres, ordered by the name of county and settlement:

gisdb=# SELECT counties.name, settlements.name,

gisdb-# (Area(settlements.geom)/1E6)::numeric(10,2) AS area gisdb-# FROM counties JOIN settlements USING(county_id)

gisdb-# ORDER BY 1, 2;

The area of counties:

gisdb=# SELECT counties.name,

gisdb-#(Sum(Area(settlements.geom))/1E6)::numeric(10,2) AS area gisdb-# FROM counties JOIN settlements USING(county_id)

gisdb-# GROUP BY 1 gisdb-# ORDER BY 1;

The geocaches, and the settlements, where the geocache is located:

gisdb=# SELECT geocaches.short_name, geocaches.name, settlements.name gisdb-# FROM settlements JOIN geocaches

(8)

Complex queries in PostGIS

4

gisdb-# ON (settlements.geom ~ geocaches.geom AND

gisdb(# contains(settlements.geom, geocaches.geom));

short_name | name | name

---+---+--- ZICH | Zichy kápolna (-C) | Lórév

Geocaches and settlements with foreign geocaches (geocaches that is not contained by any Hungarian settlements):

gisdb=# SELECT geocaches.short_name, geocaches.name, settlements.name gisdb-# FROM settlements RIGHT JOIN geocaches

---+---+--- VASF | Vasfüggöny emlékhely - Hegykő | Hidegség

ZIM | Ezredévi emlékmű - Zimony |

BZD | Budavidék Zöldút - déli kör | Herceghalom FAJ | A Fáy-kastély Fájban | Fáj

SZMH | Szent Mihály-hegy | Örtilos ARAD | Az aradi vértanúk emlékezete |

PIBA | Pista Bácsi | Szögliget ZST | Alsó-hegyi Zsombolyos tanösvény |

KRIV | Kriván (Magas-Tátra) | RAKI | Rakitnica-kanyon (BIH) | and other more 2000 rows...

Geocaches and settlements including each settlement (the result contains settlements that do not have any geocache either):

gisdb=# SELECT geocaches.short_name, geocaches.name, settlements.name gisdb-# FROM settlements LEFT JOIN geocaches

---+---+--- | | Hernádbüd

| | Szatta ZICH | Zichy kápolna (-C) | Lórév

BSZL | Búcsúszentlászló | Bucsuszentlászló | | Nagymizdó

(9)

Complex queries in PostGIS

| | Csonkamindszent | | Katafa

The number of geocaches in the settlements:

gisdb=# SELECT settlements.name, Count(*) gisdb-# FROM settlements JOIN geocaches

gisdb-# ON (settlements.geom ~ geocaches.geom AND gisdb(# contains(settlements.geom, geocaches.geom)) gisdb-# GROUP BY 1;

name | count ---+---

Nyírbéltek | 1

Szentgotthárd | 2

Nemesvámos | 1

Ráckeve | 3

Kölked | 1

Vizsoly | 1

Nyergesújfalu | 3

Etyek | 2

Siklós | 6

Cserhátszentiván | 1

Écs | 1

Kisszállás | 1

Vaspör | 1

Szedres | 1

Szany | 1

Szökedencs | 2

Nyíregyháza | 7

Velence | 4

Zalakomár | 1

Dióskál | 1

and other more 1000 rows...

The number of geocaches in the settlements with all settlements:

gisdb=# SELECT settlements.name, Count(geocaches.name) gisdb-# FROM settlements LEFT JOIN geocaches gisdb-# ON (settlements.geom ~ geocaches.geom AND gisdb(# contains(settlements.geom, geocaches.geom)) gisdb-# GROUP BY 1; name | count ---+--- Bárna | 0

Nyírbéltek | 1

Szentgotthárd | 2

Darány | 0

Nemesvámos | 1

Ráckeve | 3

Sajólád | 0

Drávagárdony | 0

Kölked | 1

Csabacsüd | 0

Cún | 0

Mozsgó | 0

Várkeszö | 0

Jánoshida | 0

Sorkikápolna | 0

(10)

Complex queries in PostGIS

6

Neighbours of Székesfehérvár:

gisdb=# SELECT a.name

gisdb-# FROM settlements AS a, settlements AS b gisdb-# WHERE b.name='Székesfehérvár' AND

gisdb-# a.geom && b.geom AND touches(a.geom, b.geom);

name

--- Aba

Sárszentmihály Seregélyes Szabadbattyán Tác

Csór

Iszkaszentgyörgy Moha

Pákozd Pátka

Sárkeresztes Zámoly (12 rows)

Neighbours of the settlements in a comma separated list:

gisdb=# SELECT a.name, gisdb-# count(*),

gisdb-# string_agg(b.name,', '

gisdb(# ORDER BY azimuth(centroid(a.geom),centroid(b.geom))) gisdb-# FROM settlements AS a, settlements AS b

gisdb-# WHERE a.geom && b.geom AND touches(a.geom, b.geom) gisdb-# GROUP BY 1

gisdb-# ORDER BY 1;

name | count |

string_agg

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

--- ----

--- ---

Aba | 8 |

Pákozd, Seregélyes, Sárosd, Sárkeresztúr, Soponya, Csösz, Tác, Székesfehérvár Abádszalók | 9 |

Ujlörincfalva, Tiszaderzs, Tiszaszentimre, Kunmadaras, Tomajm onostora, Kunhegyes, Tiszabura, Kisköre, Tiszanána

Abaliget | 8 |

Kovácsszénája, Orfü, Kövágószölös, Kövágótöttös, Hetvehely, Okorvölgy, Szentkatalin, Husztót

Abasár | 3 | Markaz, Visonta, Gyöngyös Abaújalpár | 4 |

Sima, Abaújszántó, Abaújkér, Boldogköújfalu Abaújkér | 8 |

Boldogköváralja, Boldogköújfalu, Abaújalpár, Abaújszántó, Hernádbüd, Encs, Méra, Hernádcéce

Abaújlak | 7 |

Gagyvendégi, Gagyapáti, Abaújszolnok, Nyésta, Felsövadász, Gadna, Gagybátor Abaújszántó | 10 |

Abaújalpár, Sima, Erdöbénye, Tállya, Golop, Monok, Felsödobsza, Pere, Hernádbüd, Abaújkér

Abaújszolnok | 5 |

Gagyapáti, Baktakék, Selyeb, Nyésta, Abaújlak Abaújvár | 5 |

Kéked, Pányok, Telkibánya, Zsujta, Tornyosnémeti

(11)

Complex queries in PostGIS

Abda | 7 |

Györzámoly, Györújfalu, Györ, Ikrény, Börcs, Öttevény, Kunsziget Abod | 9 |

Rakacaszend, Rakaca, Irota, Szakácsi, Lak, Ládabesenyö, Szendrölád, Szendrö, Galvács Abony | 8 |

Ujszász, Zagyvarékas, Szolnok, Tószeg, Köröstetétlen, Törtel, Cegléd, Ujszilvás Ábrahámhegy | 7 |

Kövágóörs, Balatonrendes, Balatonboglár, Fonyód, Badacsonytomaj, Salföld, Kékkút Ács | 7 |

Komárom, Csém, Nagyigmánd, Bábolna, Bana, Böny, Nagyszentjános Acsa | 7 |

Galgaguta, Vanyarc, Erdökürt, Galgamácsa, Püspökhatvan, Csövár, Nógrádsáp Acsád | 6 |

Gór, Szeleste, Vasszilvágy, Salköveskút, Meszlen, Csepreg Acsalag | 2 |

Bösárkány, Csorna

Ácsteszér | 6 |

Kisbér, Aka, Súr, Csatka, Réde, Bakonyszombathely Adács | 7 |

Karácsond, Nagyfüged, Visznek, Jászárokszállás, Vámosgyörk, Atkár, Gyöngyöshalász Ádánd | 8 |

Enying, Szabadhidvég, Nagyberény, Som, Nyim, Ságvár, Siójút, Balatonszabadi Adásztevel | 4 |

Nagygyimót, Homokbödöge, Nagytevel, Pápa Adony | 8

Ráckeve, Lórév, Makád, Kulcs, Rácalmás, Perkáta, Pusztaszabolcs, Iváncsa

4.3. 6.4.3 Modifying the data structure

Create a new geometry column in the counties table:

gisdb=# SELECT AddGeometryColumn('counties', 'geom', 23700, 'POLYGON', 2);

addgeometrycolumn

--- public.counties.geom SRID:23700 TYPE:POLYGON DIMS:2 (1 row)

Create spatial index for the new geometry column:

gisdb=# CREATE INDEX counties_geom ON counties USING GIST (geom);

CREATE INDEX

Create the Polygons of the counties:

gisdb=# UPDATE counties SET geom=

gisdb-#(SELECT ST_Union(geom) FROM settlements

gisdb(# WHERE counties.county_id=settlements.county_id);

UPDATE 20

Bibliography

PostgreSQL Global Development Group: 1996-2010.

Refractions Research Inc.: PostGIS 1.5.2 manual, 2010.

Open Geospatial Consortium: OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 1: Common architecture, OGC 06-103r4, 2010.

Spatial Databases by Open Standards and Software 6.

Spatial Databases by Open Standards and Software 6.

Complex queries in PostGIS

Gábor Nagy

Spatial Databases by Open Standards and Software 6.: Complex queries in PostGIS

Gábor Nagy Lector: Zoltán Siki

This module was created within TÁMOP - 4.1.2-08/1/A-2009-0027 "Tananyagfejlesztéssel a GEO-ért"

("Educational material development for GEO") project. The project was funded by the European Union and the Hungarian Government to the amount of HUF 44,706,488.

v 1.0

Publication date 2010

Copyright © 2010 University of West Hungary Faculty of Geoinformatics

Advanced features and comlex queries in PostGIS

Table of Contents

6. Complex queries in PostGIS ... 1

1. 6.1 Introduction ... 1

2. 6.2 Joining tables by geospatial conditions ... 1

3. 6.3 Constraints in geometry columns ... 2

3.1. 6.3.1 Default constraints ... 2

3.2. 6.3.2 User defined constraints ... 2

4. 6.4 Sample queries ... 2

4.1. 6.4.1 Sample datasets ... 2

4.2. 6.4.2 Queries ... 2

4.3. 6.4.3 Modifying the data structure ... 7

Chapter 6. Complex queries in PostGIS

1. 6.1 Introduction

This module contains examples for complex queries and advanced database features of PostGIS.

Many query examples can be found at the end of the module. This queries use a data set with Hungarian settlements and geocaches.

2. 6.2 Joining tables by geospatial conditions

Two or more tables can be joined by normal attributes:

or an equivalent solution:

We can join two tables by a spatial condition:

or the condition may be in the

statement:

This query is slow, because the database server has to check each pairing that may be very lengthy. For example if 1000 districts and 50000 poi exist, the database server will check 50 million district-poi pairs by the contains function.

We can make it faster by the pre-screening operators, which check the bounding box of the objects. These operators use the GIST indexes, accordingly return fast the row-pairs. Another condition joined by an

operator can specify the strict spatial relationship.

or the equivalent solution:

The

The row-pairs will be created faster (even with a great number of lines), if the geometry columns are indexed.

Complex queries in PostGIS

2

3. 6.3 Constraints in geometry columns

The tables can contain constraints related to the geometry columns.

3.1. 6.3.1 Default constraints

The AddGeometryColumns function defines three constraints for the created geometry column:

• check the dimension of the geometry

• check the type of the geometry

• check the SRID of the geometry

These constraints prevent storing geometries in this column with different types, dimensions or spatial reference systems.

3.2. 6.3.2 User defined constraints

The users of the database (if he has the necessary privileges) can add more constraints related to the geometry columns.

For example the next command defines a constraint that prevents creating larger polygons than 10000 square meters:

If there are any rows in the table, which have larger area than 10000 square meters, when this command is executed, the command will be rejected.

4. 6.4 Sample queries

4.1. 6.4.1 Sample datasets

The

table contains the identification number (

), the name (

), the statistical code (

), the identification number of the county (

) and 2D MultiPolygon geometry (

) of Hungarian settlements.

The

table contains the identification number (

) and the name (

) of Hungarian counties.

The

table contains the identification number (

), the name (

), the short name (

) and 3D Point geometry (

) of geocaches from the http://www.geocaching.hu/ site.

All the geometry columns use the Hungarian projection system. (Hungarian abbrevatabbreviation is EOV, SRID is 23700)

4.2. 6.4.2 Queries

The 10 largest settlements of Hungary:

Complex queries in PostGIS

The name of county, the name of settlement and the area of settlement in square kilometres, ordered by the name of county and settlement:

The area of counties:

The geocaches, and the settlements, where the geocache is located:

Complex queries in PostGIS