[gdal-dev] ogr2ogr project/transform CRS in the same database table
Hector muro
muro.hector at gmail.com
Wed Dec 2 03:46:14 PST 2020
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/d6f87a73/attachment-0001.html>
More information about the gdal-dev
mailing list