Diskussion:PostGIS Terminal: Unterschied zwischen den Versionen

Version vom 16. August 2011, 14:25 Uhr

Please note here your questions and feedback below. You can also send them directly to Stefan Keller.

Feature Requests

Query mit gid-Attribut (1): Mit 'Mouse Over' wird der Wer vom Attribut label angezeigt.

Query mit gid-Attribut (2): Mit 'Mouse Click' auf eine Geometrie wird ein Weblink angezeigt, d.h. OpenLayers-Objekte klickbar machen mit Link zu OSM, z.B. http://www.openstreetmap.org/browse/way/82142220

Weitere:

• Data... Next update scheduled on ....
• DB-unabhängig machen! D.h. "Weitere Parameter" (aus config.php) in HTML/Javascript laden.
• DB Zugangsparameter mit Port und Zugangsweise (Text, TSL, SSL) erweitern.
• Besseres Verwalten von Layern, die aus Map und Marker Queries entstanden sind.
• Wie geht das mit Wait-Cursor in OpenLayers???

More Queries...

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'

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:
 --
 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

PostGIS-Terminal Documentation

Database

Data comes from OpenStreetMap and is imported once every early morning using Osm2pgsql. For more information about the database see 'Enhanced OpenStreetMap Database One' (EOSMDBOne).

XAPI-to-Map

About

Die XAPI-to-Map-Funktion wandelt Anfragen in der Query-Sprache XAPI nach SQL um, fragt die OSM-Datenbank ab und stellt das Resultat direkt in der Webkarte dar. XAPI-to-Map ist - wie der Name sagt - kein Webservice (Maschine-Maschine) sondern eine Mensch-Maschine-Webapplikation. Die Syntax kennt häufige Requests, bestehend aus einem oder mehrerer Tag/Value-Paar sowie ggf. einer Bounding Box-Angabe. Nicht implementiert ist z.B. 'Union-Filter' (OR) im Key (node[amenity|leisure=golf_course]). Im Gegensatz zu anderen XAPI-Implementationn kann man dafür mehrere Filter-Prädikate angeben (z.B. node[amenity=golf_course]node[leisure=golf_course]).

Notes on the "xapi=..." parameter and the weblink: Special query characters - except A-Z, a-z and " - must be "percent-encoded US-ASCII octet". There are online tools to help out like this one. (see [1]

Some test examples:

 Use PostGIS Terminal for <postgisterminal_url>:
 <postgisterminal_url>?xapi=node[tourism=zoo]</nowiki> 
 <postgisterminal_url>/?xapi=node[amenity=*][bbox=5.943,45.935,10.612,47.731]</nowiki>
 <postgisterminal_url>/?xapi=node[highway=busstop]</nowiki>
 <postgisterminal_url>/?xapi=node[amenity=hospital][bbox=-6,50,2,61]?zoom=18&lat=47.223&lon=8.822&layers=B0T</nowiki>
 <postgisterminal_url>/?xapi=*[seamark:type=signal_station_warning]</nowiki>

Supported syntax elements (Original XAPI-Dokumentation und andere XAPI-Server: Siehe XAPI):

• Abbildung aller Tabellen sowie '*' (siehe Tabellen-Abbildungen unten)
• Equality Filter (1x): way[highway=motorway]
• Wildcard Filter: im Value way[highway=*]
• BBox-Filter (1x): [bbox=left,bottom,right,top]
• 'Union-Filter' (OR) im Value: way[highway=motorway|motorway_link|trunk|primary]
• Mehrere Filter-Prädikate: node[amenity=golf_course]node[leisure=golf_course]
• It's not possible to do an OR query with nodes OR ways. This is a limitation of the XAPI syntax.

Tabellen-Abbildungen

Tabellen-Abbildungen auf Basis osm2pgsql-Schema, die dann Grundlage der:

• point, line, polygon => osm_point, osm_line, osm_polygon (zusätzlich zur XAPI-Syntax).
• node => osm_point
• way => osm_polygon
• relation => osm_rels
• '*' => osm_all

XAPI-zu-SQL-Abbildungen:

• bbox becomes "WHERE ST_CONTAINS(<bbox>,<point_or_way_geom>)"
• equality search becomes AND hstore(tags)->'key' = 'value'
• wildcard search becomes AND hstore(tags)?'fee'
• Passend dazu SQL-Requests https://github.com/osm-spline/xapi/blob/master/sql/sampleRequests.sql

Implementations-Details

