<div dir="auto"><div>Oh that's a great idea. <div dir="auto"><br></div><div dir="auto">I have to use SQL Server (not by personal choice) and had given it a go with sqlite as dialect, bur was probably too focused on ogr2ogr. I also didn't know about the "indirect_SQLite" dialect. </div><div dir="auto"><br></div><div dir="auto">I will give a try at what you suggest. </div><div dir="auto"><br></div><div dir="auto">Thanks for the help :) </div><div dir="auto"><br></div><div dir="auto">Hector</div><br><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 1 Dec 2020, 20:56 jratike80, <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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>
> jukka.rahkonen@<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>
>> > gdal-dev@.osgeo<br>
>><br>
>> > <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" 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" rel="noreferrer noreferrer" target="_blank">http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html</a><br>
>> _______________________________________________<br>
>> gdal-dev mailing list<br>
>> <br>
<br>
> gdal-dev@.osgeo<br>
<br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
>><br>
> <br>
> _______________________________________________<br>
> gdal-dev mailing list<br>
<br>
> gdal-dev@.osgeo<br>
<br>
> <a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" 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" rel="noreferrer noreferrer" 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" rel="noreferrer">gdal-dev@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/gdal-dev" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><br>
</blockquote></div></div></div>