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

[Even Rouault]

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>

Even

-- 
Spatialys - Geospatial professional services
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20171122/be4de287/attachment-0001.html>