[gdal-dev] ogr2ogr project/transform CRS in the same database table
Rahkonen Jukka (MML)
jukka.rahkonen at maanmittauslaitos.fi
Wed Dec 2 03:58:51 PST 2020
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<mailto: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<mailto: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<mailto:muro.hector at gmail.com>>
Lähetetty: keskiviikko 2. joulukuuta 2020 13.02
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Kopio: gdal-dev at lists.osgeo.org<mailto: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<mailto: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<mailto:muro.hector at gmail.com>>
Lähetetty: keskiviikko 2. joulukuuta 2020 12.37
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Kopio: gdal-dev at lists.osgeo.org<mailto: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<mailto: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<mailto:muro.hector at gmail.com>>
Lähetetty: keskiviikko 2. joulukuuta 2020 12.13
Vastaanottaja: Rahkonen Jukka (MML) <jukka.rahkonen at maanmittauslaitos.fi<mailto:jukka.rahkonen at maanmittauslaitos.fi>>
Kopio: gdal-dev at lists.osgeo.org<mailto: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<mailto: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@<mailto: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<mailto: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<mailto:gdal-dev at .osgeo>
>> https://lists.osgeo.org/mailman/listinfo/gdal-dev
>>
>
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at .osgeo<mailto: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<mailto: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/1dc54821/attachment-0001.html>
More information about the gdal-dev
mailing list