<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<!--[if !mso]><style>v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style><![endif]--><style><!--
/* Font Definitions */
@font-face
{font-family:Helvetica;
panose-1:2 11 6 4 2 2 2 2 2 4;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Verdana;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.Shkpostityyli17
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:#1F497D;}
span.Shkpostityyli18
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 2.0cm 70.85pt 2.0cm;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="FI" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">If you enclose something between single quotes in SQL it becomes a string.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">SELECT ’building’ -> selects literally the given *<b>string</b>* –building—<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">However,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">SELECT "building" -> selects the *<b>value</b>* of *<b>field</b>* –building-- (or “identifier” as is it called in SQL)<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">See for example
<a href="https://www.sqlite.org/lang_keywords.html">https://www.sqlite.org/lang_keywords.html</a><o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I tested on Windows with trunk version of GDAL that this syntax works:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">ogrinfo -dialect sqlite -sql "select geometry, "building" as bldg
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">from multipolygons where "building" is not null limit 1" hamburg-latest.osm.pbf<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">By reading this old thread
<a 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 \”<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">ogrinfo -dialect sqlite -sql "select geometry, \"building\" as bldg
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">from multipolygons where \"building\" is not null limit 1" hamburg-latest.osm.pbf<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">Djordje Spasic wrote:<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<div>
<div id="yui_3_16_0_ym19_1_1478944862997_5117">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">Thank you for the reply Jukka.<br>
<br>
Neither "building:levels" nor "height" fields were located in osmconf.ini under the "[multipolygons]".<br>
I have just added them as you suggested, and now the previously mentioned command creates the .shp file without raising an error.<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_7424">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">I tried the initial:<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="yui_3_16_0_ym19_1_1478944862997_7289">
<div id="yui_3_16_0_ym19_1_1478944862997_7225">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">-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"
<o:p></o:p></span></p>
</div>
</blockquote>
<div id="yui_3_16_0_ym19_1_1478944862997_7216">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">and<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="yui_3_16_0_ym19_1_1478944862997_7311">
<div id="yui_3_16_0_ym19_1_1478944862997_7291">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">-dialect sqlite -sql "select geometry, building as bldg,
<b id="yui_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"
<o:p></o:p></span></p>
</div>
</blockquote>
<div id="yui_3_16_0_ym19_1_1478944862997_7181">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">And they both work.<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_7441">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_7442">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">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>
</span><a href="https://www.dropbox.com/s/nmlkkzr85w225qb/sql_choosing_keys.jpg?dl=0" id="yui_3_16_0_ym19_1_1478944862997_9365"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif">https://www.dropbox.com/s/nmlkkzr85w225qb/sql_choosing_keys.jpg?dl=0</span></a><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black"><br>
<br>
Why is this happening?<br>
<br>
Kind Regards,<br>
Djordje<o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_9448">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">-----------------------<o:p></o:p></span></p>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt;background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black"><o:p> </o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_5030">
<div id="yui_3_16_0_ym19_1_1478944862997_5029">
<div id="yui_3_16_0_ym19_1_1478944862997_5028">
<div id="yui_3_16_0_ym19_1_1478944862997_5032">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">On Saturday, November 12, 2016 10:29 AM, Rahkonen Jukka (MML) <</span><a href="mailto:jukka.rahkonen@maanmittauslaitos.fi"><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif">jukka.rahkonen@maanmittauslaitos.fi</span></a><span lang="EN-US" style="font-size:10.0pt;font-family:"Arial",sans-serif;color:black">>
wrote:</span><span lang="EN-US" style="font-family:"Helvetica",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<p class="MsoNormal" style="margin-bottom:12.0pt;background:white"><span lang="EN-US" style="font-family:"Helvetica",sans-serif;color:black"><o:p> </o:p></span></p>
<div id="yui_3_16_0_ym19_1_1478944862997_5036">
<div id="yiv5516807248">
<div id="yui_3_16_0_ym19_1_1478944862997_5035">
<div id="yui_3_16_0_ym19_1_1478944862997_5034">
<div id="yui_3_16_0_ym19_1_1478944862997_5033">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Hi,<br>
<br>
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>
<br>
</span><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">-Jukka-<o:p></o:p></span></p>
</div>
</div>
<div id="yiv5516807248yqt26386">
<div id="yui_3_16_0_ym19_1_1478944862997_5037">
<div class="MsoNormal" align="center" style="text-align:center;background:white">
<span style="font-family:"Helvetica",sans-serif;color:black">
<hr size="2" width="100%" align="center">
</span></div>
<p class="MsoNormal" style="margin-bottom:12.0pt;background:white"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Lähettäjä:
</span></b><a href="mailto:issworld2000@yahoo.com" target="_blank"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">Djordje Spasic</span></a><span style="font-family:"Helvetica",sans-serif;color:black"><br>
</span><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Lähetetty:
</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">12.11.2016 3:37</span><span style="font-family:"Helvetica",sans-serif;color:black"><br>
</span><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Vastaanottaja:
</span></b><a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">Rahkonen Jukka (MML)</span></a><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">;
</span><a href="mailto:gdal-dev@lists.osgeo.org" target="_blank"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">gdal-dev@lists.osgeo.org</span></a><span style="font-family:"Helvetica",sans-serif;color:black"><br>
</span><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Aihe:
</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:black">Re: VS: [gdal-dev] How to replace an osm key?</span><span style="font-family:"Helvetica",sans-serif;color:black"><o:p></o:p></span></p>
</div>
<div id="yui_3_16_0_ym19_1_1478944862997_5053">
<div id="yui_3_16_0_ym19_1_1478944862997_5052">
<div id="yiv5516807248yui_3_16_0_ym19_1_1478886502261_12701">
<div id="yiv5516807248yui_3_16_0_ym19_1_1478886502261_12791">
<p class="MsoNormal" style="margin-bottom:12.0pt;background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">Hi Jukka,<o:p></o:p></span></p>
<div id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_7336">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">I have just tried to rename the fields with SQL. Here is the syntax:<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_9241">
<div id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_7338">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">"C:\gdal\ogr2ogr.exe" --config OSM_USE_CUSTOM_INDEXING NO -skipfailures -f "ESRI Shapefile"
<b id="yiv5516807248yui_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<o:p></o:p></span></p>
</div>
</blockquote>
<div id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_9230">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">It raises the following error message:<o:p></o:p></span></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt" id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_11287">
<div id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_11219">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:#4C76A2">ERROR 1: In ExecutableSQL<>: sqlite3_prepare<select geometry, building as bldg, 'building:levels' as bldg_level, height
as height from multipolygons>:<br id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_11272">
no such column: height</span><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black"><o:p></o:p></span></p>
</div>
</blockquote>
<div id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_11169">
<p class="MsoNormal" style="background:white"><span lang="EN-US" style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">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>
<br>
So why is the upper <i id="yiv5516807248yui_3_16_0_ym19_1_1478907638248_13178">"no such column: height"</i> error message raised?<br>
<br>
Is something wrong with the SQL syntax?<br>
<br>
Thank you for the reply.<br>
<br>
</span><span style="font-size:10.0pt;font-family:"Verdana",sans-serif;color:black">Kind regards,<br>
Djordje <o:p></o:p></span></p>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</body>
</html>