PostGIS Terminal Examples

Here are some examples queries for the PostGIS Terminal.

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

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'

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

Statistics: 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 ('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 key, freq DESC