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