wiki.openmod-initiative.org - User contributions [en]

OpenStreetMap

2016-12-21T15:47:33Z

Jonasg: /* Download of OSM data */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}
== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:

[http://wiki.openstreetmap.org/wiki/Key:highway): http://wiki.openstreetmap.org/wiki/Key:highway] <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

= Download of OSM data =

Small chunks off OSM data can be downloaded using QGIS. Under Vektor --> OpenstreetMap --> Daten herunterladen a .osm-file covering the current screen size can be downloaded.

 

OSM data for whole regions is available under [http://download.geofabrik.de/ http://download.geofabrik.de/]

OpenStreetMap

2016-12-21T15:47:05Z

Jonasg:

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}
== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:

[http://wiki.openstreetmap.org/wiki/Key:highway): http://wiki.openstreetmap.org/wiki/Key:highway] <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

= Download of OSM data =

Small chunks off OSM data can be downloaded using QGIS. Under Vektor --> OpenstreetMap --> Daten herunterladen

a .osm-file covering the current screen size can be downloaded.

OSM data for whole regions is available under http://download.geofabrik.de/

Materialized View

2016-11-17T15:18:30Z

Jonasg: Created page with " = Definition = A materialized view inherits both properties from tables as well as views. Similar to a regular view it has to have a original table from which it retrieves i..."

= Definition =

A materialized view inherits both properties from tables as well as views. Similar to a regular view it has to have a original table from which it retrieves its data and to which it is connected. Similar to a table it includes real data instead of just the algorithm which calculates the data.

 

= Abbreviation =

= Synonyms =

= Superterms =

= Subterms =

= Sources =

 

= Visualization in QGIS =

Materialized views should be preferred over regular views when it is intended to visualize the data, for example in a GIS, since regular views have to constantly recalculate their data when visualizing it and therefore cause performance problems.

 

When creating a materialized view which holds geometry data, it is important to create a unique index on the ID column. QGIS requires this unique index to properly display the data.

OpenStreetMap

2016-10-13T16:26:20Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:

[http://wiki.openstreetmap.org/wiki/Key:highway): http://wiki.openstreetmap.org/wiki/Key:highway] <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

OpenStreetMap

2016-10-13T16:26:06Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows

[http://wiki.openstreetmap.org/wiki/Key:highway): http://wiki.openstreetmap.org/wiki/Key:highway] <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

OpenStreetMap

2016-10-13T16:25:11Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows

([http://wiki.openstreetmap.org/wiki/Key:highway): &nbsp http://wiki.openstreetmap.org/wiki/Key:highway):] <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

OpenStreetMap

2016-10-13T16:24:46Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows

(http://wiki.openstreetmap.org/wiki/Key:highway):   <pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

OpenStreetMap

2016-10-13T16:23:09Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:   
<pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service'
OR "highway" = 'living_street' OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
         OR "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'</pre>

OpenStreetMap

2016-10-13T16:21:49Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:   

 
<pre> SELECT * FROM openstreetmap.osm_deu_line
WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary'
OR  "highway" = 'tertiary' OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service' OR "highway" = 'living_street'
             OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
             OR  "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
             OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'
             </pre>

OpenStreetMap

2016-10-13T16:17:18Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:
<pre> SELECT * FROM openstreetmap.osm_deu_line</pre>
<pre> WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary' OR  "highway" = 'tertiary'</pre>
<pre> OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service' OR "highway" = 'living_street'
             OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
             OR  "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
             OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'
             OR  "highway" = 'motorway_link' OR  "highway" = 'motorway_link' OR  "highway" = 'motorway_link' </pre>

OpenStreetMap

2016-10-13T16:16:16Z

Jonasg: /* OSM in oedb */

{{GlossaryTermTemp
|Abbreviation=OSM
|Definition=1. OpenStreetMap (OSM) is a collaborative project to create a free editable map of the world. The creation and growth of OSM has been motivated by restrictions on use or availability of map information across much of the world, and the advent of inexpensive portable satellite navigation devices.[5] OSM is considered a prominent example of volunteered geographic information.
|Sources=https://en.wikipedia.org/wiki/OpenStreetMap, http://www.openstreetmap.org/
}}

== OSM in oedb ==

OSM can be imported in PostgreSQL. The '''oedb''' has the schema openstreetmap. 

All keys and tags are stored in the '''tags '''column with format [https://www.postgresql.org/docs/9.6/static/hstore.html hstore]. To query hstore is different but simple: <pre>SELECT * FROM openstreetmap.osm_deu_point WHERE tags ? 'research_institution'</pre> <pre>SELECT tags -> 'name' AS name, tags -> 'short_name' AS abbreviation FROM openstreetmap.osm_deu_point WHERE tags @> '"amenity"=>"research_institute"'</pre>

 

The import is done with [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/osm2oedb.txt osm2pgsql], [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/tools/osm2pgsql/oedb.style oedb.style] and [https://github.com/openego/data_processing/blob/refactor/as-a-python-script/data_processing/sql_snippets/setup_openstreetmap.sql setup_openstreetmap.sql] 

 

When importing OSM data this way, the tables osm_deu_line and osm_deu_roads are created among others. Osm_deu_line contains every imported line object. Osm_deu_roads does NOT contain only roads, but is a subset of osm_deu_line, which in other applications can be used for simplified map visualisation at higher zoom levels. It is not suitable for data processing purposes because of incompleteness of the data.

 A subset of osm_deu_line which contains only roads can be created by using the highway-key as follows:

<pre><code> SELECT * FROM openstreetmap.osm_deu_line</pre>
<pre><code> WHERE "highway" = 'motorway' OR "highway" = 'trunk' OR "highway" = 'primary' OR "highway" = 'secondary' OR  "highway" = 'tertiary'</pre>
<pre></code> OR  "highway" = 'unclassified' OR "highway" = 'residential' OR "highway" = 'service' OR "highway" = 'living_street'
                    OR  "highway" = 'pedestrian' OR "highway" = 'bus_guideway' OR "highway" = 'road'  
                    OR  "highway" = 'motorway_link' OR  "highway" = 'trunk_link' OR  "highway" = 'primary_link'
                    OR  "highway" = 'secondary_link' OR  "highway" = 'tertiary_link' OR  "highway" = 'track'
                    OR  "highway" = 'motorway_link' OR  "highway" = 'motorway_link' OR  "highway" = 'motorway_link' </pre>

OpenStreetMap

2016-10-13T16:11:46Z

Jonasg: /* OSM in oedb */