<html><head></head><body><div style="color:#000; background-color:#fff; font-family:verdana, helvetica, sans-serif;font-size:13px"><div id="yui_3_16_0_ym19_1_1479135998950_10027"><span id="yui_3_16_0_ym19_1_1479135998950_10082">Thank you Jukka,<br><br>How should I edit the command you gave me:<br></span></div><blockquote id="yui_3_16_0_ym19_1_1479135998950_11970"><div id="yui_3_16_0_ym19_1_1479135998950_10081" dir="ltr"><span id="yui_3_16_0_ym19_1_1479135998950_11889">"C:\gdal\ogr2ogr.exe" --config OSM_USE_CUSTOM_INDEXING NO -skipfailures -f "ESRI Shapefile" <b id="yui_3_16_0_ym19_1_1479135998950_12008">-dialect sqlite -sql "select geometry, \"building\" as bldg from multipolygons where \"building\" is not null limit 1"</b> "C:\output_shp_files" "C:\input.osm"<br></span></div></blockquote><div id="yui_3_16_0_ym19_1_1479135998950_11918"><span id="yui_3_16_0_ym19_1_1479135998950_11917">so that "building", "building:levels", and "height" fields are included, not only the "building" field?<br></span></div> <div class="qtdSeparateBR"><br><br></div><div class="yahoo_quoted" style="display: block;"> <div style="font-family: verdana, helvetica, sans-serif; font-size: 13px;"> <div style="font-family: HelveticaNeue, Helvetica Neue, Helvetica, Arial, Lucida Grande, sans-serif; font-size: 16px;"> <div dir="ltr"><font size="2" face="Arial"> On Monday, November 14, 2016 2:10 PM, Rahkonen Jukka (MML) <jukka.rahkonen@maanmittauslaitos.fi> wrote:<br></font></div> <br><br> <div class="y_msg_container"><div id="yiv1051054434"><style>#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 </style><div>
<div class="yiv1051054434WordSection1">
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">Hi,</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">If you enclose something between single quotes in SQL it becomes a string.
</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">SELECT ’building’ -> selects literally the given *<b>string</b>* –building—</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">However,</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">SELECT "building" -> selects the *<b>value</b>* of *<b>field</b>* –building-- (or “identifier” as is it called in SQL)</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">See for example
<a rel="nofollow" shape="rect" target="_blank" href="https://www.sqlite.org/lang_keywords.html">https://www.sqlite.org/lang_keywords.html</a></span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">I tested on Windows with trunk version of GDAL that this syntax works:</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">ogrinfo -dialect sqlite -sql "select geometry, "building" as bldg
</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">from multipolygons where "building" is not null limit 1" hamburg-latest.osm.pbf</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">By reading this old thread
<a rel="nofollow" shape="rect" target="_blank" href="http://osgeo-org.1560.x6.nabble.com/gdal-dev-OGR-SQL-Excecuting-select-where-column-name-contains-quot-quot-td5006377.html">
http://osgeo-org.1560.x6.nabble.com/gdal-dev-OGR-SQL-Excecuting-select-where-column-name-contains-quot-quot-td5006377.html</a> some older GDAL versions may need some more tweaking and inner double quotes must be escaped by back slash as \”</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">ogrinfo -dialect sqlite -sql "select geometry, \"building\" as bldg
</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">from multipolygons where \"building\" is not null limit 1" hamburg-latest.osm.pbf</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">-Jukka Rahkonen-</span></div>
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US"> </span></div>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm;">
<div class="yiv1051054434MsoNormal"><span style="font-size:11.0pt;" lang="EN-US">Djordje Spasic wrote:</span></div>
</div>
</div>
<div class="yiv1051054434MsoNormal"><span lang="EN-US"> </span></div>
<div class="yiv1051054434yqt9047266824" id="yiv1051054434yqt89454"><div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5117">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">Thank you for the reply Jukka.<br clear="none">
<br clear="none">
Neither "building:levels" nor "height" fields were located in osmconf.ini under the "[multipolygons]".<br clear="none">
I have just added them as you suggested, and now the previously mentioned command creates the .shp file without raising an error.</span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7424">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">I tried the initial:</span></div>
</div>
<blockquote id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7289" style="margin-top:5.0pt;margin-bottom:5.0pt;">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7225">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">-dialect sqlite -sql "select geometry, building as bldg,
<b>'building:levels'</b> as bldg_level, height as height from multipolygons" "C:\ladybug\osm_files\barcelona_shp_files"
</span></div>
</div>
</blockquote>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7216">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">and</span></div>
</div>
<blockquote id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7311" style="margin-top:5.0pt;margin-bottom:5.0pt;">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7291">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">-dialect sqlite -sql "select geometry, building as bldg,
<b id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7384">\"building:levels\"</b> as bldg_level, height as height from multipolygons" "C:\ladybug\osm_files\barcelona_shp_files"
</span></div>
</div>
</blockquote>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7181">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">And they both work.</span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7441">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US"> </span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_7442">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">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:<br clear="none">
</span><a rel="nofollow" shape="rect" id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_9365" target="_blank" href="https://www.dropbox.com/s/nmlkkzr85w225qb/sql_choosing_keys.jpg?dl=0"><span style="font-size:10.0pt;" lang="EN-US">https://www.dropbox.com/s/nmlkkzr85w225qb/sql_choosing_keys.jpg?dl=0</span></a><span style="font-size:10.0pt;" lang="EN-US"><br clear="none">
<br clear="none">
Why is this happening?<br clear="none">
<br clear="none">
Kind Regards,<br clear="none">
Djordje</span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_9448">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US"> </span></div>
</div>
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">-----------------------</span></div>
<div>
<div class="yiv1051054434MsoNormal" style="margin-bottom:12.0pt;background:white;"><span style="font-size:10.0pt;" lang="EN-US"> </span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5030">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5029">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5028">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5032">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">On Saturday, November 12, 2016 10:29 AM, Rahkonen Jukka (MML) <</span><a rel="nofollow" shape="rect" ymailto="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><span style="font-size:10.0pt;" lang="EN-US">jukka.rahkonen@maanmittauslaitos.fi</span></a><span style="font-size:10.0pt;" lang="EN-US">>
wrote:</span><span style="" lang="EN-US"></span></div>
</div>
<div class="yiv1051054434MsoNormal" style="margin-bottom:12.0pt;background:white;"><span style="" lang="EN-US"> </span></div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5036">
<div id="yiv1051054434">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5035">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5034">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5033">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:11.0pt;" lang="EN-US">Hi,<br clear="none">
<br clear="none">
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.<br clear="none">
<br clear="none">
</span><span style="font-size:11.0pt;">-Jukka-</span></div>
</div>
</div>
<div id="yiv1051054434yqt26386">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5037">
<div class="yiv1051054434MsoNormal" style="text-align:center;background:white;" align="center">
<span style="">
</span><hr width="100%" size="2" align="center">
</div>
<div class="yiv1051054434MsoNormal" style="margin-bottom:12.0pt;background:white;"><b><span style="font-size:11.0pt;">Lähettäjä:
</span></b><a rel="nofollow" shape="rect" ymailto="mailto:issworld2000@yahoo.com" target="_blank" href="mailto:issworld2000@yahoo.com"><span style="font-size:11.0pt;">Djordje Spasic</span></a><span style=""><br clear="none">
</span><b><span style="font-size:11.0pt;">Lähetetty:
</span></b><span style="font-size:11.0pt;">12.11.2016 3:37</span><span style=""><br clear="none">
</span><b><span style="font-size:11.0pt;">Vastaanottaja:
</span></b><a rel="nofollow" shape="rect" ymailto="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank" href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><span style="font-size:11.0pt;">Rahkonen Jukka (MML)</span></a><span style="font-size:11.0pt;">;
</span><a rel="nofollow" shape="rect" ymailto="mailto:gdal-dev@lists.osgeo.org" target="_blank" href="mailto:gdal-dev@lists.osgeo.org"><span style="font-size:11.0pt;">gdal-dev@lists.osgeo.org</span></a><span style=""><br clear="none">
</span><b><span style="font-size:11.0pt;">Aihe:
</span></b><span style="font-size:11.0pt;">Re: VS: [gdal-dev] How to replace an osm key?</span><span style=""></span></div>
</div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5053">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478944862997_5052">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478886502261_12701">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478886502261_12791">
<div class="yiv1051054434MsoNormal" style="margin-bottom:12.0pt;background:white;"><span style="font-size:10.0pt;" lang="EN-US">Hi Jukka,</span></div>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_7336">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">I have just tried to rename the fields with SQL. Here is the syntax:</span></div>
</div>
<blockquote id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_9241" style="margin-top:5.0pt;margin-bottom:5.0pt;">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_7338">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">"C:\gdal\ogr2ogr.exe" --config OSM_USE_CUSTOM_INDEXING NO -skipfailures -f "ESRI Shapefile"
<b id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_9338">-dialect sqlite -sql "select geometry, building as bldg, 'building:levels' as bldg_level, height as height from multipolygons"</b> output_shps input.osm</span></div>
</div>
</blockquote>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_9230">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">It raises the following error message:</span></div>
</div>
<blockquote id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_11287" style="margin-top:5.0pt;margin-bottom:5.0pt;">
<div id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_11219">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">ERROR 1: In ExecutableSQL<>: sqlite3_prepare<select geometry, building as bldg, 'building:levels' as bldg_level, height
as height from multipolygons>:<br id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_11272" clear="none">
no such column: height</span><span style="font-size:10.0pt;" lang="EN-US"></span></div>
</div>
</blockquote>
<div id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_11169">
<div class="yiv1051054434MsoNormal" style="background:white;"><span style="font-size:10.0pt;" lang="EN-US">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.<br clear="none">
<br clear="none">
So why is the upper <i id="yiv1051054434yui_3_16_0_ym19_1_1478907638248_13178">"no such column: height"</i> error message raised?<br clear="none">
<br clear="none">
Is something wrong with the SQL syntax?<br clear="none">
<br clear="none">
Thank you for the reply.<br clear="none">
<br clear="none">
</span><span style="font-size:10.0pt;">Kind regards,<br clear="none">
Djordje </span></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div></div>
</div>
</div></div><br><br></div> </div> </div> </div></div></body></html>