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

Hector muro muro.hector at gmail.com
Wed Dec 2 04:07:45 PST 2020


Hi,

Thanks for that.

The first query returns:
  sqlite_version() (String) = 3.7.17

The latter returns an error (no such function: spatialite_version), so I
gess my gdal installation is not set with Spatialite, but if I do this:
 ogrinfo --formats | grep -i spatialite
  SQLite -vector- (rw+v): SQLite / Spatialite

Is there another separate driver for Spatialite only? I had assumed it came
with the SQLite itself. should I configure GDAL installation with:
"--with-spatialite=ARG" ?

I am running this on CentOS , GDAL 3.0.4 and I installed it from source.

Thanks and regards
Hector

On Wed, 2 Dec 2020 at 11:58, Rahkonen Jukka (MML) <
jukka.rahkonen at maanmittauslaitos.fi> wrote:

> Hi,
>
> Hi,
>
>
>
> What do you get from ogrinfo by having
>
> -sql "select sqlite_version()"
>
> and
>
> -sql "select spatialite_version()"
>
>
>
> If the first query returns something like sqlite_version() (String) =
> 3.24.0 it confirms that you are really running SQLite. It the latter
> command does not return anything it means that your GDAL is built without
> Spatialite or there is something wrong with loading the Spatialite
> extension. The good result would be something like spatialite_version()
> (String) = 4.3.0-RC1 or higher.
>
>
>
> I suppose you are on Linux. What Linux? What is your GDAL version? How did
> you install it?
>
>
>
> -Jukka
>
>
>
> *Lähettäjä:* Hector muro <muro.hector at gmail.com>
> *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.46
> *Vastaanottaja:* Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi
> >
> *Kopio:* gdal-dev at lists.osgeo.org
> *Aihe:* Re: [gdal-dev] ogr2ogr project/transform CRS in the same database
> table
>
>
>
> Hi again,
>
>
>
> Okey, I think I got it. And you were right, it was getting the  quotes
> correctly. I needed to single quote the 'Schema.Table'. Now the error says
> that there is no such function "ST_Transform" when using sqlite dialect!!
>
>
>
> ogrinfo "$connect_string" -dialect sqlite -sql "update 'XXX.xxx' set xxx =
> ST_AsText(ST_Transform(geometry,4326))" --config
> MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO "$connect_string"
> INFO: Open of
> `MSSQL:server=localhost;database=INMWS_GEOM_TESTS;UID=dev;PWD=DevDev1234;DRIVER=ODBC
> Driver 17 for SQL Server'
>       using driver `MSSQLSpatial' successful.
> layer names ignored in combination with -sql.
> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(update 'XXX.xxx' set xxx =
> ST_AsText(ST_Transform(geometry,4326))):
>   no such function: ST_Transform
>
>
>
> What am I missing?
>
>
>
> On Wed, 2 Dec 2020 at 11:19, Hector muro <muro.hector at gmail.com> wrote:
>
> Hi,
>
>
>
> Yeah, indirect_sqlite returns the same as using no dialect.
>
>
>
> And if I use the dialect sqlite with the top function I get a syntax error;
>
>
>
> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select top(1) * from xxx):
>   near "from": syntax error
>
>
>
> Thanks for the effort, I appreciate it. I really did not know the ability
> to edit data via -sql in the ogrinfo command, so asking here wasn't in vane
> :)
>
>
>
> Hector
>
>
>
> On Wed, 2 Dec 2020 at 11:14, Rahkonen Jukka (MML) <
> jukka.rahkonen at maanmittauslaitos.fi> wrote:
>
> Hi,
>
>
>
> Try also with “-dialect indirect_sqlite” but it should not make difference
> in this case. Then
>
> I must give up, hopefully somebody who has access to SQL server can
> continue. Before that one more hint: If you do not define a dialect the you
> are using the native SQL server SQL dialect. SQL server understands what
> top(1) means but SQLite and OGRSQL do not. If you manage to get so far you
> would see this error with SQLite dialect:
>
>
>
> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select top(1) from
> underscore_test):
>
>   no such function: top
>
>
>
> -Jukka-
>
>
>
>
>
>
>
> *Lähettäjä:* Hector muro <muro.hector at gmail.com>
> *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.02
> *Vastaanottaja:* Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi
> >
> *Kopio:* gdal-dev at lists.osgeo.org
> *Aihe:* Re: [gdal-dev] ogr2ogr project/transform CRS in the same database
> table
>
>
>
> Hi,
>
>
>
> Sorry, no, I meant that, using the flag dialect returns an error of "Table
> not found", but using the -sql flag without dialect returns records for
> that table.
>
>
>
> Therefor I believe the "problem" is with the dialect, here:
>
>
>
> [dev at localhost ~]$ ogrinfo -dialect ogrsql -sql "select top(1) * from
> xxx" "$connect_string"
> 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)
> INFO: Open of
> `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17
> for SQL Server'
>       using driver `MSSQLSpatial' successful.
> ERROR 1: SELECT from table xxxfailed, no such table/featureclass.
>
> [dev at localhost ~]$ ogrinfo -dialect sqlite -sql "select top(1) * from
> xxx" "$connect_string"
> 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)
> INFO: Open of
> `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17
> for SQL Server'
>       using driver `MSSQLSpatial' successful.
> ERROR 1: In ExecuteSQL(): sqlite3_prepare_v2(select * from crown_site_data
> limit 1):
>   no such table: xxx
>
>
>
> But, if I do not set the dialect, i.e. direct sql:
>
>
>
> [dev at localhost ~]$ ogrinfo -sql "select top(1) * from xxx"
> "$connect_string"
> 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)
> INFO: Open of
> `MSSQL:server=localhost;database=xxx;UID=xxx;PWD=xxx;DRIVER=ODBC Driver 17
> for SQL Server'
>       using driver `MSSQLSpatial' successful.
>
> Layer name: SELECT
> Geometry: Unknown (any)
> Feature Count: 1
> Layer SRS WKT:
> PROJCRS["OSGB 1936 / British National Grid",
>     BASEGEOGCRS["OSGB 1936",
>
>
>
> What do you think? I wouldn't be surprised if SQL Server was blocking this
> somehow, or there was some implementation missing.
>
>
>
> Thanks
>
> Hector
>
>
>
> On Wed, 2 Dec 2020 at 10:55, Rahkonen Jukka (MML) <
> jukka.rahkonen at maanmittauslaitos.fi> wrote:
>
> Hi,
>
>
>
> Sorry, I can’t follow what did you test. Do you mean that
>
> ogrinfo -dialect SQLite -sql "select * from crown_site_data limit 1"
> "$connect_string"
>
>
>
> returns just an error? And the same with
>
> ogrinfo -dialect SQLite -sql "select * from \"crown_site_data\" limit 1"
> "$connect_string"
>
>
>
> Try also with “-dialect ogrsql” even that dialect does  not have those ST_
> functions that you need.
>
>
>
> -Jukka-
>
>
>
>
>
>
>
>
>
>
>
>
>
> *Lähettäjä:* Hector muro <muro.hector at gmail.com>
> *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.37
> *Vastaanottaja:* Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi
> >
> *Kopio:* gdal-dev at lists.osgeo.org
> *Aihe:* Re: [gdal-dev] ogr2ogr project/transform CRS in the same database
> table
>
>
>
> Hi,
>
>
>
> Yeah, I did try that, with the same result. the test with "sql" (no
> dialect) returns correct.
>
>
>
> ogrinfo -sql "select top(1) * from <table_name>" "$connect_string" :
>
>
>
> [dev at localhost ~]$ ogrinfo -sql "select top(1) * from crown_site_data"
> "$connect_string"
> 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)
> INFO: Open of
> `MSSQL:server=localhost;database=INMWS_GEOM_TESTS;UID=dev;PWD=DevDev1234;DRIVER=ODBC
> Driver 17 for SQL Server'
>       using driver `MSSQLSpatial' successful.
>
> Layer name: SELECT
> Geometry: Unknown (any)
> Feature Count: 1
> Layer SRS WKT:
> PROJCRS["OSGB 1936 / British National Grid",
>     BASEGEOGCRS["OSGB 1936",
>         DATUM["OSGB 1936",
>             ELLIPSOID["Airy 1830",6377563.396,299.3249646,
>                 LENGTHUNIT["metre",1]]],
>         PRIMEM["Greenwich",0,
>             ANGLEUNIT["degree",0.0174532925199433]],
>         ID["EPSG",4277]],
>     CONVERSION["British National Grid",
>         METHOD["Transverse Mercator",
>             ID["EPSG",9807]],
>         PARAMETER["Latitude of natural origin",49,
>             ANGLEUNIT["degree",0.0174532925199433],
>             ID["EPSG",8801]],
>         PARAMETER["Longitude of natural origin",-2,
>             ANGLEUNIT["degree",0.0174532925199433],
>             ID["EPSG",8802]],
>         PARAMETER["Scale factor at natural origin",0.9996012717,
>             SCALEUNIT["unity",1],
>             ID["EPSG",8805]],
>         PARAMETER["False easting",400000,
>             LENGTHUNIT["metre",1],
>             ID["EPSG",8806]],
>         PARAMETER["False northing",-100000,
>             LENGTHUNIT["metre",1],
>             ID["EPSG",8807]]],
>     CS[Cartesian,2],
>         AXIS["(E)",east,
>             ORDER[1],
>             LENGTHUNIT["metre",1]],
>         AXIS["(N)",north,
>             ORDER[2],
>             LENGTHUNIT["metre",1]],
>     USAGE[
>         SCOPE["unknown"],
>         AREA["UK - Britain and UKCS 49°46'N to 61°01'N, 7°33'W to 3°33'E"],
>         BBOX[49.75,-9.2,61.14,2.88]],
>     ID["EPSG",27700]]
> Data axis to CRS axis mapping: 1,2
> Geometry Column = geometry
> [...]
>
>
>
> 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.
>
>
>
> Thanks
>
> Hector
>
>
>
> On Wed, 2 Dec 2020 at 10:29, Rahkonen Jukka (MML) <
> jukka.rahkonen at maanmittauslaitos.fi> wrote:
>
> Hi,
>
>
>
> 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
>
> -sql "update \"table_name\" set …. "
>
>
>
> Simple test to verify if the problem is caused by the unquoted table name
> is to run ogrinfo with
>
> -sql "select * from table_name limit 1".
>
>
>
> -Jukka-
>
>
>
>
>
>
>
>
>
>
>
> *Lähettäjä:* Hector muro <muro.hector at gmail.com>
> *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.13
> *Vastaanottaja:* Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi
> >
> *Kopio:* gdal-dev at lists.osgeo.org
> *Aihe:* Re: [gdal-dev] ogr2ogr project/transform CRS in the same database
> table
>
>
>
> 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/37b8e2b1/attachment-0001.html>


More information about the gdal-dev mailing list