<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.Shkpostityyli19
{mso-style-type:personal-compose;
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">Sorry, I can’t follow what did you test. Do you mean that<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo -dialect SQLite -sql "select * from crown_site_data limit 1" "$connect_string"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">returns just an error? And the same with <o:p>
</o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">ogrinfo -dialect SQLite -sql "select * from \"crown_site_data\" limit 1" "$connect_string"<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Try also with “-dialect ogrsql” even that dialect does not have those ST_ functions that you need.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">-Jukka-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><br>
<br>
<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 12.37<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">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"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">ogrinfo -sql "select top(1) * from <table_name>" "$connect_string" :<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 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"><o:p> </o:p></p>
</div>
<div>
<p class="MsoNormal">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"><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:29, 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"><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>
</body>
</html>