Diskussion:PostGIS Terminal: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
(→‎Data Features)
(→‎Interessante Queries)
Zeile 6: Zeile 6:
 
   -- types numeric, date/time etc. chosen by hand:
 
   -- types numeric, date/time etc. chosen by hand:
 
   --
 
   --
−
   SELECT MIN(keys.key) as feldname, hstore(p.tags)->keys.key, count(*)
+
   SELECT tmp.key||'='||tmp.value as kvp, count(*)::integer as freq
−
   FROM osm_point p, (
+
   FROM (
−
     SELECT key, count(*)
+
     SELECT (each(tags)).key as key, (each(tags)).value as value
−
    FROM (
+
    FROM planet_osm_point) as tmp
−
      SELECT (each(tags)).key FROM planet_osm_point
+
   WHERE (trim(value) !~ '^[-]*[0-9,;.:\- ]+[m]*$')
−
    ) AS stat
+
  AND NOT (value ILIKE '%fixme%' OR key ILIKE '%fixme%')
−
   GROUP BY key HAVING count(*) > 400
+
  AND key NOT LIKE '%:%'
−
    AND key NOT LIKE 'name%'
+
  AND key NOT LIKE '%description%'
−
    AND key NOT LIKE 'addr:%'
+
  AND key NOT LIKE '%comment%'
−
    AND key NOT ILIKE 'FIXME'
+
  AND key NOT LIKE '%name'
−
    AND key NOT ILIKE 'openGeoDB:%'
+
  AND key NOT LIKE 'uic_%'
−
    AND key NOT LIKE 'note:%'
+
  AND key NOT LIKE '%ref'
−
    AND key NOT LIKE '%_ref'
+
  AND key NOT ILIKE '%fixme%'
−
    AND key NOT LIKE '%description%'
+
  AND key NOT ILIKE '%todo%'
−
    AND key NOT LIKE 'contact:%'
+
  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')
−
    AND key NOT LIKE 'operator:%'
+
   GROUP BY tmp.key||'='||tmp.value
−
    AND key NOT LIKE 'uic_%'
+
   HAVING COUNT(*) > 1
−
    AND key NOT LIKE 'TMC%'
+
   ORDER by kvp, freq DESC;
−
    AND key NOT LIKE 'uic_%'
 
−
    AND key NOT LIKE 'direction%'
 
−
    AND key NOT LIKE 'is_in%'
 
−
    AND key NOT LIKE 'wikipedia%'
 
−
    AND key NOT IN ('ele','ref','url','website','email','maxspeed','converted_by','layer','level','phone',
 
−
                    'mobility:station_id','information','opening_hours','date','time','collection_times',
 
−
                    'operator', 'colour', 'fee','nat_name','alt_name','population','seats','postal_code',  
 
−
                    'capacity','line','lines','maxheight','reg_name')
 
−
   ) keys
 
−
  WHERE hstore(p.tags)->keys.key >''
 
−
   GROUP BY hstore(p.tags)->keys.key HAVING count(*) > 1
 
−
   ORDER by 1,3 desc
 
  
 
== Feature Requests ==
 
== Feature Requests ==

Version vom 16. Mai 2011, 13:42 Uhr

Interessante Queries

 --
 -- 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 planet_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 kvp, freq DESC;

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 [1] 

 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 member_name_idx
 ON member
 USING btree
 (upper(last_name), upper(first_name))
 WITH (FILLFACTOR=100);
 
 ALTER TABLE member CLUSTER ON member_name_idx;
 VACUUM FREEZE ANALYZE;

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

Nice-to-have

  • Permalink: Uebergabe Query als URL
  • Anzeige (Test), ob Server überhaupt online ist.
  • 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???
  • Verarbeiten von OSM XAPI-Calls (XAPI nach SQL transformieren)?
  • 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=32881»