Spatial Databases by Open Standards and Software 6

University of West Hungary, Faculty of Geoinformatics

Gábor Nagy

Spatial Databases by Open Standards and Software 6. module SDO6

Complex queries in PostGIS

SZÉKESFEHÉRVÁR 2010

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.

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.

Lector: Zoltán Siki

Project manager: András Szepes

Professional project manager: Gábor Mélykúti, dean

Copyright © University of West Hungary Faculty of Geoinformatics 2010

Table of Contents 6. Complex queries in PostGIS .................................................................................................... 6.1 Introduction ................................................................................................................ 6.2 Joining tables by geospatial conditions ............................................................................. 6.3 Constraints in geometry columns .................................................................................... 6.3.1 Default constraints ............................................................................................. 6.3.2 User defined constraints ...................................................................................... 6.4 Sample queries ............................................................................................................ 6.4.1 Sample datasets ................................................................................................. 6.4.2 Queries ............................................................................................................ 6.4.3 Modifying the data structure ................................................................................

1 1 1 1 2 2 2 2 2 7

Chapter 6. Complex queries in PostGIS 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.

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.3 Constraints in geometry columns The tables can contain constraints related to the geometry columns.

Spatial Databases by Open Standards and Software 6.

2010

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.

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.

6.4 Sample queries 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)

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; name | numeric ------------------+--------Hódmezövásárhely | 490.67 Debrecen | 463.56 Hajdúböszörmény | 356.00 Karcag | 352.05 Szentes | 343.25 Gyomaendröd | 303.06 Kecskemét | 298.58 Mezötúr | 296.85 Hortobágy | 295.51

SDO6-2

© University of West Hungary Faculty of Geoinformatics, 2010

Nagy Gábor Szeged (10 rows)

Complex queries in PostGIS |

285.85

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; name | name | area ------------------------+---------------------+--------Bács-Kiskun | Ágasegyháza | 56.77 Bács-Kiskun | Akasztó | 69.01 Bács-Kiskun | Apostag | 33.54 Bács-Kiskun | Bácsalmás | 99.91 Bács-Kiskun | Bácsbokod | 58.30 Bács-Kiskun | Bácsborsod | 77.54 Bács-Kiskun | Bácsszentgyörgy | 16.10 Bács-Kiskun | Bácsszölös | 40.58 Bács-Kiskun | Baja | 186.46 Bács-Kiskun | Ballószög | 39.17 Bács-Kiskun | Balotaszállás | 102.33 Bács-Kiskun | Bátmonostor | 37.22 Bács-Kiskun | Bátya | 38.55 Bács-Kiskun | Bócsa | 102.96 Bács-Kiskun | Borota | 76.44 Bács-Kiskun | Bugac | 128.90 Bács-Kiskun | Bugacpusztaháza | 44.45 Bács-Kiskun | Császártöltés | 86.95 Bács-Kiskun | Csátalja | 37.66 Bács-Kiskun | Csávoly | 43.48 and other more than 3000 rows...

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; name | area ------------------------+--------Bács-Kiskun | 8564.31 Baranya | 4434.91 Békés | 5636.10 Borsod-Abaúj-Zemplén | 7247.92 Budapest | 512.17 Csongrád | 4356.80 Fejér | 4305.55 Győr-Moson-Sopron | 4081.74 Hajdú-Bihar | 6175.91 Heves | 3614.40 Jász-Nagykun-Szolnok | 5602.84 Komárom-Esztergom | 2261.18 Nógrád | 2563.23 Pest | 6396.78 Somogy | 6044.21 Szabolcs-Szatmár-Bereg | 5872.30 Tolna | 3660.03 Vas | 3344.99 Veszprém | 4566.65 Zala | 3822.90 (20 rows)

