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