PostGIS Terminal: Unterschied zwischen den Versionen

Aus Geoinformation HSR
Wechseln zu: Navigation, Suche
(XAPI-to-Map)
K
 
(45 dazwischenliegende Versionen desselben Benutzers werden nicht angezeigt)
Zeile 1: Zeile 1:
 
[[Bild:PostGIS_Terminal.jpg|thumb|300px|right|Printscreen of PostGIS Terminal (Prototype).]]
 
[[Bild:PostGIS_Terminal.jpg|thumb|300px|right|Printscreen of PostGIS Terminal (Prototype).]]
  
'''PostGIS Terminal''' is an online browser shell to query [[PostGIS]] database (using Spatial [[SQL]]) and to see the result in a map window in the browser. The background map and geodata comes from [[OpenStreetMap]].  
+
The '''OSM SQL Terminal''' (formerly known as '''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.  
  
  '''>> Go to [http://labs.geometa.info/postgisterminal/?xapi=node%5Bname%3DHochschule%20Rapperswil%5D PostGIS Terminal]... <<''' - ''NOTE: Use with care! There is no warrranty about availability!''
+
See also [[PostGIS Terminal Examples]], [[PostGIS]].
 +
 
 +
  >> Ask Prof. Stefan Keller about access to it <<
  
 
== About ==
 
== 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 [http://openlayers-buch.de/beispiele/chapter-09/postgis-terminal.html] and [http://2010.foss4g.org/presentations_show.php?id=3630]). The idea was also realized independently in [http://www.postgisonline.org/ postgisonline.org].  
+
The OSM SQL 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 [http://openlayers-buch.de/beispiele/chapter-09/postgis-terminal.html] and [http://2010.foss4g.org/presentations_show.php?id=3630]). The idea was also realized independently in [http://www.postgisonline.org/ 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.
+
The 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... ==
 
== Ü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 [http://openlayers-buch.de/beispiele/chapter-09/nice-queries-for-postgis-console.sql nice-queries-for-postgis-console.sql]. Könnte theoretisch auch weitere Geo-Datenbanksysteme unterstützen (müsste dann aber umbenannt werden).
+
Notes (in german): Das OSM SQL 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 [http://openlayers-buch.de/beispiele/chapter-09/nice-queries-for-postgis-console.sql 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 ==
 
== How to use it ==
Zeile 27: Zeile 29:
 
== Credits / License ==
 
== Credits / License ==
 
Credits to...
 
Credits to...
 +
* Joël Schwab und Felix Reiniger, IFS OST
 
* Marc Jansen/Till Adams (code, OpenLayers book)
 
* Marc Jansen/Till Adams (code, OpenLayers book)
 
* and Hartmut Holzgräfe (code)
 
* and Hartmut Holzgräfe (code)
Zeile 66: Zeile 69:
 
To be defined. The use of selectors which "generate" SQL into query input console is experimental from an eLearning view.
 
To be defined. The use of selectors which "generate" SQL into query input console is experimental from an eLearning view.
  
=== Help using PostGIS Terminal ===
+
=== Help using OSM SQL Terminal ===
  
PostGIS Terminal analyses your SQL query (from your input into the Query editor), beautifies it and looks for placeholders.  
+
The 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  
 
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.  
 
in the SQL console input or in the query result.  
Zeile 79: Zeile 82:
  
 
   .--------------------------.
 
   .--------------------------.
   | PostGIS Terminal...      |
+
   | OSM SQL Terminal...      |
 
   |--------------------------|
 
   |--------------------------|
 
   | xxx    |                |
 
   | xxx    |                |
Zeile 91: Zeile 94:
 
   | ####### |        Footer |
 
   | ####### |        Footer |
 
   .--------------------------.
 
   .--------------------------.
   Figure: GUI Layout of PostGIS Terminal.
+
   Figure: GUI Layout of OSM SQL Terminal.
  
 
Reserved field and function names are:
 
Reserved field and function names are:
Zeile 104: Zeile 107:
 
* _mouse_x/_mouse_y: for each mouse click in map window.
 
* _mouse_x/_mouse_y: for each mouse click in map window.
  
See e.g. examples below.
+
See [[PostGIS Terminal Examples]].
  
=== Examples ===
+
=== Syntax ===
  
See also [[POI]]s for other frequently used tags.
+
This is the general syntax for an SQL query:
  
Zoos der Schweiz:
+
<nowiki>
   SELECT ST_AsText(way) AS geom, name||' '||osm_id AS label, tags
+
   SELECT geom, label
   FROM osm_all
+
   FROM our_poi_table
   WHERE tags @> hstore('tourism','zoo')
+
   WHERE (our_poi_tags contain a tag with 'key=value')
 +
  AND (our_poi_tags contain a tag with 'other_key=other_value') </nowiki>
  
Schweizer Kernkraftwerke mit 40 Km-Puffer:
+
See also:
  SELECT
+
* [[PostGIS_Terminal#XAPI-to-Map|XAPI-to-Map]] below for an easy introduction, and
  ST_AsText(ST_Buffer(ST_Centroid(way),40000)) AS geom,
+
* [[POI]]s for other frequently used tags.
  name AS label
+
* [[PostGIS Terminal Examples]].
  FROM osm_all
 
  WHERE tags @> hstore('generator:source','nuclear')
 
  
Alle Restaurants mit Namen 'Rössli' der Schweiz:
+
Example "Alle Zoos 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:
+
<nowiki>
   SELECT ST_AsText(way) AS geom, name||','||ele AS label
+
   SELECT ST_AsText(way) AS geom, name||' '||osm_id AS label
   FROM osm_point
+
   FROM osm_all
   WHERE "natural" = 'peak'
+
   WHERE tags @> hstore('tourism','zoo') </nowiki>
  AND to_number(ele, '9999') >= 4000
 
  
Alle Picnic-Plätze und Aussichtspunkte im aktuellen Kartenausschnitt:
+
Shortcut using XAPI-to-Map: http://labs.geometa.info/postgisterminal/?xapi=*%5Btourism=zoo%5D
  SELECT ST_AsText(way) AS geom, name AS label
 
  FROM osm_point
 
  WHERE tourism IN ('picnic_site','viewpoint')
 
  AND ST_Contains(mapextent(), way)
 
  
 
=== XAPI-to-Map ===
 
=== XAPI-to-Map ===
  
''NOTE: !''
+
''NOTE: If the OSM SQL 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:
+
You can query the OSM SQL Terminal by using the [[XAPI]] syntax from [[OSM]], like this:
  
* Zoos: <nowiki>http://labs.geometa.info/postgisterminal/?xapi=node[tourism=zoo]</nowiki> [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism=zoo%5D]
+
* Alle Zoos der Schweiz: <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=*[tourism=zoo]</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=*%5Btourism=zoo%5D]
* Castles: <nowiki>http://labs.geometa.info/postgisterminal/?xapi=node[historic=castle]</nowiki> [http://labs.geometa.info/postgisterminal/?xapi=node%5Bhistoric=castle%5D]
+
* Museum: <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=*[tourism=museum]</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=*%5Btourism=museum%5D]
* Viewpoints: <nowiki>http://labs.geometa.info/postgisterminal/?xapi=node[tourism=viewpoint]</nowiki> [http://labs.geometa.info/postgisterminal/?xapi=node%5Btourism=viewpoint%5D]
+
* Castles (Burgen): <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=*[historic=castle]</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=*%5Bhistoric=castle%5D]
* Hedges: <nowiki>http://labs.geometa.info/postgisterminal/?xapi=line[barrier=hedge]</nowiki> [http://labs.geometa.info/postgisterminal/?xapi=line%5Bbarrier=hedge%5D]
+
* Viewpoints (Aussichtspunkte) around Pfannenstiel: <tt><nowiki>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</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=*%5Btourism=viewpoint%5D%5Bbbox=8.420,47.072,9.088,47.431%5D&zoom=11&lat=47.26079&lon=8.77597&layers=B0T]
 +
* Hedges (Hecken): <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=line[barrier=hedge]</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=line%5Bbarrier=hedge%5D]
 +
* Marroni-Stände: <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=node[cuisine=roasted_chestnut]</nowiki></tt> [http://labs.geometa.info/postgisterminal/?xapi=node%5Bcuisine=roasted_chestnut%5D]
 +
* Behindertenparkplätze: <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=*[amenity=parking][capacity:disabled=*][bbox=8.420,47.072,9.088,47.431]</nowiki></tt>
 +
* Uebernachten: <tt><nowiki>http://labs.geometa.info/postgisterminal/?xapi=*[tourism=camp_site|hotel|hostel|guest house|chalet|motel|caravan_site]</nowiki></tt>
  
 
== Feedback ==
 
== Feedback ==
  
'''See >>> [[Diskussion:PostGIS_Terminal]] <<<'''
+
Please use [http://gis.hsr.ch/index.php?option=com_contact&task=view&contact_id=2&Itemid=119 this contact form here].
  
 
== Download and Installation ==
 
== Download and Installation ==

Aktuelle Version vom 14. November 2023, 12:43 Uhr

Printscreen of PostGIS Terminal (Prototype).

The OSM SQL Terminal (formerly known as 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.

See also PostGIS Terminal Examples, PostGIS.

 >> Ask Prof. Stefan Keller about access to it <<

About

The OSM SQL 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.

The 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 OSM SQL 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...

  • Joël Schwab und Felix Reiniger, IFS OST
  • 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

Hinweis.jpg Terms of Use:

By using this Site you agree to these Terms of Use. The contents of the Site, including these terms and conditions of use, are subject to change by us without prior notification.

Exclusion of Liability:

  • We give no warranty regarding the accuracy or completeness of the content of this website. Consequently, we accept no liability for any losses or damages arising out of errors or omissions contained in the Site.
  • No warranty is given that the website shall be available on an uninterrupted basis.
  • Access to and use of this website is at the user's own risk.

Prohibited Use:

  • You agree to use the Site for lawful purposes only, and in a way that does not infringe the rights of, restrict or inhibit anyone else's use of the Site.
  • You agree not to attempt to alter the content of this Site in any way.

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 OSM SQL Terminal

The 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).

 .--------------------------.
 | OSM SQL Terminal...      |
 |--------------------------|
 | xxx     |                |
 |         |                |
 | Query:  |                |
 | ####### |   Map Window   |
 | ####### |                |
 |         |                |
 | Log:    |                |
 | ####### |----------------|
 | ####### |         Footer |
 .--------------------------.
 Figure: GUI Layout of OSM SQL 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 PostGIS Terminal Examples.

Syntax

This is the general syntax for an SQL query:

  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')  

See also:

Example "Alle Zoos der Schweiz":

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

Shortcut using XAPI-to-Map: http://labs.geometa.info/postgisterminal/?xapi=*%5Btourism=zoo%5D

XAPI-to-Map

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

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

  • Alle Zoos der Schweiz: 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