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

[Hector muro]

Hi again,

I've given your idea a go:

ogrinfo -dialect sqlite -sql "update <table_name> set <text_geom> =
ST_AsText(ST_Transform(geometry),4326)" --config
MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO "$connect_string"

This connects correctly, as I get this message:

INFO: Open of `MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xx;DRIVER=ODBC
Driver 17 for SQL Server'
      using driver `MSSQLSpatial' successful.

But it can't read/find the table in the update statement:

ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(update <table_name> set
<text_geom> = ST_AsText(ST_Transform(geometry),4326)):
  no such table: <table_name>

(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).

Thanks again,
Hector

On Tue, 1 Dec 2020 at 20:56, jratike80 <jukka.rahkonen at maanmittauslaitos.fi>
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
> >>
> >> > 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
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20201202/fedaa23c/attachment.html>