[gdal-dev] Extracting keys & values from OSM Planet

[Nikos Alexandris]

* Even Rouault <even.rouault at spatialys.com> [2017-11-22 14:30:23 +0100]:

>On mercredi 22 novembre 2017 14:14:38 CET Nikos Alexandris wrote:
>> * Even Rouault <even.rouault at spatialys.com> [2017-11-20 15:59:51 +0100]:
>> >On lundi 20 novembre 2017 15:48:20 CET Nikos Alexandris wrote:
>> >> * Even Rouault <even.rouault at spatialys.com> [2017-11-07 22:37:02 +0100]:
>> >> >> 2. Is OGR handling well the conversion from .osm to ESRI Shapefiles?
>> >> >
>> >> >Yes, but within the limits ot the shapefile, and particularly .dbf
>> >> >format:
>> >> >limitation to 254 characters for field values, 10 characters for field
>> >> >names... which are easily violated by OSM extracts.
>> >> >
>> >> >Spatialite, GeoPackage, PostGIS etc. would be better choices as output
>> >> >format>
>> >> >
>> >> >> 3. What is the way to split "other_tags" in multiple new fields *when*
>> >> >> knowing exactly which tags are contained and should be obtained?
>> >> >
>> >> >Edit osmconf.ini to add in the attributes= settings the tag names you're
>> >> >interested in.
>> >> >
>> >> >Even
>> >>
>> >> Dear List,
>> >>
>> >> in a custom .ini file, under the [points] layer, an `attributes=`
>> >> instruction set of values that includes `natural,cave_entrance` (key and
>> >> value under OSM's Natural key), well extracts for the points layer some
>> >> existing nodes which bear the tag `natural:cave_entrance`.
>> >>
>> >> It is not required, as far as I understand the documentation, to include
>> >> both the parent key and any values (under it) of interest. Perhaps it may
>> >> be practical to have the attributes of interest split in a separate
>> >> column.
>> >>
>> >> The command I tested, for example, is:
>> >>
>> >> ```
>> >> ogr2ogr --config OSM_CONFIG_FILE custom.ini -f SQLite -dsco
>> >> SPATIALITE=YES
>> >> -gt 65536 -progress output.sqlite source.osm ```
>> >>
>> >> While this is sufficient for the work I am doing (I can filter
>> >> `cave_entrance`s out from the `natural` column in the `output.sqlite`
>> >> file), I wonder why the entries for some nodes correctly bear the string
>> >> `cave_entrance` in the `natural` column, yet the `cave_entrance` column
>> >> itself is empty for the same entries.
>> >
>> >The attributes keyword in osmconf.ini will select OSM tags whose *key* is
>> >one of the item specified in attributes.
>> >If you want to filter by value, you may add a -where or -sql clause  with
>> >something like "natural = 'cave_entrance'"
>>
>> This works!
>>
>> Yet, I can't seem to find an answer on the web, nor a solution by trying.
>> I'd like to perform SQL queries in one go: one querying the points layer
>> and another one the lines layer.
>>
>> This is certainly not a JOIN operation, as far as I understand. And
>> UNION only works in data sourced from the same table/layer. Is it doable
>> to merge an
>>
>> SELECT *
>> FROM points
>> WHERE ...
>>
>> and an
>>
>> SELECT *
>> FROM lines
>> WHERE ...
>>
>> query?
>
>You can't do that in a single operation with ogr2ogr. You may use ogr2ogr for each layer, with
>-append if you need to append to an existing output file
>
>Or if you want to do in a single operation. You can use a OGR VRT
>( http://gdal.org/drv_vrt.html ) file like the following one (customize the name of the
>datasource and the SQL expressions) as the input file of ogr2ogr
>
><OGRVRTDataSource>
>  <OGRVRTLayer name="points">
>    <SrcDataSource relativeToVRT="1" shared="0">in.osm</SrcDataSource>
>    <SrcSQL>SELECT * FROM points</SrcSQL>
>    <GeometryType>wkbPoint</GeometryType>
>    <LayerSRS>WGS84</LayerSRS>
>  </OGRVRTLayer>
>  <OGRVRTLayer name="lines">
>    <SrcDataSource relativeToVRT="1" shared="0">in.osm</SrcDataSource>
>    <SrcSQL>SELECT * FROM lines</SrcSQL>
>    <GeometryType>wkbLineString</GeometryType>
>    <LayerSRS>WGS84</LayerSRS>
>  </OGRVRTLayer>
>  <OGRVRTLayer name="multilinestrings">
>    <SrcDataSource relativeToVRT="1" shared="0">in.osm</SrcDataSource>
>    <SrcSQL>SELECT * FROM multilinestrings</SrcSQL>
>    <GeometryType>wkbMultiLineString</GeometryType>
>    <LayerSRS>WGS84</LayerSRS>
>  </OGRVRTLayer>
>  <OGRVRTLayer name="multipolygons">
>    <SrcDataSource relativeToVRT="1" shared="0">in.osm</SrcDataSource>
>    <SrcSQL>SELECT * FROM multipolygons</SrcSQL>
>    <GeometryType>wkbMultiPolygon</GeometryType>
>    <LayerSRS>WGS84</LayerSRS>
>  </OGRVRTLayer>
>  <OGRVRTLayer name="other_relations">
>    <SrcDataSource relativeToVRT="1" shared="0">in.osm</SrcDataSource>
>    <SrcSQL>SELECT * FROM other_relations</SrcSQL>
>    <GeometryType>wkbGeometryCollection</GeometryType>
>    <LayerSRS>WGS84</LayerSRS>
>  </OGRVRTLayer>
></OGRVRTDataSource>

Magnifique!  Both work as expected.


Some bash functions:

For a single operation, given a custom osmconf.ini file

# Syntax: osm2sqlite  INI-FILE  SQL-CLAUSE  SOURCE.OSM
# Reusing parts from INI and SQL filenames for the output file
function osm2sqlite { ogr2ogr --config OSM_CONFIG_FILE $1 -sql @"$2" -f SQLite -dsco SPATIALITE=YES -gt 65536 -progress $(basename $3 .osm)_$(basename $1 .ini)_${2#*.}.sqlite $3; };


For two operations

# First filtering
# Syntax: osm2sqlite  INI-FILE  SQL-CLAUSE  SOURCE.OSM
function osm2sqlite { ogr2ogr --config OSM_CONFIG_FILE $1 -sql @"$2" -f SQLite -dsco SPATIALITE=YES -gt 65536 -progress $(basename $3 .osm)_$(basename $1 .ini).sqlite $3; };

# Filter and append to output file of the above command, given the same source file is used
# Syntax: osm2sqlite.append  INI-FILE  SQL-CLAUSE  SOURCE.OSM
function osm2sqlite.append { ogr2ogr -append --config OSM_CONFIG_FILE $1 -sql @"$2" -f SQLite -dsco SPATIALITE=YES -gt 65536 -progress $(basename $3 .osm)_$(basename $1 .ini).sqlite $3; };


Similar for the VRT file, just skipping the '-sql' part. For
completeness, I kept only points and lines in the VRT file, since I am
not interested, at the moment, for the rest of the features.

Merci Even u. Danke Frank,

Nikos
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 228 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20171122/3e9ec9b5/attachment.sig>