[gdal-dev] How to replace an osm key?

[Djordje Spasic]

Thank you Jukka,

How should I edit the command you gave me:

"C:\gdal\ogr2ogr.exe" --config OSM_USE_CUSTOM_INDEXING NO -skipfailures -f "ESRI Shapefile" -dialect sqlite -sql "select geometry, \"building\" as bldg from multipolygons where \"building\" is not null limit 1" "C:\output_shp_files" "C:\input.osm"

so that "building", "building:levels", and "height" fields are included, not only the "building" field?
 

    On Monday, November 14, 2016 2:10 PM, Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi> wrote:
 

 #yiv1051054434 #yiv1051054434 -- _filtered #yiv1051054434 {font-family:Helvetica;panose-1:2 11 6 4 2 2 2 2 2 4;} _filtered #yiv1051054434 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv1051054434 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv1051054434 {font-family:Verdana;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv1051054434 #yiv1051054434 p.yiv1051054434MsoNormal, #yiv1051054434 li.yiv1051054434MsoNormal, #yiv1051054434 div.yiv1051054434MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv1051054434 a:link, #yiv1051054434 span.yiv1051054434MsoHyperlink {color:blue;text-decoration:underline;}#yiv1051054434 a:visited, #yiv1051054434 span.yiv1051054434MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv1051054434 span.yiv1051054434Shkpostityyli17 {color:#1F497D;}#yiv1051054434 span.yiv1051054434Shkpostityyli18 {color:windowtext;}#yiv1051054434 .yiv1051054434MsoChpDefault {font-size:10.0pt;} _filtered #yiv1051054434 {margin:70.85pt 2.0cm 70.85pt 2.0cm;}#yiv1051054434 div.yiv1051054434WordSection1 {}#yiv1051054434 Hi,    If you enclose something between single quotes in SQL it becomes a string.  SELECT ’building’  -> selects literally the given *string* –building— However,    SELECT "building" -> selects the *value* of *field* –building-- (or “identifier” as is it called in SQL)    See for examplehttps://www.sqlite.org/lang_keywords.html    I tested on Windows with trunk version of GDAL that this syntax works:    ogrinfo -dialect sqlite -sql "select geometry, "building" as bldg from multipolygons where "building" is not null limit 1" hamburg-latest.osm.pbf    By reading this old threadhttp://osgeo-org.1560.x6.nabble.com/gdal-dev-OGR-SQL-Excecuting-select-where-column-name-contains-quot-quot-td5006377.html some older GDAL versions may need some more tweaking and inner double quotes must be escaped by back slash as \”    ogrinfo -dialect sqlite -sql "select geometry, \"building\" as bldg from multipolygons where \"building\" is not null limit 1" hamburg-latest.osm.pbf       -Jukka Rahkonen-    Djordje Spasic wrote:    Thank you for the reply Jukka.

Neither "building:levels" nor "height" fields were located in osmconf.ini under the "[multipolygons]".
I have just added them as you suggested, and now the previously mentioned command creates the .shp file without raising an error. I tried the initial: 
-dialect sqlite -sql "select geometry, building as bldg,'building:levels' as bldg_level, height as height from multipolygons" "C:\ladybug\osm_files\barcelona_shp_files" 
and 
-dialect sqlite -sql "select geometry, building as bldg,\"building:levels\" as bldg_level, height as height from multipolygons" "C:\ladybug\osm_files\barcelona_shp_files" 
And they both work.    The problem is that: once the .shp file is created, and I open it, all shapes have the "building:level" value for the "bldg_level" field. Here is a screenshot:
https://www.dropbox.com/s/nmlkkzr85w225qb/sql_choosing_keys.jpg?dl=0

Why is this happening?

Kind Regards,
Djordje    -----------------------    On Saturday, November 12, 2016 10:29 AM, Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi> wrote:    Hi,

Related to your previous question, you can use SQL select only for fields which are included in osmconf.ini. Perhaps "height" is not. 'building:levels' may make a surprise for you, if ogr2ogr follows SQL specification it will select a fixed string. You should use double quotes instead and they may require escaping but I do not remember with which character. Start with easy test and more attributes with odd names one by one.

-Jukka- Lähettäjä:Djordje Spasic
Lähetetty:‎12.‎11.‎2016 3:37
Vastaanottaja:Rahkonen Jukka (MML);gdal-dev at lists.osgeo.org
Aihe:Re: VS: [gdal-dev] How to replace an osm key? Hi Jukka, I have just tried to rename the fields with SQL. Here is the syntax: 
"C:\gdal\ogr2ogr.exe" --config OSM_USE_CUSTOM_INDEXING NO -skipfailures -f "ESRI Shapefile"-dialect sqlite -sql "select geometry, building as bldg, 'building:levels' as bldg_level, height as height from multipolygons" output_shps input.osm 
It raises the following error message: 
ERROR 1: In ExecutableSQL<>: sqlite3_prepare<select geometry, building as bldg, 'building:levels' as bldg_level, height as height from multipolygons>:
no such column: height 
Interestingly when I convert an .osm file to .shp files without using the SQL syntax, then the multipolygons.shp file does contain the "height" field. Not all shapes inside it contain it, but some do.

So why is the upper "no such column: height" error message raised?

Is something wrong with the SQL syntax?

Thank you for the reply.

Kind regards,
Djordje  

   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20161114/26c0f16b/attachment.html>