PostGIS Terminal Examples
Here are some examples queries for the PostGIS Terminal.
See also PostGIS - Tipps und Tricks.
Inhaltsverzeichnis
- 1 Simple SQL Queries
- 1.1 Alle Geonamen, die bereits in Schweizerdeutscher Mundart ("name:gsw") erfasst sind
- 1.2 Finding a string part of a tag key or a tag value
- 1.3 Alle Zoos der Schweiz
- 1.4 Schweizer Kernkraftwerke mit 40 Km-Puffer
- 1.5 Alle Restaurants mit Namen 'Rössli' der Schweiz
- 1.6 Alle 4000er Berggipfel der Schweiz =
- 1.7 Alle Aussichtspunkte im Kanton Zürich, die höher als 500 m ü.M. sind
- 1.8 Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt
- 1.9 Alle Schulhäuser im Umkreis von 40 km aller Kernkraftwerke
- 1.10 Die nächsten 10 Bars in der Nähe von 'mylocation' (ungeachtet der Distanz)
- 1.11 Die nächsten 100 Restaurants in der Nähe von 'mylocation' im Umkreis von max. 20 Km (Luftlinie)
- 1.12 Alle Strassen mit Namen "Bahnhofstrasse" im Kt.ZH
- 1.13 Bounding Box gegeben ein geografischer Name (Beispiel "Entlebuch")
- 1.14 Alle OeV-Haltestellen
- 2 Complex SQL Queries
- 3 Marker Queries
- 4 Mouse Queries
- 5 Statistics
Simple SQL Queries
Alle Geonamen, die bereits in Schweizerdeutscher Mundart ("name:gsw") erfasst sind
SELECT ST_AsText(way) geom, tags->'name:gsw' AS label FROM osm_poi WHERE tags ? 'name:gsw'
Finding a string part of a tag key or a tag value
SELECT osm_id, name, key FROM (SELECT osm_id, name, skeys(tags) AS key from osm_point) tmp WHERE key LIKE 'generator%';
SELECT osm_id, name, value FROM (SELECT osm_id, name, svals(tags) AS value FROM osm_polygon) tmp WHERE value LIKE 'nuclear%';
Alle Zoos der Schweiz
(Tipp: http://labs.geometa.info/postgisterminal/?xapi=*[tourism=zoo]):
SELECT ST_AsText(way) AS geom, name||' '||osm_id AS label FROM osm_all WHERE tags @> hstore('tourism','zoo')
Schweizer Kernkraftwerke mit 40 Km-Puffer
SELECT ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, name AS label FROM osm_all WHERE tags @> hstore('generator:source','nuclear')
Alle Restaurants mit Namen 'Rössli' der Schweiz
SELECT ST_AsText(way) AS geom, name AS label FROM osm_point WHERE amenity = 'restaurant' AND name ILIKE '%rössli%'
Alle 4000er Berggipfel der Schweiz =
SELECT ST_AsText(way) AS geom, name||','||ele AS label FROM osm_point WHERE "natural" = 'peak' AND to_number('0'||ele, '99999999999.000')::int >= 4000 oder: SELECT ST_AsText(way) AS geom, COALESCE(name, '')||' '||osm_id AS label FROM osm_poi WHERE tags @> hstore('natural', 'peak') AND CAST(regexp_replace(hstore("tags")->'ele', '[^0-9\.]', '', 'g') AS real) >= 4000
Alle Aussichtspunkte im Kanton Zürich, die höher als 500 m ü.M. sind
SELECT ST_AsText(a.way) geom, COALESCE(name, '')||' '||ele||' m ü.M' AS label FROM osm_point AS a, (SELECT way FROM osm_polygon WHERE name = 'Zürich' AND tags @> hstore('admin_level','4')) AS b WHERE ST_Contains(b.way,a.way) AND tags @> hstore('tourism','viewpoint') AND to_number('0'||ele, '99999999999.000')::int >= 500
Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt
SELECT ST_AsText(way) AS geom, name AS label FROM osm_point WHERE tourism IN ('picnic_site','viewpoint') AND ST_Contains(mapextent(), way)
Alle Schulhäuser im Umkreis von 40 km aller Kernkraftwerke
SELECT ST_AsText(a.way) AS geom, '' AS label FROM osm_poi AS a, (SELECT ST_Buffer(ST_Centroid(way),40000) AS way FROM osm_poi WHERE tags @> hstore('generator:source','nuclear')) AS b WHERE ST_Within(a.way,b.way) AND a.tags @> hstore('amenity', 'school') UNION SELECT ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, COALESCE(name, '') AS label FROM osm_poi WHERE tags @> hstore('generator:source','nuclear') LIMIT 2000
Die nächsten 10 Bars in der Nähe von 'mylocation' (ungeachtet der Distanz)
SELECT ST_AsText(osm_poi.way) AS geom, COALESCE(name, '') AS label FROM osm_poi, (SELECT ST_Transform(ST_GeomFromText('POINT(8.81638 47.22666)', 4326), 900913) AS way) AS mylocation WHERE osm_poi.tags @> hstore('amenity', 'bar') ORDER BY osm_poi.way <-> mylocation.way LIMIT 10
Die nächsten 100 Restaurants in der Nähe von 'mylocation' im Umkreis von max. 20 Km (Luftlinie)
SELECT ST_AsText(osm_poi.way) AS geom, COALESCE(name, '') AS label FROM osm_poi, (SELECT ST_Transform(ST_GeomFromText('POINT(8.81638 47.22666)', 4326), 900913) AS way) AS mylocation WHERE ST_DWithin(osm_poi.way, mylocation.way, 20000) AND osm_poi.tags @> hstore('amenity', 'restaurant') ORDER BY osm_poi.way <-> mylocation.way LIMIT 100
Alle Strassen mit Namen "Bahnhofstrasse" im Kt.ZH
SELECT ST_AsText(ST_LineMerge(w.way)) FROM osm_line w, (SELECT way FROM osm_polygon WHERE name = 'Zürich' AND tags @> hstore('admin_level','4')) AS p WHERE w.name = 'Bahnhofstrasse' AND (hstore("tags")->'highway') IS NOT NULL AND ST_Within(w.way, p.way)
Bounding Box gegeben ein geografischer Name (Beispiel "Entlebuch")
- Enter Entlebuch in http://boundingbox.klokantech.com/
- Select DublinCore (and reduce to 4 digits) => westlimit=7.9891; southlimit=46.9419; eastlimit=8.3543; northlimit=47.1263
- Place coords. in Polygon:
SELECT ST_AsText( ST_Transform( ST_GeomFromText( 'MULTIPOLYGON(((7.9891 47.1263, 8.3543 47.1263, 8.3543 46.9419, 7.9891 46.9419, 7.9891 47.1263)))', 4326), 900913) )
Alle OeV-Haltestellen
- Es hat 4000(!) mehr als in OSM als Didok/uic_name eingetragene Nodes?
- Tags-Zusammenstellung aus DIDOK: https://github.com/datendelphin/didok/blob/master/db-import/import_stops.py
- "SELECT way FROM osm_polygon WHERE osm_id=-51701" gibt die Schweizer Grenze zurück (EOSMDBOne enthält auch Teile des angrenzenden Auslands!).
-- Just run this e.g. in PostGIS Terminal: SELECT count(*) FROM osm_point AS osm WHERE ST_Within(osm.way, (SELECT way FROM osm_polygon WHERE osm_id=-51701)) AND NOT (tags ? 'uic_name') AND (tags @> 'railway=>station' OR tags @> 'railway=>halt' OR tags @> 'railway=>tram_stop' OR tags @> 'highway=>bus_stop' OR tags @> 'amenity=>bus_station' OR tags @> 'amenity=>ferry_terminal' OR tags @> 'railway=>funicular' OR tags @> 'aerialway=>station') -- 4935, query runtime: 31271 ms. -- Use PostGIS Terminal to visualize the stops not in Didok (uic_name): SELECT ST_AsText(way) as geom, coalesce(name,'') as label FROM osm_point AS osm WHERE ST_Within(osm.way, (SELECT way FROM osm_polygon WHERE osm_id=-51701)) AND NOT (tags ? 'uic_name') AND (tags @> 'railway=>station' OR tags @> 'railway=>halt' OR tags @> 'railway=>tram_stop' OR tags @> 'highway=>bus_stop' OR tags @> 'amenity=>bus_station' OR tags @> 'amenity=>ferry_terminal' OR tags @> 'railway=>funicular' OR tags @> 'aerialway=>station') -- Get result as GeoCSV/WKT - to be run e.g. in psql or pgAdmin and export result to file: SELECT osm_id, coalesce(name,'') as name, ST_AsText(ST_Transform(way,4326)) as wkt FROM osm_point AS osm WHERE ST_Within(osm.way, (SELECT way FROM osm_polygon WHERE osm_id=-51701)) AND NOT (tags ? 'uic_name') AND (tags @> 'railway=>station' OR tags @> 'railway=>halt' OR tags @> 'railway=>tram_stop' OR tags @> 'highway=>bus_stop' OR tags @> 'amenity=>bus_station' OR tags @> 'amenity=>ferry_terminal' OR tags @> 'railway=>funicular' OR tags @> 'aerialway=>station') -- Resultat in https://gist.github.com/sfkeller/a18675d2190c4e121549e745474dc80f
Complex SQL Queries
Select names of all OSM-objects containing 'zoo' at start middle or end (using wildcard '%' in String):
SELECT ST_AsText(way) geom, COALESCE(name, ' ')||' '||osm_id label FROM osm_all WHERE hstore(tags)->'name' ILIKE '%zoo%'
Select all parking lots for disabled persons (Rollstuhlparkplatz / wheel parking) within visible map area (without FIXME). Note: Tag capacity:disabled currently occurs always together with amenity={parking, parking_space, parking_entrance}):
SELECT ST_AsText(way) geom, COALESCE(name,' ')||' ('||COALESCE(SUBSTRING((tags->'capacity:disabled') FROM E'[0-9]+'),' ')||')' label FROM osm_poi WHERE (tags @> '"capacity:disabled"=>"yes"' OR SUBSTRING((tags->'capacity:disabled') FROM E'[0-9]+')::int > 0) AND ST_Contains(mapextent(), way)
Select all motorways (higways) with speed limit greater or equal than 100:
SELECT ST_AsText(way) geom FROM osm_line WHERE tags @> '"highway"=>"motorway"' AND COALESCE(SUBSTRING((tags->'maxspeed') FROM E'[0-9]+')::int,0) >= 100 -- 1806!
Count tags except xxx...:
SELECT array_upper(%# tags, 1) AS "#tags", count(*) AS "count" FROM osm_point WHERE array_upper(%# tags, 1) > 0 AND NOT EXISTS (SELECT skeys FROM skeys(tags) WHERE skeys LIKE 'xxx:%') GROUP BY 1 ORDER BY 1
Point Clustering with round/truncate to grid and centroid. Parameter 9000 defines the grid width (meter) and is dependent from the units of the coordinate reference systems. Fast - reduces 13358 Restaurants to 1163 - but with a "visual grid effect".
SELECT ST_AsText( ST_Centroid(ST_Collect(position)) ) AS geom, CASE COUNT(position) WHEN 1 THEN COALESCE(max(name), '1') ELSE COUNT(position)::text END AS label, array_agg(osm_id) AS list_of_ids FROM ( SELECT way AS position, name, osm_id FROM osm_poi WHERE tags @> hstore('amenity','restaurant') ) tmp GROUP BY ST_SnapToGrid(position, 9000);
Marker Queries
marker query (must have exactly field names lon, lat, title and description):
SELECT X(p2.way) AS lon, Y(p2.way) AS lat, 'Briefkasten' AS title, p2.ref AS description FROM planet_osm_polygon p1 JOIN planet_osm_point p2 ON CONTAINS(p1.way, p2.way) WHERE p1.name = 'Uster' AND p2.amenity = 'post_box'
Extension with user defined marker icon (attribute 'icon'):
SELECT X(p2.way) AS lon, Y(p2.way) AS lat, 'Briefkasten' AS title, p2.ref AS description, 'http://myserver/marker.png' as icon FROM planet_osm_polygon p1 JOIN planet_osm_point p2 ON CONTAINS(p1.way, p2.way) WHERE p1.name = 'Uster' AND p2.amenity = 'post_box'
Mouse Queries
Finding Nearest Point to Linestrings (Snapping)
- Lösung mit Distanzen innerhalb 50m (siehe ST_DWithin).
- Anwendung: PostGIS-Terminal aufrufen, Copy&Paste dieser Query, dann in Karte klicken.
- Bemerkungen:
- (_mouse_x, _mouse_y) liefern die Mauskoordinaten (anstelle mypos).
- Die Lösung verwendet WITH-Klausel, da (_mouse_x, _mouse_y) vom Terminal nur einmal geparst werden (leider).
- (tags->'highway') liefert die Strassenart, z.B. unclassified, pedestrian, etc.
- (SELECT geom FROM tmp1) liefert die Mauskoordinaten
- COALESCE(name,'NN') liefert NN wenn kein Name vorhanden
WITH tmp1 AS ( SELECT ST_SetSRID(ST_MakePoint(_mouse_x, _mouse_y), 900913) AS geom ), tmp2 AS ( SELECT osm_id, way, name, tags, ST_Distance((SELECT min(geom)::geometry FROM tmp1), way) as distance FROM osm_line WHERE (tags->'highway') IS NOT NULL AND ST_DWithin((SELECT geom FROM tmp1), way, 50) ) SELECT ST_AsText(way) AS geom, COALESCE(name,'NN')||' ('||(tags->'highway')||')' AS label FROM tmp2 ORDER BY distance LIMIT 1
(Fast) dasselbe wie oben aber mit KNN Index (<->) nun aber mit den 10 nächsten Linien (k-Nearest Neighbor (KNN) Index)
- geordnet nach dem Zentrum der Bounding Boxes ('<->' Operator).
- Hinweis: der <#> ordnet nach den Bounding Boxen selber (ab PostGIS 2.0 und PostgreSQL 9.1)
WITH tmp1 AS ( SELECT ST_SetSRID(ST_MakePoint(_mouse_x, _mouse_y), 900913) AS geom ), tmp2 AS ( SELECT osm_id, way, name, tags, ST_Distance((SELECT min(geom)::geometry FROM tmp1), way) as distance FROM osm_line WHERE (tags->'highway') IS NOT NULL ORDER BY way <-> (SELECT min(geom)::geometry FROM tmp1) LIMIT 10 ) SELECT ST_AsText(way) AS geom, COALESCE(name,'NN')||' ('||(tags->'highway')||')' AS label FROM tmp2 ORDER BY distance LIMIT 1
Statistics
All tupels in all tables
SELECT '1. '||to_char(count(*), '999G999G999')||' osm_point(s)' FROM osm_point UNION SELECT '2. '||to_char(count(*), '999G999G999')||' osm_poi(s)' FROM osm_poi UNION SELECT '3. '||to_char(count(*), '999G999G999')||' osm_line(s)' FROM osm_line UNION SELECT '4. '||to_char(count(*), '999G999G999')||' osm_polygon(s)' FROM osm_polygon UNION SELECT '5. '||to_char(count(*), '999G999G999')||' osm_nodes' FROM osm_nodes UNION SELECT '6. '||to_char(count(*), '999G999G999')||' osm_ways' FROM osm_ways UNION SELECT '7. '||to_char(count(*), '999G999G999')||' osm_rels' FROM osm_rels ORDER BY 1
All Tag-Value-Pairs of OSM data
Kann anstelle mit osm_point auch mit osm_all durchgeführt werden.
-- -- Key-Value Statistics of OSM Data -- Return all key-value-pairs of type 'enum' without some -- types numeric, date/time etc. chosen by hand: -- -- Alternative with separate tag-value: -- SELECT tmp.key as tag, tmp.value as value, count(*)::integer as freq SELECT tmp.key||'='||tmp.value as kvp, count(*)::integer as freq FROM ( SELECT (each(tags)).key as key, (each(tags)).value as value FROM osm_point) as tmp WHERE (trim(value) !~ '^[-]*[0-9,.:\ ]+[m]*$') AND NOT (value ILIKE '%fixme%' OR key ILIKE '%fixme%') AND key NOT LIKE '%:%' AND key NOT LIKE '%description%' AND key NOT LIKE '%comment%' AND key NOT LIKE '%name' AND key NOT LIKE 'uic_%' AND key NOT LIKE '%ref' AND key NOT ILIKE '%fixme%' AND key NOT ILIKE '%todo%' AND key NOT IN ('osm_timestamp', 'name','operator','_picture_','_waypoint_','address','alt','is_in','url','website','wikipedia','email', 'converted_by','phone','information','opening_hours','date','time','collection_times','colour','fee', 'population','access','noexit','towards','bus_routes','busline','lines','type','denotation', 'CONTINUE','continue','copyright','stop') GROUP BY tmp.key, tmp.value HAVING COUNT(*) > 1 ORDER by freq DESC; -- ca. 500 sec.!