Diskussion:PostGIS Terminal

Aus Geoinformation HSR
Version vom 15. Juli 2011, 14:08 Uhr von Stefan (Diskussion | Beiträge) (→‎Abbildung XAPI => SQL)

Wechseln zu: Navigation, Suche

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

XAPI-Tests

(HINWEIS: Das ist 'Work in Progress' für das Blackbox-Testing von XAPI-Webservices... Bitte ungeniert direkt unten ergänzen). 

 <xapi_service_url>/node[tourism=zoo]
 <xapi_service_url>/*[highway=bus_stop][bbox=5.943,45.935,10.612,47.731]
 <xapi_service_url>/node[amenity=hospital]
 <xapi_service_url>/node[natural=peak]
 <xapi_service_url>/*[power_source=nuclear]
 <xapi_service_url>/node[amenity=*][bbox=5.943,45.935,10.612,47.731] 
 <xapi_service_url>/*[seamark:type=signal_station_warning]
 <xapi_service_url>/*[amenity=*][bbox=8.420,47.072,9.088,47.431]
 <xapi_service_url>/*[shop=*][bbox=8.420,47.072,9.088,47.431]
 <xapi_service_url>/*[tourism=*][bbox=8.420,47.072,9.088,47.431]
 <xapi_service_url>/node[railway=station][bbox=8.420,47.072,9.088,47.431]
 <xapi_service_url>/relation[type=restriction][bbox=8.420,47.072,9.088,47.431]

Wobei <xapi_service_url> (hier ohne Slash am Ende) ein konkreter XAPI-Webservice ist, namentlich: http://osm.xiala.net:8080/xapi/api/0.6 oder http://152.96.80.16 .

Siehe auch die JXAPI-Logs (live!) sowie den Log-Backup von XAPI mit unbestätigter Herkunft.

XAPI-to-Map

Hinweis: Diese Funktion ist in Bearbeitung. Ideen und Verbesserungsvorschläge sind sehr erwünscht, z.B. per E-Mail an Stefan Keller.

Das Ziel dieser Webapp bzw. Funktion ist das Umwandeln von OSM XAPI Requests nach SQL mit anschliessender Darstellung als Webkarte. Das Ziel ist nicht ein eigentlicher Webservice und auch nicht die Umsetzung der vollständigen "Syntax". Realisiert werden nur spezifische und häufige Requests, bestehend aus einem Tag/Value-Paar sowie ggf. einer Bounding Box-Angabe.

Hier einige Beispiele (geplant):

  • http://152.96.80.16/?xapi=node[tourism=zoo]
  • http://152.96.80.16/?xapi=node[amenity=*][bbox=5.943,45.935,10.612,47.731]
  • http://152.96.80.16/?xapi=node[highway=busstop]
  • http://152.96.80.16/?xapi=node[amenity=hospital][bbox=-6,50,2,61]?zoom=18&lat=47.223&lon=8.822&layers=B0T
  • http://152.96.80.16/?xapi=*[seamark:type=signal_station_warning]

Zu realisierende Syntaxelemente:

  • Prio. 1:
    • Abbildung aller Tabellen sowie '*' (siehe Tabellen-Abbildungen unten)
    • Equality Filter (1x): way[highway=motorway]
    • Wildcard Filter: im Value way[highway=*]
  • Prio. 2:
    • BBox-Filter (1x): [bbox=left,bottom,right,top]
    • 'Union-Filter' (OR) im Value: way[highway=motorway|motorway_link|trunk|primary]
  • Prio. 3:
    • Mehrere Filter-Prädikate: node[amenity=golf_course]node[leisure=golf_course]
    • 'Union-Filter' (OR) im Key: node[amenity|leisure=golf_course]

Response:

  • Karte
  • Ev. muss die DB mit dem Attribut version ergänzt werden: Dazu bei osm2pgsql -x angeben und default.style anpassen (nur version, die anderen nicht).

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 sollte auch funktionieren mit Permalink, z.B. node[amenity=hospital][bbox=-6,50,2,61]?zoom=18&lat=47.223&lon=8.822&layers=B0T

XAPI-Beispiele

Hier konkrete, laufende XAPI-Beispiele:

XAPI-Dokumentation

Abbildung XAPI => SQL

Tabellen-Abbildungen auf Basis osm2pgsql-Schema:

  • point, line, polygon => osm_point, osm_line, osm_polygon
  • node => osm_node (siehe unten; nicht osm_nodes)
  • way => osm_way (siehe unten; nicht osm_ways)
  • relation => osm_rels
  • '*' => osm_nwr (NEU zu erstellen!)
  • all => osm_all (Nicht erwähnt in XAPI aber sinngemäss)

Zusätzliche Views: 

 -- Materialized view => similar to osm_node!
 CREATE VIEW osm_way_v AS
   SELECT 
     w.id AS id, 
     (SELECT ST_SetSRID(ST_MakeLine(ST_Makepoint(n.lon/100.0, n.lat/100.0)),900913) AS way
      FROM osm_nodes n
      JOIN (SELECT unnest(nodes) AS id FROM osm_ways WHERE id=w.id) m ON m.id = n.id
     ) AS way, 
     hstore(w.tags) AS tags
   FROM osm_ways w;

 CREATE VIEW osm_nwr AS
   SELECT id, version, tags
   FROM osm_node
   UNION
   SELECT id, version, tags
   FROM osm_ways
   UNION
   SELECT id, version, tags 
   FROM osm_rels

Zusätzliche Tabellen: 

 -- *Materialized view* --
 CREATE VIEW osm_node_v AS
   SELECT 
     id, 
     ST_SetSRID(ST_MakePoint(lon/100.0, lat/100.0),900913) AS way, 
     hstore(tags) AS tags
   FROM osm_nodes;
 --DROP TABLE osm_node
 CREATE TABLE osm_node AS SELECT * FROM osm_node_v; 
 ALTER TABLE osm_node ADD PRIMARY KEY(id);
 CREATE INDEX ON osm_node USING GIST(way); -- takes very long time!?!
 CREATE INDEX ON osm_node USING GIST(tags);

 Todo RMI:
 * Zusätzliche Tabellen 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... 
 * Mir das Skript mit allen Tabellen und Grants schicken

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'

Original wird das OSM-Schema verwendet, das auch version, timestamp, uid, user, changeset liefert. V.a. version wäre noch wichtig, um die Daten editiert wieder hochzuladen (dann mit version+=1).

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, hstore(tags)->'name'||' '||osm_id label
 FROM osm_node
 WHERE tags @> hstore('tourism','zoo') -- nicht hstore(tags)->'tourism' = 'zoo'
 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?)

Weitere interessante Queries

Alle Tag-Value-Paare der OSM-Daten

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

Artikel zum Laden von OSM Daten

Loading OpenStreetMap data into PostGIS: An Almost Idiot's Guide: [3]

Feature Requests

Security Features

SF1. 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.

=> RMI

SF2. Einbau Timeout als Parameter der PostgreSQL-Verbindung in process-query.php (oder als "statement_timeout (60000)" in postgresql.conf); siehe [4] 

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

=> RMI

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

Data Features

DF1. Style-Datei anpassen, so dass Tabellennamen ohne "planet" und ohne Underscores erzeugt werden, also z.B. anstelle „planet_osm_point“ nur „osmpoint“. => RMI

DF2. Neu immer Laden mit hstore (-h Parameter bei osm2pgsql). Zudem folgende Tabelle hinzufügen: 

 CREATE VIEW osm_all AS 
 SELECT osm_id, name, tags, 'pt' AS gtyp, way
 FROM osm_point
 UNION
 SELECT osm_id, name, tags, 'ln' AS gtyp, way
 FROM osm_line
 UNION
 SELECT osm_id, name, tags, 'po' AS gtyp, way
 FROM osm_polygon

 CREATE INDEX osm_point_name_idx
 ON osm_point(name) WITH (FILLFACTOR=100);

 CREATE INDEX osm_point_tags_idx
 ON osm_point USING gist(tags) WITH (FILLFACTOR=100);
 -- Kann "FEHLER:  invalid hstore value found" ergeben!
 
 ALTER TABLE osm_point CLUSTER ON osm_point_name_idx;

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

=> RMI

DF3. Einrichten eines Cron-Jobs, der einmal pro Nacht die Schweiz neu einspielt (Bitte Vorschlag). => RMI

DF4. Siehe NF2. => RMI

New Functionality/Refactoring

NF1. Refactoring Marker Query from Text class to Vector Class (see Test Cases for Marker Query below). => Kemper/Hengartner

NF2. „Updated: 2011-03-25“ ist zurzeit fix in config.php festgehalten und wird über "?" angezeigt. Schöner wäre eine Anzeige über eine DB-Query (about-db-query.php).

NF3. Anzeige/Einfügen Tabellen und -Attribute als <options...> (laden via about-db-query.php).

NF4. Erweiterung Marker Query um Attribut 'icon', mit dem man Icons von externen Quellen (url) darstellen kann. => erledigt.

Nice-to-have

  • Data... Next update scheduled on ....
  • Umwandeln von OSM XAPI ('zappy') Requests nach SQL (SELECT FROM WHERE...) - auch als Ergänzung des Permalinks.
  • Features klickbar machen mit Link zu OSM mit osm_id, z.B. http://www.openstreetmap.org/browse/way/82142220
  • Vollständig 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.
  • Markers mit Maus-über ("hover") - wären toll... nicht Popup und wegklicken müssen! -
  • Wie geht das mit Wait-Cursor in OpenLayers???
  • Aufrufen von Google Earth (KML network link), falls output mit ST_KML und alias kml (als einzigem Ausgabe-Feld) angegeben.

Test Cases for Marker Query

 --- 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'
Von «https://giswiki.hsr.ch/index.php?title=Diskussion:PostGIS_Terminal&oldid=33309»