The geocaches, and the settlements, where the geocache is located: gisdb=# SELECT geocaches.short_name, geocaches.name, settlements.name gisdb-# FROM settlements JOIN geocaches gisdb-# ON (settlements.geom ~ geocaches.geom AND gisdb(# contains(settlements.geom, geocaches.geom)); short_name | name | name


SDO6-3


2010

------------+-----------------------------------------------+--------------------ZICH | Zichy kápolna (-C) | Lórév BSZL | Búcsúszentlászló | Bucsuszentlászló ERTA | Erdőtarcsa | Erdötarcsa HOKN | Szigetcsépi hókonyvilág | Szigetcsép KANY | Kányavári sziget | Balatonmagyaród BANK | Bánki kilátás | Bánk BOKO | Bokodi-tó | Bokod KVCS | Csesztreg - Cseszt-Regélő Aktív Park | Csesztreg PG | Pantheon | Ganna GYAT | Gyáli tó | Gyál CSBC | Csobánc | Gyulakeszi RECS | Recsek-hegyi kilátó | Hidegkút MIX | Mikszáth (+C) | Horpács KKT | Kallósdi kerek templom | Kallósd SZVM | Szarkavári Mauzóleum | Kaposújlak ALFA | Kapos alfától ómegáig | Kiskorpád GYUM | Rába völgye gyümölcsút | Magyarlak NRHT | Nemzeti Radioaktívhulladék Tároló | Mórágy GRNT | Gránittömb | Mórágy DOB | Dobos úr | Perbál and other more 2000 rows...

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 gisdb-# ON (settlements.geom ~ geocaches.geom AND gisdb(# contains(settlements.geom, geocaches.geom)); short_name | name | name ------------+-----------------------------------------------+--------------------VASF | Vasfüggöny emlékhely - Hegykő | Hidegség ZIM | Ezredévi emlékmű - Zimony | EROS | Erős bogyiszlói | Öcsény ESZP | Eszperantó | Visegrád IKS | Ismeretlen katona sírja | Sajóbábony BAYA | Mini-skanzen | Kisbajom BUJD | Bujdosók menedéke | Felsönyék SOHO | Sopronhorpácsi Plébániatemplom | Sopronhorpács TITA | Híradó laktanya Vácon | Vác GOLL | Göller (A) | ZEGA | Prenj-Zelena Glava (BIH) | TULA | Túl a Vágon (SK) | 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 gisdb-# ON (settlements.geom ~ geocaches.geom AND gisdb(# contains(settlements.geom, geocaches.geom)); short_name | name | name ------------+---------------------------------------------+--------------------| | Hernádbüd | | Szatta ZICH | Zichy kápolna (-C) | Lórév BSZL | Búcsúszentlászló | Bucsuszentlászló | | Nagymizdó | | Bögöt | | Börzönce | | Bókaháza | | Csér

SDO6-4


Nagy Gábor | | | | ERTA | Erdőtarcsa | | | | | | and other more 3000 rows...

Complex queries in PostGIS | | | | | | | | | | |

Csomád Csonkamindszent Katafa Kisvásárhely Erdötarcsa Felsöpakony Móricgát Nemeskeresztúr Györújfalu Hegyháthodász Nemesborzova

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 Vizsoly | 1 Szentgáloskér | 0 Pécsbagota | 0 Nyergesújfalu | 3


SDO6-5


2010

Etyek | 2 and other more 3000 rows...

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=# gisdb-# gisdb-# gisdb(# gisdb-# gisdb-# gisdb-# gisdb-#

SELECT a.name, count(*), string_agg(b.name,', ' ORDER BY azimuth(centroid(a.geom),centroid(b.geom))) FROM settlements AS a, settlements AS b WHERE a.geom && b.geom AND touches(a.geom, b.geom) GROUP BY 1 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 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

SDO6-6


Nagy Gábor

Complex queries in PostGIS

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

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. Open Geospatial Consortium: OpenGIS Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option, OGC 06-104r4, 2010.


SDO6-7

Spatial Databases by Open Standards and Software 6

Recommend Documents