<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)">
<style><!--
/* Font Definitions */
@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;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
span.Shkpostityyli18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@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 style="mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">Try also with “-dialect indirect_sqlite” but it should not make difference in this case. Then
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">I must give up, hopefully somebody who has access to SQL server can continue. Before that one more hint: If you do not define a dialect the you are using the native SQL server SQL dialect.
SQL server understands what top(1) means but SQLite and OGRSQL do not. If you manage to get so far you would see this error with SQLite dialect:<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select top(1) from underscore_test):<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"> no such function: top<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US">-Jukka-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b>Lähettäjä:</b> Hector muro <muro.hector@gmail.com> <br>
<b>Lähetetty:</b> keskiviikko 2. joulukuuta 2020 13.02<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <jukka.rahkonen@maanmittauslaitos.fi><br>
<b>Kopio:</b> gdal-dev@lists.osgeo.org<br>
<b>Aihe:</b> Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table<o:p></o:p></p>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<p class="MsoNormal">Hi,<o:p></o:p></p>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Sorry, no, I meant that, using the flag dialect returns an error of "Table not found", but using the -sql flag without dialect returns records for that table.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Therefor I believe the "problem" is with the dialect, here:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal" style="margin-bottom:12.0pt">[dev@localhost ~]$ ogrinfo <span style="color:black;background:red">
-dialect ogrsql</span> -sql "select top(1) * from xxx" "$connect_string"<br>
ERROR 1: Error initializing the metadata tables : [S0001][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'geometry_columns' in the database.(2714)<br>
INFO: Open of `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17 for SQL Server'<br>
using driver `MSSQLSpatial' successful.<br>
ERROR 1: SELECT from table xxxfailed, no such table/featureclass.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">[dev@localhost ~]$ ogrinfo -<span style="color:black;background:red">dialect sqlite</span> -sql "select top(1) * from xxx" "$connect_string"<br>
ERROR 1: Error initializing the metadata tables : [S0001][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'geometry_columns' in the database.(2714)<br>
INFO: Open of `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17 for SQL Server'<br>
using driver `MSSQLSpatial' successful.<br>
ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select * from crown_site_data limit 1):<br>
no such table: xxx<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">But, if I do not set the dialect, i.e. direct sql:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">[dev@localhost ~]$ ogrinfo -sql "select top(1) * from xxx" "$connect_string"<br>
ERROR 1: Error initializing the metadata tables : [S0001][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'geometry_columns' in the database.(2714)<br>
INFO: Open of `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17 for SQL Server'<br>
using driver `MSSQLSpatial' successful.<br>
<br>
Layer name: SELECT<br>
Geometry: Unknown (any)<br>
Feature Count: 1<br>
Layer SRS WKT:<br>
PROJCRS["OSGB 1936 / British National Grid",<br>
BASEGEOGCRS["OSGB 1936",<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">What do you think? I wouldn't be surprised if SQL Server was blocking this somehow, or there was some implementation missing.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">Thanks<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal">Hector<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<div>
<div>
<p class="MsoNormal">On Wed, 2 Dec 2020 at 10:55, Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Sorry, I can’t follow what did you test. Do you mean that</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">ogrinfo -dialect SQLite -sql "select * from crown_site_data limit 1" "$connect_string"</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">returns just an error? And the same with
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">ogrinfo -dialect SQLite -sql "select * from \"crown_site_data\" limit 1" "$connect_string"</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Try also with “-dialect ogrsql” even that dialect does not have those ST_ functions that you need.</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">-Jukka-</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;margin-bottom:12.0pt"><o:p> </o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b>Lähettäjä:</b> Hector muro <<a href="mailto:muro.hector@gmail.com" target="_blank">muro.hector@gmail.com</a>>
<br>
<b>Lähetetty:</b> keskiviikko 2. joulukuuta 2020 12.37<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Kopio:</b> <a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi,<o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Yeah, I did try that, with the same result. the test with "sql" (no dialect) returns correct.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">ogrinfo -sql "select top(1) * from <table_name>" "$connect_string" :<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">[dev@localhost ~]$ ogrinfo -sql "select top(1) * from crown_site_data" "$connect_string"<br>
ERROR 1: Error initializing the metadata tables : [S0001][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]There is already an object named 'geometry_columns' in the database.(2714)<br>
INFO: Open of `MSSQL:server=localhost;database=INMWS_GEOM_TESTS;UID=dev;PWD=DevDev1234;DRIVER=ODBC Driver 17 for SQL Server'<br>
using driver `MSSQLSpatial' successful.<br>
<br>
Layer name: SELECT<br>
Geometry: Unknown (any)<br>
Feature Count: 1<br>
Layer SRS WKT:<br>
PROJCRS["OSGB 1936 / British National Grid",<br>
BASEGEOGCRS["OSGB 1936",<br>
DATUM["OSGB 1936",<br>
ELLIPSOID["Airy 1830",6377563.396,299.3249646,<br>
LENGTHUNIT["metre",1]]],<br>
PRIMEM["Greenwich",0,<br>
ANGLEUNIT["degree",0.0174532925199433]],<br>
ID["EPSG",4277]],<br>
CONVERSION["British National Grid",<br>
METHOD["Transverse Mercator",<br>
ID["EPSG",9807]],<br>
PARAMETER["Latitude of natural origin",49,<br>
ANGLEUNIT["degree",0.0174532925199433],<br>
ID["EPSG",8801]],<br>
PARAMETER["Longitude of natural origin",-2,<br>
ANGLEUNIT["degree",0.0174532925199433],<br>
ID["EPSG",8802]],<br>
PARAMETER["Scale factor at natural origin",0.9996012717,<br>
SCALEUNIT["unity",1],<br>
ID["EPSG",8805]],<br>
PARAMETER["False easting",400000,<br>
LENGTHUNIT["metre",1],<br>
ID["EPSG",8806]],<br>
PARAMETER["False northing",-100000,<br>
LENGTHUNIT["metre",1],<br>
ID["EPSG",8807]]],<br>
CS[Cartesian,2],<br>
AXIS["(E)",east,<br>
ORDER[1],<br>
LENGTHUNIT["metre",1]],<br>
AXIS["(N)",north,<br>
ORDER[2],<br>
LENGTHUNIT["metre",1]],<br>
USAGE[<br>
SCOPE["unknown"],<br>
AREA["UK - Britain and UKCS 49°46'N to 61°01'N, 7°33'W to 3°33'E"],<br>
BBOX[49.75,-9.2,61.14,2.88]],<br>
ID["EPSG",27700]]<br>
Data axis to CRS axis mapping: 1,2<br>
Geometry Column = geometry<br>
[...]<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">So, it is not the formatting, but rather what I supposed that sqlite has no visibility over the SQL Server schema, which surprises me a bit, since it can actually connect using
the MSSQLSpatial Driver and retrieve info without it.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Thanks<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hector<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">On Wed, 2 Dec 2020 at 10:29, Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Hi,</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">I am not familiar with SQL server but your table name may require quotation marks " " and they require escaping with \ so that they do not close the SQL statement.
Try</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">-sql "update \"table_name\" set …. "
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">Simple test to verify if the problem is caused by the unquoted table name is to run ogrinfo with
</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">-sql "select * from table_name limit 1".</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US">-Jukka-</span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><span lang="EN-US"> </span><o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><b>Lähettäjä:</b> Hector muro <<a href="mailto:muro.hector@gmail.com" target="_blank">muro.hector@gmail.com</a>>
<br>
<b>Lähetetty:</b> keskiviikko 2. joulukuuta 2020 12.13<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>><br>
<b>Kopio:</b> <a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hi again,<o:p></o:p></p>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">I've given your idea a go:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">ogrinfo -dialect sqlite -sql "update <table_name> set <text_geom> = ST_AsText(ST_Transform(geometry),4326)" --config MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO "$connect_string"<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">This connects correctly, as I get this message: <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xx;DRIVER=ODBC Driver 17 for SQL Server'<br>
using driver `MSSQLSpatial' successful.<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">But it can't read/find the table in the update statement:<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(update <table_name> set <text_geom> = ST_AsText(ST_Transform(geometry),4326)):<br>
no such table: <table_name><o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">(I have tried different versions of the table name, quoting, unquoting, adding the Schema name . table name, with the same results.) I now wonder if the sqlite dialect can't really
read what's inside my SQL Server instance? Is it maybe too much what I am trying to do? (too much for SQL Server's limitations).<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Thanks again,<o:p></o:p></p>
</div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Hector<o:p></o:p></p>
</div>
</div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<div>
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">On Tue, 1 Dec 2020 at 20:56, jratike80 <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi" target="_blank">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<o:p></o:p></p>
</div>
<blockquote style="border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">I would say that it is best to still use ogrinfo. You did not tell what is<br>
your database so I can't tell the exact recipe for you but you will learn<br>
it. You need to know that there are different SQL dialects for GDAL: OGR<br>
SQL, SQLite, indirect_SQLite, and the native SQL for your database. They<br>
are all documented. If your database does not have native ST_Transform<br>
support then you take it from SQLite/Spatialite. For overriding the native<br>
SQL of your database you may need to use -dialect indirect_SQLite.<br>
<br>
Here is another example with a shapefile. A combination of OGR SQL and<br>
SQLite dialects is needed.<br>
<br>
Add a new column<br>
ogrinfo -sql "alter table states add wkt_geom text" states.shp<br>
<br>
There is space for only 254 charactes in a shapefile and that is not enough<br>
for big polygons but we can do at least something with a little trick. <br>
<br>
ogrinfo -dialect sqlite -sql "update states set<br>
wkt_geom=ST_AsText(ST_Transform(ST_Centroid(geometry),3857))" states.shp<br>
<br>
Check what we got<br>
ogrinfo states.shp -al<br>
...<br>
SERVICE (Real) = 637487.000000000<br>
MANUAL (Real) = 302635.000000000<br>
P_MALE (Real) = 0.496000000<br>
P_FEMALE (Real) = 0.504000000<br>
SAMP_POP (Real) = 736744.000000000<br>
wkt_geom (String) = POINT(-13405860.170799 6003812.424048)<br>
MULTIPOLYGON (((-122.400749 48.225395,-122.461586 48.228542,-122.453156<br>
48.128674,-122.360077 48.06028,-122.513245 48.134155,-122.540802<br>
48.21064,-122.507858 48.253971,-122.403137 48....<br>
<br>
<br>
-Jukka Rahkonen- <br>
<br>
<br>
<br>
<br>
hectormauer wrote<br>
> Oh, I see. That's great.<br>
> <br>
> But what if the Database does not have ST_Transform support? Could we<br>
> still<br>
> achieve a similar approach by means of ogr2ogr?<br>
> <br>
> (I should have mentioned this in my first email).<br>
> <br>
> Thanks again!<br>
> Hector<br>
> <br>
> On Tue, 1 Dec 2020 at 20:14, jratike80 <<br>
<br>
> <a href="mailto:jukka.rahkonen@" target="_blank">jukka.rahkonen@</a><br>
<br>
> ><br>
> wrote:<br>
> <br>
>> Hi,<br>
>><br>
>> No, unfortunately it is not possible with one ogr2ogr command.<br>
>><br>
>> You need two commands and you must use ogrinfo. Here an example about<br>
>> adding<br>
>> another geometry column and populating it with EPSG:3857 version of<br>
>> original<br>
>> EPSG:4326 geometries.<br>
>><br>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user<br>
>> password=password" -sql "alter table states add column geom2 geometry"<br>
>><br>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user<br>
>> password=password" -sql "update states set<br>
>> geom2=ST_Transform(wkb_geometry,3857)"<br>
>><br>
>> You can do all that is possible with SQL also with ogrinfo.<br>
>><br>
>><br>
>> -Jukka Rahkonen-<br>
>><br>
>><br>
>><br>
>> hectormauer wrote<br>
>> > Hi all,<br>
>> ><br>
>> > I was wondering if someone knew if it's possible to project or<br>
>> transform<br>
>> > within the same table in a database, using ogr2ogr.<br>
>> ><br>
>> > What I mean is, I have a table with fields: id, id2, geom_wkt_27700 and<br>
>> I<br>
>> > would like, using a single command to create an extra column<br>
>> > "geom_wkt_4326" for instance. It does not have to be of type geometry,<br>
>> but<br>
>> > could be text for instance.<br>
>> ><br>
>> > I guess the other plausible solution would be to create a temporary<br>
>> table<br>
>> > with that transformation on it and reference it to the first one by id<br>
>> for<br>
>> > instance.<br>
>> ><br>
>> > Any help is appreciated!<br>
>> ><br>
>> > Thanks,<br>
>> > Hector<br>
>> ><br>
>> > _______________________________________________<br>
>> > gdal-dev mailing list<br>
>><br>
>> > <a href="mailto:gdal-dev@.osgeo" target="_blank">gdal-dev@.osgeo</a><br>
>><br>
>> > <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">
https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
>><br>
>><br>
>><br>
>><br>
>><br>
>> --<br>
>> Sent from: <a href="http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html" target="_blank">
http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html</a><br>
>> _______________________________________________<br>
>> gdal-dev mailing list<br>
>> <br>
<br>
> <a href="mailto:gdal-dev@.osgeo" target="_blank">gdal-dev@.osgeo</a><br>
<br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
>><br>
> <br>
> _______________________________________________<br>
> gdal-dev mailing list<br>
<br>
> <a href="mailto:gdal-dev@.osgeo" target="_blank">gdal-dev@.osgeo</a><br>
<br>
> <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
<br>
<br>
<br>
<br>
<br>
--<br>
Sent from: <a href="http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html" target="_blank">
http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html</a><br>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><o:p></o:p></p>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</div>
</blockquote>
</div>
</div>
</body>
</html>