[gdal-dev] Row count limit in OGR SQL with -dialect=SQLITE

[Rahkonen Jukka]

Even Rouault wrote:
> Selon Jukka Rahkonen <jukka.rahkonen at mmmtike.fi>:

>> Hi,
>>
>> The following SQL works fine when using Spatialite as source
>>
>> osm_data>ogrinfo osm.sqlite -dialect SQLITE -sql "select * from lines
>> where highway is not null limit 20"

> Note that for a SQLite datasource, -dialect SQLite is useless since the SQL
> engine of SQLite is used by default.

>>
>> With native OSM data it fails
>> OSM_data>ogrinfo -dialect SQLITE finland.osm.pbf -sql "select * from lines
>> limit 20"
>> Had to open data source read-only.
>> INFO: Open of `finland.osm.pbf'
>>       using driver `OSM' successful.
>> ERROR 1: Too many features have accumulated in points layer. Use
>> OGR_INTERLEAVED
>> _READING=YES mode
>>
>> Why does it fail? Is the error message correct because I can read from
>> http://www.gdal.org/ogr/drv_osm.html
>> "Note : the ogr2ogr application has been modified to use that
>> OGR_INTERLEAVED_READING mode without any particular user action."
>
>> Adding --config OGR_INTERLEAVED_READING YES to ogrinfo request does not lead
>> to
>> desired result either
>>

> It fails because in the ExecuteSQL() method of the OSM driver there's some logic
> to parse the SQL statement and extract the table names that are mentioned in it.
> The OSM driver then knows which tables it must ignore to avoid them being filled
> up with features that will not be consumed and will accumulate.

> The issue is that your SQL request isn't understood by the OGR SQL engine due to
> the presence of the LIMIT clause, so the above mentionned trick isn't used and
> points are accumulated into the points layer until it reaches an error
> threshold.

> I'm not sure how to fix that. Please file a ticket about it.

I will do, I tend to use "limit 10" of something in making quick tests but OSM is 
so odd data that I was not surprised in seeing that it fails. Is this driver specific 
or related to OGR SQL engine generally?

> (So the error message is correct, but it is more aimed at developers than
> end-users, since setting OGR_INTERLEAVED_READING=YES isn't sufficient per se,
> but also requires that the code regularly "consumes" features that are filled in
> other layers.)

Next question, this one works:
OSM_data>ogrinfo -dialect SQLITE  finland.osm.pbf -sql "select name, amenity
from points where amenity is not null" -spat 24.821 60.123 25.259 60.317

But writing to csv file fails, why?
OSM_data>ogr2ogr -f csv -dialect SQLITE poi.csv finland.osm.pbf -sql "select
amenity from points where amenity is not null" -spat 24.821 60.123 25.259 60.317
csv driver failed to create poi.csv

Debug does not show any more information. More odd is that with the following 
I can select attributes from the points layer and write them into csv file
OSM_data>ogr2ogr -f csv -dialect SQLITE finland.csv osm.sqlite  -sql "select
distinct name from lines order by name" -spat 24.821 60.123 25.259 60.317

-Jukka-