PostGIS Terminal: Unterschied zwischen den Versionen

Version vom 14. Juni 2012, 08:36 Uhr (Quelltext anzeigen) Stefan (Diskussion | Beiträge) K (→‎Examples) ← Zum vorherigen Versionsunterschied		Version vom 14. Juni 2012, 08:37 Uhr (Quelltext anzeigen) Stefan (Diskussion | Beiträge) K (→‎Examples) Zum nächsten Versionsunterschied →
Zeile 111:		Zeile 111:
	This is the general syntax for the SQL. See also		This is the general syntax for the SQL. See also
−	* [[PostGIS_Terminal#XAPI-to-Map|XAPI-to-Map]] for an easy introduction, and	+	* [[PostGIS_Terminal#XAPI-to-Map|XAPI-to-Map]] below for an easy introduction, and
	* [[POI]]s for other frequently used tags.		* [[POI]]s for other frequently used tags.

---

Version vom 14. Juni 2012, 08:37 Uhr

PostGIS Terminal is an online browser shell to query a geospatial database called EOSMDBOne ("Extended OSM DB One") using spatial SQL. The result is visualized in raw graphics in a map window directly in the browser (map view). The background map and the geodata come from OpenStreetMap. The data is syncronized every night (area of Switzerland) from the OpenStreetMap database to EOSMDBOne.

PostGIS

 >> Go to PostGIS Terminal... << - NOTE: Use with care! There is no warrranty about availability!

About

The PostGIS Terminal was developed based on the original idea and implementation of Marc Jansen and Till Adams. They are the authors of the german OpenLayers book (see [1] and [2]). The idea was also realized independently in postgisonline.org.

PostGIS Terminal is mainly a rich client written in JavaScript with some server code currently in PHP. Specifically it runs on Apache (with PHP support), PostgreSQL/PostGIS (see EOSMDBOne) plus OpenLayers. There are no other SW dependencies.

Über...

Notes (in german): Das PostGIS Terminal ist eine Art Webadmin-Tool zur Visualisierung von Spatial SQL Queries und kann für Ausbildungzwecke aber auch für GIS-Administratoren nützlich sein. Installieren siehe _README.txt. Es gibt ev. mal eine Homepage auf http://labs.geometa.info/ (analog http://openlayers-buch.de/beispiele/chapter-09/postgis-terminal.html ). Setzt Apache/PHP und PostgresSQL/PostGIS voraus. Passende Daten dazu stammen beispielsweise von OpenStreetMap (http://download.geofabrik.de/osm/ ). Testen mit den Beispielen in _EXAMPLE_QUERIES.txt und nice-queries-for-postgis-console.sql. Könnte theoretisch auch weitere Geo-Datenbanksysteme unterstützen (müsste dann aber umbenannt werden).

How to use it

WKT ( ST_AsText(...) ) is being displayed in map window.

The result of a query (resultset)...

• SELECT WKT... FROM ... => map
• SELECT ST_AsText(...) AS geom FROM ... => map
• SELECT ST_AsText(...) AS geom, name as label FROM ... => map with text label

else => output goes to log window.

Credits / License

Credits to...

• Marc Jansen/Till Adams (code, OpenLayers book)
• and Hartmut Holzgräfe (code)
• and the fabulous OpenStreetMappers (data).

Software is licensed under "New BSD License" (see http://en.wikipedia.org/wiki/BSD_licenses ).

Terms of Use

Help

Database

Tables and Attributes

You can find out more about the actual database by clicking on the '?' button right to 'Database'. To find out the attribute names, you can do a: "SELECT * from osm_point"

Schema

For a more information about the database see 'Enhanced OpenStreetMap Database One' (EOSMDBOne).

Key/Value Pairs

If you are searching for the right keys and values, try Taginfo (http://taginfo.openstreetmap.ch/); e.g. search for key 'tourism', then click on the key again and you get the values, like 'zoo' (which makes "tourism = zoo"). See also 'Map Features' on OSM Wiki.

SQL Help

To be defined. The use of selectors which "generate" SQL into query input console is experimental from an eLearning view.

Help using PostGIS Terminal

PostGIS Terminal analyses your SQL query (from your input into the Query editor), beautifies it and looks for placeholders. Output (text or map) can be controlled with field names (or function names) if they exist in the SQL console input or in the query result.

Output can go to

1. log window (called "text query") or to
2. map window (called "map query").

"map queries" can be either be "geometry" (= point, linestring, polygon) or "marker" (= icon).

 .--------------------------.
 | PostGIS Terminal...      |
 |--------------------------|
 | xxx     |                |
 |         |                |
 | Query:  |                |
 | ####### |   Map Window   |
 | ####### |                |
 |         |                |
 | Log:    |                |
 | ####### |----------------|
 | ####### |         Footer |
 .--------------------------.
 Figure: GUI Layout of PostGIS Terminal.

Reserved field and function names are:

• "geom"  : field with WKT geometry.
• "label  : field with text displayed as a label of the geometry.
• mapextent(): bbox of current map view.

System variable placeholder:

• _table: for current Table/name name.
• _geom: for Geometry attribute name.
• _string: for Search name.
• _mouse_x/_mouse_y: for each mouse click in map window.

See e.g. examples below.

Examples

This is the general syntax for the SQL. See also

• XAPI-to-Map below for an easy introduction, and
• POIs for other frequently used tags.

  SELECT geom, label
  FROM our_poi_table
  WHERE (our_poi_tags contain a tag with 'key=value')
  AND (our_poi_tags contain a tag with 'other_key=other_value')  

Zoos der Schweiz:

  SELECT ST_AsText(way) AS geom, name||' '||osm_id AS label
  FROM osm_all
  WHERE tags @> hstore('tourism','zoo')  

Schweizer Kernkraftwerke mit 40 Km-Puffer:

  SELECT
   ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom,
   name AS label
  FROM osm_all
  WHERE tags @> hstore('generator:source','nuclear')  

Alle Restaurants mit Namen 'Rössli' der Schweiz:

  SELECT ST_AsText(way) AS geom, name AS label
  FROM osm_point
  WHERE amenity = 'restaurant'
  AND name ILIKE '%rössli%'  

Suche nach Namen, die "Hochschule" und "Rapperswil" (und was auch immer für Zeichen dazwischen sind):

  SELECT ST_AsText(way) geom,
  COALESCE(name, '')||' '||osm_id AS label
  FROM osm_all
  WHERE hstore("tags")->'name' LIKE 'Hochschule%Rapperswil'   

Alle 4000er Berggipfel der Schweiz:

  SELECT ST_AsText(way) AS geom, name||','||ele AS label
  FROM osm_point
  WHERE "natural" = 'peak'
  AND  to_number('0'||ele, '99999999999.000')::int >= 4000
  
  oder:
  
  SELECT ST_AsText(way) AS geom, COALESCE(name, '')||' '||osm_id AS label
  FROM osm_poi
  WHERE tags @> hstore('natural', 'peak')
  AND CAST(regexp_replace(hstore("tags")->'ele', '[^0-9\.]', '', 'g') AS real) >= 4000  

Alle Aussichtspunkte im Kanton Zürich, die höher als 500 m ü.M. sind:

  SELECT ST_AsText(a.way) geom, COALESCE(name, '')||' '||ele||' m ü.M' AS label
  FROM 
    osm_point AS a, 
    (SELECT way FROM osm_polygon 
     WHERE name = 'Zürich'
     AND tags @> hstore('admin_level','4')) AS b
  WHERE ST_Contains(b.way,a.way)
  AND tags @> hstore('tourism','viewpoint')
  AND  to_number('0'||ele, '99999999999.000')::int >= 500   

Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt:

  SELECT ST_AsText(way) AS geom, name AS label
  FROM osm_point
  WHERE tourism IN ('picnic_site','viewpoint')
  AND ST_Contains(mapextent(), way)  

Alle Schulhäuser im Umkreis von 40 km aller Kernkraftwerke:

  SELECT ST_AsText(a.way) AS geom, '' AS label
  FROM 
    osm_poi AS a, 
    (SELECT ST_Buffer(ST_Centroid(way),40000) AS way
     FROM osm_poi
     WHERE tags @> hstore('generator:source','nuclear')) AS b
  WHERE ST_Within(a.way,b.way)
    AND a.tags @> hstore('amenity', 'school')
  UNION
  SELECT ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, COALESCE(name, '') AS label
  FROM osm_poi
  WHERE tags @> hstore('generator:source','nuclear')  

Die nächsten 10 Bars in der Nähe von 'mylocation' (ungeachtet der Distanz):

  SELECT ST_AsText(osm_poi.way) AS geom, COALESCE(name, '') AS label
  FROM 
    osm_poi, 
    (SELECT ST_Transform(ST_GeomFromText('POINT(8.81638 47.22666)', 4326), 900913) AS way) AS mylocation
  WHERE osm_poi.tags @> hstore('amenity', 'bar')
  ORDER BY osm_poi.way <-> mylocation.way
  LIMIT 10  

Die nächsten 100 Restaurants in der Nähe von 'mylocation' im Umkreis von max. 20 Km (Luftlinie):

  SELECT ST_AsText(osm_poi.way) AS geom, COALESCE(name, '') AS label
  FROM 
    osm_poi, 
    (SELECT ST_Transform(ST_GeomFromText('POINT(8.81638 47.22666)', 4326), 900913) AS way) AS mylocation
  WHERE ST_DWithin(osm_poi.way, mylocation.way, 20000)
  AND osm_poi.tags @> hstore('amenity', 'restaurant')
  ORDER BY osm_poi.way <-> mylocation.way
  LIMIT 100  

XAPI-to-Map

NOTE: If PostGIS Terminal shows an error, try to Copy&Paste the examples below into the browser of your choice!

You can query the PostGIS-Terminal by using the XAPI syntax from OSM, like this:

• Zoos: http://labs.geometa.info/postgisterminal/?xapi=*[tourism=zoo] [3]
• Museum: http://labs.geometa.info/postgisterminal/?xapi=*[tourism=museum] [4]
• Castles (Burgen): http://labs.geometa.info/postgisterminal/?xapi=*[historic=castle] [5]
• Viewpoints (Aussichtspunkte) around Pfannenstiel: http://labs.geometa.info/postgisterminal/?xapi=*[tourism=viewpoint][bbox=8.420,47.072,9.088,47.431]&zoom=11&lat=47.26079&lon=8.77597&layers=B0T [6]
• Hedges (Hecken): http://labs.geometa.info/postgisterminal/?xapi=line[barrier=hedge] [7]
• Marroni-Stände: http://labs.geometa.info/postgisterminal/?xapi=node[cuisine=roasted_chestnut] [8]
• Behindertenparkplätze: http://labs.geometa.info/postgisterminal/?xapi=*[amenity=parking][capacity:disabled=*][bbox=8.420,47.072,9.088,47.431]
• Uebernachten: http://labs.geometa.info/postgisterminal/?xapi=*[tourism=camp_site|hotel|hostel|guest house|chalet|motel|caravan_site]

Feedback

Please use this contact form here.

Download and Installation

• There's no download yet (pls. send request as e-mail).
• Installation, readme and more are included in the delivery (zip file).

Weblinks

• http://openlayers-buch.de/beispiele/chapter-09/postgis-terminal.html (german)
• postgisonline.org