PostGIS Terminal: Unterschied zwischen den Versionen
Stefan (Diskussion | Beiträge) (→Help about the OSM database) |
Stefan (Diskussion | Beiträge) |
||
Zeile 107: | Zeile 107: | ||
ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, | ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, | ||
name AS label | name AS label | ||
− | FROM | + | FROM osm_polygon |
WHERE power_source = 'nuclear' | WHERE power_source = 'nuclear' | ||
Alle Restaurants mit Namen 'Rössli' der Schweiz: | Alle Restaurants mit Namen 'Rössli' der Schweiz: | ||
SELECT ST_AsText(way) AS geom, name AS label | SELECT ST_AsText(way) AS geom, name AS label | ||
− | FROM | + | FROM osm_point |
WHERE amenity = 'restaurant' | WHERE amenity = 'restaurant' | ||
AND name ILIKE '%rössli%' | AND name ILIKE '%rössli%' | ||
Zeile 118: | Zeile 118: | ||
Alle 4000er Berggipfel der Schweiz: | Alle 4000er Berggipfel der Schweiz: | ||
SELECT ST_AsText(way) AS geom, name||','||ele AS label | SELECT ST_AsText(way) AS geom, name||','||ele AS label | ||
− | FROM | + | FROM osm_point |
WHERE "natural" = 'peak' | WHERE "natural" = 'peak' | ||
AND to_number(ele, '9999') >= 4000 | AND to_number(ele, '9999') >= 4000 | ||
Zeile 124: | Zeile 124: | ||
Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt: | Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt: | ||
SELECT ST_AsText(way) AS geom, name AS label | SELECT ST_AsText(way) AS geom, name AS label | ||
− | FROM | + | FROM osm_point |
WHERE tourism IN ('picnic_site','viewpoint') | WHERE tourism IN ('picnic_site','viewpoint') | ||
AND ST_Contains(mapextent(), way) | AND ST_Contains(mapextent(), way) |
Version vom 10. Mai 2011, 20:30 Uhr
Inhaltsverzeichnis
About
'PostGIS Terminal' (short PT) is a webapplication to query PostGIS database using pure SQL and see the result in a map window. The background map and geodata comes from OpenStreetMap. It's mainly a rich client written in JavaScript with some server code currently in PHP.
PostGIS Terminal was developed based on the idea and implementation of Marc Jansen and Till Adams german OpenLayers book (see [1] and [2]). There's another implementation behind postgisonline.org.
Preconditions/Dependencies: Apache (with PHP support), PostgreSQL (8 or 9), PostGIS (>=1.5) plus OpenLayers (). There are no other SW dependencies.
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).
>>> Got to PostGIS Terminal - NOTE: Use with care! There is no warrranty about availability! <<<
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
Help about the OSM database
If you are searching for attributes ('key's) and values try Taginfo (http://taginfo.openstreetmap.de/); e.g. search for key 'natural', then click on key natural again and you get the values (like natural = 'water' or natural = 'peak').
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 (1) to log window (called "text query") or (2) to 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
See also file "_EXAMPLE_QUERIES.txt" in .zip file for a start.
Schweizer Kernkraftwerke mit 40 Km-Puffer:
SELECT ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom, name AS label FROM osm_polygon WHERE power_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%'
Alle 4000er Berggipfel der Schweiz:
SELECT ST_AsText(way) AS geom, name||','||ele AS label FROM osm_point WHERE "natural" = 'peak' AND to_number(ele, '9999') >= 4000
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)
Feedback
Please send questions and feedback to Stefan Keller.
Download and Installation
- Installation, readme and more are included in the delivery (zip file).
- There's no download yet (pls. send request as e-mail).