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.hec...@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.rahko...@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.hec...@gmail.com> >> *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.02 >> *Vastaanottaja:* Rahkonen Jukka (MML) < >> jukka.rahko...@maanmittauslaitos.fi> >> *Kopio:* gdal-dev@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@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@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@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.rahko...@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.hec...@gmail.com> >> *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.37 >> *Vastaanottaja:* Rahkonen Jukka (MML) < >> jukka.rahko...@maanmittauslaitos.fi> >> *Kopio:* gdal-dev@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@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.rahko...@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.hec...@gmail.com> >> *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.13 >> *Vastaanottaja:* Rahkonen Jukka (MML) < >> jukka.rahko...@maanmittauslaitos.fi> >> *Kopio:* gdal-dev@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.rahko...@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@.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@.osgeo >> >> >> https://lists.osgeo.org/mailman/listinfo/gdal-dev >> >> >> > >> > _______________________________________________ >> > gdal-dev mailing list >> >> > gdal-dev@.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@lists.osgeo.org >> https://lists.osgeo.org/mailman/listinfo/gdal-dev >> >>
_______________________________________________ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev