[gdal-dev] very slow sql ogr2ogr conversion

[Neil Walker]

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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20210427/56d563ad/attachment.html>