2

I'm using ogr2ogr to download features from a WFS service and save it to a sqlite database. Here's the full command:

ogr2ogr -f SQLite -gt 65536 -nln my_table_name --config OGR_WFS_USE_STREAMING NO --config OGR_SQLITE_CACHE 4096 -nlt POINT -skipfailures -append my_sqlite_file.sqlite "http://url/to/my/service"

When I run this on GDAL 2.1.2, downloading the features is extremely slow.

By the look of it, the my_sqlite_file.sqlite_journal file never grows to more than 10K and the table is constantly updated. In comparison, when using GDAL 1.11.3, my_sqlite_file.sqlite_journal grows to the excess of 400K and there are maybe 2-3 writes tops to the table.

I was able to pinpoint this problem down to the --config OGR_WFS_USE_STREAMING NO part: when I remove it from the command, I achieve similar behaviour and speeds with GDAL 1.11.3.

With the dataset I'm using, the download time grows from 30 seconds to 8 minutes.

Did something change between the two versions of the ogr2ogr?

Should I pass the --config OGR_WFS_USE_STREAMING NO part of the command somehow differently?

Would love to find a solution to this.


I experimented a bit more, still to no avail but at least I know more.

I removed all the -skipfailures, -gt and all the config options but still ogr2ogr supplied with GDAL 1.11.3 is much faster than the 2.1.x (tried 2.1.2 and 2.1.3). Looking at the memory and processor usage on windows here's what's going on.

GDAL 1.11.x:

  • data download starts, ogr2ogr never exceeds 25MB of memory, CPU usage stays around 10%
  • ogr2ogr performs the writes to the sqlite database, more writes when -gt 65536 eliminated, but still fairly quickly

GDAL 2.1.X

  • data download starts, ogr2ogr downloads all the data at once and takes around 350MB of memory, CPU usage goes all the way to 50%
  • then it hangs there performing really slow writes. If limit set to -gt 65536, then will perform first write after around 10 mins from the start

Really quite clueless to what to do here. I think the only thing I can do is file a bug report.


Using user30184's pointer on the debug I can see some differences in the process.


First of all the older version of ogr2ogr clearly states that it is doing a WFS request, but the newer version claims it's doing an HTTP request with both reporting respectively that they OGROpen succeeded in in a WFS request and an HTTP request.

Secondly, when starting the download the new version of ogr2ogr displays the following lines

SQLITE: dropping trigger ggi_mylayername_GEOMETRY

SQLITE: dropping trigger tmi_mylayername_GEOMETRY

and when I control-c the process it prints out the lines

SQLITE: restoring trigger ggi_mylayername_GEOMETRY

SQLITE: restoring trigger tmi_mylayername_GEOMETRY

all of these trigger lines were absent from the older version of ogr2ogr.


Would any of these have an effect on the sluggishness I'm experiencing with the new version?

I wonder if it loads the data in the wrong format (not WFS) and then the conversion is what is taking time and CPU power?

  • What happens if you remove -skipfailures? -gt 65536 has no effect when also -skipfailures is selected, because you can only skip the whole transaction. Therefore skipfailures effectively also sets -gt 1. But this is different thing than --config OGR_WFS_USE_STREAMING NO. I guess that giving source as WFS:"url/to/my/service" makes no difference but I would have a try. – user30184 Apr 12 '17 at 10:41
  • I tried those tips but unfortunately it didn't help. I removed -skipfailures, -gt and also all the config options and the results are still the same: the ogr2ogr with GDAL 1.11.2 is still faster to the point that it seems that GDAL 2.1.3 freezes. I'll edit the question with new info. – tarikki May 16 '17 at 12:50
  • Why do you want to set OGR_WFS_USE_STREAMING to NO? Add also --debug on to your ogr2ogr commands and if it shows some interesting differences report them. – user30184 May 16 '17 at 13:05
  • This is used as part of a legacy QGIS plugin for the well-being of which I'm responsible. OGR_WFS_USE_STREAMING NO was part of the command and I didn't really start digging into it until it stopped working correctly. Will try the --debug, thanks for the tip! – tarikki May 16 '17 at 13:10
  • Thanks the debug option was really helpful. Updated my question. – tarikki May 16 '17 at 13:42
3

Using the debug suggestion from @user30184 the problem was that the new version of ogr2ogr needs to be told the format of the data at the very beginning of the URL. When the older version was completely happy to with just

http://urltosomewhere.com/service?SERVICE=WFS

and figures out the WFS part from the end, starting from version 1.8.0 GDAL/OGR needs the be given the URL in the format

WFS:http://urltosomewhere.com/service?SERVICE=WFS

I wish the documentation was a bit better organized. I only discovered this by random googling and ending up on this page and seeing the correct format at the top of the page.

  • There's also a way to get some of that info from the command line! Typing ogrinfo --format WFS outputs a lot of info, but the neat part is under the Format Details: that has a portion that says Connection prefix: WFS: – SaultDon May 16 '17 at 20:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.