• Wegen '[', '*' sowie ':' (in Keys wie 'name:de') muss die URL kanonisiert werden?
• Spaces sind in Values auch zugelassen, vgl. node[name=Knie's Kinderzoo]
• das XAPI-to-Map funktioniert auch mit Permalink, z.B. node[amenity=hospital][bbox=-6,50,2,61]?zoom=18&lat=47.223&lon=8.822&layers=B0T
• Für häufige Abrafen siehe auch die JXAPI-Logs (live!) sowie den Log-Backup von XAPI mit unbestätigter Herkunft.
• Ev. Zusätzliche Views, Tabellen und Indexes anlegen.
• Primary Keys prüfen! Bei mir hatten osm_point, osm_line, osm_polygon keine!!
• Prüfen was schneller ist: DROP osm_node und all das CREATE... oder TRUNCATE TABLE osm_node und INSERT...
• osm_roads => streichen aus default-style?

Beispiel (man beachte, dass nicht der '->'- sondern der '@>'-Operator verwendet wird wegen GiST-Index):

 .../node[tourism=zoo][bbox=6.2,46.1,10.0,47.6]
 wird abgebildet auf: 
 SELECT ST_AsText(way) geom, name||' '||osm_id label
 FROM osm_point
 WHERE tags @> hstore('tourism','zoo') -- nicht hstore(tags)->'tourism' = 'zoo'
 -- OR ... abhängig von XAPI-Syntax
 AND ST_Contains(ST_Transform(ST_SetSRID('BOX(6.2 46.1, 10.0 47.6)'::box2d, 4326),900913),way)

 .../way[xxx]
 wird abgebildet auf osm_line (oder osm_polygon?)

Webservices API

Date and Table List of Database

• Request: about-db-query.php
• Response besteht aus Datum der letzten erfolgreichen Aktualisierung der OSM Daten mit Hilfe des daily jobs und allen Tabellen in der DB 'gisdb':

LastUpdate=17.06.2011
Tables=geography_columns,geometry_columns,osm_all,osm_line,osm_nodes,osm_point,osm_polygon,osm_rels,osm_roads,osm_ways,spatial_ref_sys

XAPI-to-SQL

• Request:

http://labs.geometa.info/postgisterminal/xapi.py?query=node[amenity=*]

• Response: SQL gemäss dem gegebenen XAPI-String:

SELECT ST_AsText(way) geom,
COALESCE(name, )||' '||osm_id label
FROM osm_point WHERE hstore(tags)?'amenity'

Security Features

GRANTS: Rechte entziehen, damit User Tables und Views nicht erzeugen oder löschen können!

 Schema nicht PUBLIC? Test: create table customer (id serial, name text); drop table customer; !!!
 Konfigurieren PostgreSQL Server (postgresql.conf parameters): Disabling autovacuum daemon, fsync=off etc.

DB-Server-Timeout: als Parameter der PostgreSQL-Verbindung in process-query.php (oder als "statement_timeout (60000)" in postgresql.conf); siehe [2]

 SET STATEMENT_TIMEOUT TO 60000;
 SET transaction_read_only TO TRUE;
 ALTER DATABASE x SET default_transaction_read_only = on;

Logging: Log von IP, Query-String und Response-Infos (success/error/etc. und Prozess-Zeit) speichert. Lösung über process-query.php oder Analyse (grep) Apache-Log?

Data Features

Schema configuration: Style-Datei anpassen, so dass Tabellennamen ohne "planet" und ohne Underscores erzeugt werden, also z.B. anstelle „planet_osm_point“ nur „osmpoint“.

Associative Array: hstore (-h Parameter bei osm2pgsql)

Additional Views and Tables:

 ----------
 -- osm_all
 CREATE OR REPLACE VIEW osm_all_v (id, name, tags, gtype, osm_version, way) AS 
 SELECT osm_id, name, tags, 'pt', osm_version, way
 FROM osm_point
 UNION
 SELECT osm_id, name, tags, 'ln', osm_version, way
 FROM osm_line
 UNION
 SELECT osm_id, name, tags, 'po', osm_version, way
 FROM osm_polygon
 
 CREATE OR REPLACE TABLE osm_all AS (SELECT * FROM osm_all_v);
 
 CREATE INDEX osm_all_name_idx
 ON osm_all(name) WITH (FILLFACTOR=100);
 CREATE INDEX osm_all_tags_idx
 ON osm_point USING gist(tags) WITH (FILLFACTOR=100); -- Kann "FEHLER:  invalid hstore value found" ergeben!
 -- CLUSTER osm_poi USING ; --?
 
 ----------
 -- osm_poi
 CREATE OR REPLACE VIEW osm_poi_v (id, name, tags, gtype, osm_version, way) AS 
 SELECT osm_id, name, tags, 'pt', osm_version, way
 FROM osm_point
 UNION
 SELECT osm_id, name, tags, 'po', osm_version, ST_Centroid(way)
 FROM osm_polygon
 
 CREATE OR REPLACE TABLE osm_poi AS (SELECT * FROM osm_poi_v);
 
 CREATE INDEX osm_poi_name_idx
 ON osm_poi(name) WITH (FILLFACTOR=100);
 CREATE INDEX osm_poi_tags_idx
 ON osm_poi USING gist(tags) WITH (FILLFACTOR=100); -- Kann "FEHLER:  invalid hstore value found" ergeben!
 -- CLUSTER osm_poi USING ; --?

 ----------------
 -- Cleanup DB...
 VACUUM FREEZE ANALYZE;  -- das kann dauern...

• Nightly Update: Einrichten eines Cron-Jobs, der einmal pro Nacht die Schweiz neu einspielt. -- realisiert
• Webservice indicating database update time and a list of tables => about-db-query.php . -- realisiert
• Marker Query with attribute 'icon', mit dem man Icons von externen Quellen (url) darstellen kann.