Re: [gdal-dev] very slow sql ogr2ogr conversion
Hi Neil, I found a similar situation once. Are you using Linux? If so, I think the ogr2ogr MSSQLSpatial driver for Linux does not use BCP (for multiple inserts, which is why the CSV goes so fast). If not, have you tried using the MSSQLSpatial Configuration Options for the command? I don't remember exactly what they are but it's well documented. It was something on the lines of "MSSQLSPATIAL_USE_BCP" and "MSSQLSPATIAL_BCP_SIZE" Another thing I found useful was to check the SSMS Profiler to see what's going on as you run. Finally, I was forced by circumstances to use SQL Server, but if that's not your case, I strongly recommend you to get away from it :) Hope this helps, Hector On Tue, 27 Apr 2021, 19:47 Neil Walker, wrote: > Hello, > > First time user, I hope this is the right mailing list :) > > > > I have a fairly small (37mb - 160,000 linestrings) shp file and it loads > into QGIS in a couple of seconds. I want to import this into sql server, so > used the following ogr2ogr command: > > > > ogr2ogr -f "MSSQLSpatial" > "MSSQL:server=myserver;database=mydatabase;Trusted_Connection=True;" > "NZ_RoadLink.shp" -a_srs "EPSG:27700" -nln "neilshape" -progress -lco > UPLOAD_GEOM_FORMAT=wkt > > > > It's UK Ordnance Survey open roads data if anyone is interested. After 40 > minutes it got to 3% complete then I gave up. > > > So I instead saved it as a CSV and it took about 1 minute, I then imported > this CSV into sql server and that took 1 minute too. > > > > I then ran the following command and it worked ok too after a few seconds, > I don't know if it's right or not but looks ok... > > > > alter table add geom GEOMETRY > > update table set geom=replace(wkt,'Z','') > > > > So, can anyone let me know what could be causing this massive slowdown > because my database connection is fast and there are no network issues. I > have tried using all the available sql server drivers (SQL Server, default > ODBC) but it's still this slow. I actually also have 'sql server native > 11.0' but ogr2ogr refused to use this. > > > If nobody has a clue can you confirm if my removal of 'Z' in this command > is ok or what a better way is? > > > Also, I don't know if it's just the way it is but I kept getting 'invalid > database object mydatabase' until I entered the UPLOAD_GEOM_FORMAT=wkt > parameter. Clearly 'mydatabase' has nothing to do with the error... > > > Thanks. > > > ___ > 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
Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
Hi, Thanks for that. The first query returns: sqlite_version() (String) = 3.7.17 The latter returns an error (no such function: spatialite_version), so I gess my gdal installation is not set with Spatialite, but if I do this: ogrinfo --formats | grep -i spatialite SQLite -vector- (rw+v): SQLite / Spatialite Is there another separate driver for Spatialite only? I had assumed it came with the SQLite itself. should I configure GDAL installation with: "--with-spatialite=ARG" ? I am running this on CentOS , GDAL 3.0.4 and I installed it from source. Thanks and regards Hector On Wed, 2 Dec 2020 at 11:58, Rahkonen Jukka (MML) < jukka.rahko...@maanmittauslaitos.fi> wrote: > 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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.46 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *Kopio:* gdal-dev@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 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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.02 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *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][Micro
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 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 >> *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' >>
Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 13.02 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.37 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *Kopio:* gdal-dev@lists.osgeo.org > *Aihe:* Re: [gdal-dev] ogr2ogr pr
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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.37 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *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 " "$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&q
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 " "$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",40, LENGTHUNIT["metre",1], ID["EPSG",8806]], PARAMETER["False northing",-10, 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 > *Lähetetty:* keskiviikko 2. joulukuuta 2020 12.13 > *Vastaanottaja:* Rahkonen Jukka (MML) > > *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 set = > 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 set > = ST_AsText(ST_Transform(geometry),4326)): > no such table: > > > > (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...@maanmitt
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 set = 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 set = ST_AsText(ST_Transform(geometry),4326)): no such table: (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 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.0 > MANUAL (Real) = 302635.0 > P_MALE (Real) = 0.49600 > P_FEMALE (Real) = 0.50400 > SAMP_POP (Real) = 736744.0 > 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 >
Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
Oh that's a great idea. I have to use SQL Server (not by personal choice) and had given it a go with sqlite as dialect, bur was probably too focused on ogr2ogr. I also didn't know about the "indirect_SQLite" dialect. I will give a try at what you suggest. Thanks for the help :) Hector On Tue, 1 Dec 2020, 20:56 jratike80, 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.0 > MANUAL (Real) = 302635.0 > P_MALE (Real) = 0.49600 > P_FEMALE (Real) = 0.50400 > SAMP_POP (Real) = 736744.0 > 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
Re: [gdal-dev] ogr2ogr project/transform CRS in the same database table
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 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@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
[gdal-dev] ogr2ogr project/transform CRS in the same database table
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@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
[gdal-dev] Specify ODBC PATH
Hi all, I have successfully installed gdal (from source) with the MSSQLSpatial driver in a couple servers (RHEL), but there is one that is resisting and I think it's got to do with the ODBC installation. If I do the following, cat /etc/odbcinst.ini I obtain this: [PostgreSQL] Description=ODBC for PostgreSQL Driver=/usr/lib/psqlodbcw.so Setup=/usr/lib/libodbcpsqlS.so Driver64=/usr/lib64/psqlodbcw.so Setup64=/usr/lib64/libodbcpsqlS.so FileUsage=1 [MySQL] Description=ODBC for MySQL Driver=/usr/lib/libmyodbc5.so Setup=/usr/lib/libodbcmyS.so Driver64=/usr/lib64/libmyodbc5.so Setup64=/usr/lib64/libodbcmyS.so FileUsage=1 [ODBC Driver 17 for SQL Server] Description=Microsoft ODBC Driver 17 for SQL Server Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1 UsageCount=1 So, using the configure with normal flags, does not configure the installation for MSSQLSpatial, even adding the flag "--enable-driver-mssqlspatial". Then I tried with the following flag: --with-odbc=/opt/microsoft/msodbcsql17, which made the configuration prepare for odbc and mssqlspatial, but then, when running "make", I got the following error: In file included from cpl_odbc.cpp:32:0: cpl_odbc.h:39:17: fatal error: sql.h: No such file or directory #include ^ compilation terminated. make[1]: *** [cpl_odbc.lo] Error 1 make: *** [port-target] Error 2 Any ideas about which configuration am I getting wrong to get that error? Thanks and regards, Hector ___ gdal-dev mailing list gdal-dev@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] MSSQLSpatial not using BCP to load
Hi Mateusz
Thanks a lot for that. I can see that in my gdal copy it is commented out.
But I also see all these paths refer to a Windows installation. Does this
mean the BCP support is only available for a Windows installation?
Regards
Hector
On Tue, 30 Jun 2020 at 13:42, Mateusz Loskot wrote:
> On Mon, 29 Jun 2020 at 15:53, Hector muro wrote:
> >
> > As a part of a project I need to load quite big geojsons into SQL Server
> and I am using ogr2ogr to do so.
> >
> > Here is an example command I am using:
> >
> > ogr2ogr -f MSSQLSpatial
> "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;DRIVER={ODBC Driver 17 for
> SQL Server}" -append --config SPATIAL_INDEX NO --config
> MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 1 --config
> MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO -nln INM.nv_lv_ohline -nlt GEOMETRY
> -t_srs EPSG:27700 -s_srs EPSG:4326 -skipfailures -splitlistfields
> $DATA/data.json
> >
> > I found that it is very slow and I have been checking the SQL Server
> profiler (as suggested already in this thread:
> https://lists.osgeo.org/pipermail/gdal-dev/2018-May/048520.html) and I
> can confirm that it is trying to insert every record one-by-one, hence the
> slowness.
> >
> > What I can't get to understand is how to point gdal to my sql server
> installation.
> >
> > SQL Server version: 2016
> > GDAL: 3.0.4
>
> Double check your GDAL is built with MSSQL_BCP_SUPPORTED=1 macro defined
>
> https://github.com/OSGeo/gdal/blob/7ce18dfea8c2c6c2d73d5824cf1c91f6e0287a75/gdal/nmake.opt#L288-L298
>
> Best regards,
> --
> Mateusz Loskot, http://mateusz.loskot.net
> ___
> 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
[gdal-dev] Has ogr2ogr support for BCP Bulk Load on Unix?
Hi,
I've seen that there is support for BCP bulk load support since gdal 2.1.0,
but from other posts in this list and all mentions in the docs, it looks as
if this support is only available for Windows, not unix (I hope I am wrong
and my problem comes from somewhere else), e.g.:
- https://lists.osgeo.org/pipermail/gdal-dev/2018-May/048520.html
-
http://osgeo-org.1560.x6.nabble.com/gdal-dev-connect-mssql-from-linux-td5363547.html
And here:
https://gdal.org/drivers/vector/mssqlspatial.html#configuration-options
"*MSSQLSPATIAL_USE_BCP*: (From GDAL 2.1.0) Enable bulk insert when adding
features. This option requires to to compile GDAL against a bulk copy
enabled ODBC driver like SQL Server Native Client 11.0. To specify a BCP
supported driver in the connection string, use the driver parameter, like
DRIVER={SQL Server Native Client 11.0}. If GDAL is compiled against SQL
Server Native Client 10.0 or 11.0 the driver is selected automatically not
requiring to specify that in the connection string. If GDAL is compiled
against SQL Server Native Client 10.0 or 11.0 the default setting of this
parameter is TRUE, otherwise the parameter is ignored by the driver."
It all references SQL Server Native Client and even in the code I've seen
"sqlncli", whereas the driver in Linux for SQL Server is called "ODBC
Driver XX for SQL Server", e.g.:
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.2.1
(Also, what does "compile GDAL against a bulk copy enabled ODBC driver
like" mean? I have built GDAL from source, configuring it with the
flag --enable-driver-mssqlspatial)
So I am wondering how does gdal checks if there is BCP Support? Like these
lines that i.e. #ifdef MSSQL_BCP_SUPPORTED? Is it a "Windows"-check feature?
I am asking because we have gdal and SQL Server on a CentOS machine and we
certainly have BCP, SQLCMD and other SQL Server tools available, but when
using ogr2ogr to load source files into a SQL Server instance it does not
use BCP. It instead loads one-by-one and this causes the process to be
really slow.
SQL Server version: 2017
GDAL version: 3.0.4
Any help/tips will be very appreciated.
Thanks
Hector Muro
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
Re: [gdal-dev] MSSQLSpatial not using BCP to load
Hi,
Cheers for that. I tried it, even specifying a "-gt 5" for instance,
but still stuck with the single-transaction limit...
Any other suggestions?
On Mon, 29 Jun 2020 at 15:10, jratike80
wrote:
> Hi,
>
> Don't use -skipfailures. It is only possible to skip errors one by one if
> transactions also contain just one row. It is even documented in the
> Performance hints in https://gdal.org/programs/ogr2ogr.html.
>
> -Jukka Rahkonen-
>
>
>
> hectormauer wrote
> > Hi,
> >
> > As a part of a project I need to load quite big geojsons into SQL Server
> > and I am using ogr2ogr to do so.
> >
> > Here is an example command I am using:
> >
> > ogr2ogr -f MSSQLSpatial
> > "MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;DRIVER={ODBC Driver 17 for
> > SQL Server}" -append --config SPATIAL_INDEX NO --config
> > MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 1 --config
> > MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO -nln INM.nv_lv_ohline -nlt GEOMETRY
> > -t_srs EPSG:27700 -s_srs EPSG:4326 -skipfailures -splitlistfields
> > $DATA/data.json
> >
> > I found that it is very slow and I have been checking the SQL Server
> > profiler (as suggested already in this thread:
> > https://lists.osgeo.org/pipermail/gdal-dev/2018-May/048520.html) and I
> can
> > confirm that it is trying to insert every record one-by-one, hence the
> > slowness.
> >
> > What I can't get to understand is how to point gdal to my sql server
> > installation.
> >
> > SQL Server version: 2016
> > GDAL: 3.0.4
> >
> > Thanks and regards,
> >
> > Hector Muro
> >
> > ___
> > 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
[gdal-dev] MSSQLSpatial not using BCP to load
Hi,
As a part of a project I need to load quite big geojsons into SQL Server
and I am using ogr2ogr to do so.
Here is an example command I am using:
ogr2ogr -f MSSQLSpatial
"MSSQL:server=xxx;database=xxx;UID=xxx;PWD=xxx;DRIVER={ODBC Driver 17 for
SQL Server}" -append --config SPATIAL_INDEX NO --config
MSSQLSPATIAL_USE_BCP TRUE --config MSSQLSPATIAL_BCP_SIZE 1 --config
MSSQLSPATIAL_USE_GEOMETRY_COLUMNS NO -nln INM.nv_lv_ohline -nlt GEOMETRY
-t_srs EPSG:27700 -s_srs EPSG:4326 -skipfailures -splitlistfields
$DATA/data.json
I found that it is very slow and I have been checking the SQL Server
profiler (as suggested already in this thread:
https://lists.osgeo.org/pipermail/gdal-dev/2018-May/048520.html) and I can
confirm that it is trying to insert every record one-by-one, hence the
slowness.
What I can't get to understand is how to point gdal to my sql server
installation.
SQL Server version: 2016
GDAL: 3.0.4
Thanks and regards,
Hector Muro
___
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
