Diskussion:PostGIS Terminal: Unterschied zwischen den Versionen
Stefan (Diskussion | Beiträge) (→Abbildung XAPI => SQL) |
Stefan (Diskussion | Beiträge) (→Abbildung XAPI => SQL) |
||
Zeile 99: | Zeile 99: | ||
--DROP TABLE osm_node | --DROP TABLE osm_node | ||
CREATE TABLE osm_node AS SELECT * FROM osm_node_v; | CREATE TABLE osm_node AS SELECT * FROM osm_node_v; | ||
− | ALTER TABLE osm_node ADD PRIMARY KEY(id); | + | 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(way); -- takes very long time!?! | ||
CREATE INDEX ON osm_node USING GIST(tags); | CREATE INDEX ON osm_node USING GIST(tags); |
Version vom 15. Juni 2011, 23:12 Uhr
Please note here your questions and feedback below. You can also send them directly to Stefan Keller.
Inhaltsverzeichnis
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/node[tourism=zoo]
- http://152.96.80.16/node[amenity=*][bbox=5.943,45.935,10.612,47.731]
- http://152.96.80.16/node[highway=busstop]
- http://152.96.80.16/node[amenity=hospital][bbox=-6,50,2,61]?zoom=18&lat=47.223&lon=8.822&layers=B0T
- http://152.96.80.16/*[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
Dokumentation
Hier konkrete, laufende Beispiele:
- http://jxapi.openstreetmap.org/xapi/api/0.6/*%5Bname=Sylt%5D
- http://open.mapquestapi.com/xapi/api/0.6/node[operator=Stadtverkehr Lübeck]
- http://open.mapquestapi.com/xapi/api/0.6/node[ref:svhl=*]
- Defunct?
Dokumentation:
- XAPI
- Query-Syntax: ANTRL-Syntax [1], Query-Parser in Python [2]
- JXAPI-Logs (live!): http://jxapi.openstreetmap.org/xapi/admin/stats sowie Log-Backup von XAPI (unbestätigter Herkunft).
- Häufige Anfragen gemäss Logs:
- *[amenity=*]
- *[highway=bus_stop]
- *[shop=*]
- *[tourism=*]
- node[railway=station]
- relation[type=restriction]
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 Tabellen:
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; -- *Materialized view* -- --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); 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; -- Materialized view => similar to osm_node! 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).
- daher => ausprobieren, ob osm2pgsql im Slim-mode auch 'version' als Attribut in den Zieltabellen speichern kann.
- osm2pgsql Schema (default.style)
- pgsnapshot_schema_0.6
- Passend dazu SQL-Requests https://github.com/osm-spline/xapi/blob/master/sql/sampleRequests.sql
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_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 Tags 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'