[gdal-dev] ogr2ogr project/transform CRS in the same database table

[jratike80]

I would say that it is best to still use ogrinfo. You did not tell what is
your database so I can't tell the exact recipe for you but you will learn
it. You need to know that there are different SQL dialects for GDAL: OGR
SQL, SQLite, indirect_SQLite, and the native SQL for your database.  They
are all documented.  If your database does not have native ST_Transform
support then you take it from SQLite/Spatialite. For overriding the native
SQL of your database you may need to use -dialect indirect_SQLite.

Here is another example with a shapefile. A combination of OGR SQL and
SQLite dialects is needed.

Add a new column
ogrinfo -sql "alter table states add wkt_geom text" states.shp

There is space for only 254 charactes in a shapefile and that is not enough
for big polygons but we can do at least something with a little trick. 

ogrinfo -dialect sqlite -sql "update states set
wkt_geom=ST_AsText(ST_Transform(ST_Centroid(geometry),3857))" states.shp

Check what we got
ogrinfo states.shp -al
...
SERVICE (Real) = 637487.000000000
  MANUAL (Real) = 302635.000000000
  P_MALE (Real) = 0.496000000
  P_FEMALE (Real) = 0.504000000
  SAMP_POP (Real) = 736744.000000000
  wkt_geom (String) = POINT(-13405860.170799 6003812.424048)
  MULTIPOLYGON (((-122.400749 48.225395,-122.461586 48.228542,-122.453156
48.128674,-122.360077 48.06028,-122.513245 48.134155,-122.540802
48.21064,-122.507858 48.253971,-122.403137 48....


-Jukka Rahkonen- 




hectormauer wrote
> Oh, I see. That's great.
> 
> But what if the Database does not have ST_Transform support? Could we
> still
> achieve a similar approach by means of ogr2ogr?
> 
> (I should have mentioned this in my first email).
> 
> Thanks again!
> Hector
> 
> On Tue, 1 Dec 2020 at 20:14, jratike80 <

> jukka.rahkonen@

> >
> wrote:
> 
>> Hi,
>>
>> No, unfortunately it is not possible with one ogr2ogr command.
>>
>> You need two commands and you must use ogrinfo. Here an example about
>> adding
>> another geometry column and populating it with EPSG:3857 version of
>> original
>> EPSG:4326 geometries.
>>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user
>> password=password" -sql "alter table states add column geom2 geometry"
>>
>> ogrinfo PG:"host=localhost port=5432 dbname=test user=user
>> password=password" -sql "update states set
>> geom2=ST_Transform(wkb_geometry,3857)"
>>
>> You can do all that is possible with SQL also with ogrinfo.
>>
>>
>> -Jukka Rahkonen-
>>
>>
>>
>> hectormauer wrote
>> > Hi all,
>> >
>> > I was wondering if someone knew if it's possible to project or
>> transform
>> > within the same table in a database, using ogr2ogr.
>> >
>> > What I mean is, I have a table with fields: id, id2, geom_wkt_27700 and
>> I
>> > would like, using a single command to create an extra column
>> > "geom_wkt_4326" for instance. It does not have to be of type geometry,
>> but
>> > could be text for instance.
>> >
>> > I guess the other plausible solution would be to create a temporary
>> table
>> > with that transformation on it and reference it to the first one by id
>> for
>> > instance.
>> >
>> > Any help is appreciated!
>> >
>> > Thanks,
>> > Hector
>> >
>> > _______________________________________________
>> > gdal-dev mailing list
>>
>> > gdal-dev at .osgeo
>>
>> > https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>>
>>
>>
>>
>> --
>> Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html
>> _______________________________________________
>> gdal-dev mailing list
>> 

> gdal-dev at .osgeo

>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
> 
> _______________________________________________
> gdal-dev mailing list

> gdal-dev at .osgeo

> https://lists.osgeo.org/mailman/listinfo/gdal-dev





--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html