Oh that's a great idea. 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.
I will give a try at what you suggest. Thanks for the help :) Hector On Tue, 1 Dec 2020, 20:56 jratike80, <[email protected]> wrote: > 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 > >> > >> > [email protected] > >> > >> > 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 > >> > > > [email protected] > > >> https://lists.osgeo.org/mailman/listinfo/gdal-dev > >> > > > > _______________________________________________ > > gdal-dev mailing list > > > [email protected] > > > 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 > [email protected] > https://lists.osgeo.org/mailman/listinfo/gdal-dev >
_______________________________________________ gdal-dev mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/gdal-dev
