sqlite version error with ogr2ogr

Question

I am trying to randomly select some points from a shape file using ogr2ogr and an SQL query via sqlite but it seems to baulk at the sqlite version:

ogr2ogr -sql "SELECT * FROM probe_2004to2012 WHERE D2004 > 0 ORDER BY RANDOM() LIMIT 10" -dialect sqlite ./Probing/probe_2004_r10.shp ./Probing/probe_2004to2012.shp

ERROR 6: The SQLite version is to old to support the SQLite SQL dialect

Given that I tried updating sqlite3 in my /usr/bin/ from the jan 2013 version and it is now the latest (Oct 2013) version and ogr2ogr still throws up this error I suspect it may be something else that's wrong. I know I can select random points in QGIS using the "Research Tools" toolbox but I would really like to be able to script this as I have a lot of random selecting to do.

-update: its seems ogr2ogr supports

-dialect sqlite3

as a valid flag and that seems to get a simplified sql query to parse but the random() selection won't get through.

After the discussion below it seems that ogr2ogr is not parsing the -dialect flag "properly". The sqlite3 flag is ignored and ogr defaults to its built in sql, which is why the simple queries work. Why the sqlite flag is coming up ERROR 6 I don't know. Yet.

Answer 1

Since GDAL/OGR 1.10, the SQLite "dialect" can be used as an alternate SQL dialect to the OGR SQL dialect. This assumes that GDAL/OGR is built with support for SQLite (>= 3.6), and preferably with Spatialite support too to benefit from spatial functions.

Source: http://www.gdal.org/ogr/ogr_sql_sqlite.html

So you need to build GDAL (>= 1.10) against SQLite (>= 3.6) and SpatiaLite (>= 3.0) in order to use the -dialect SQLite option.

In order to understand which versions of SQLite and (eventually) SpatiaLite you are using, simply execute:

ogrinfo foo.shp -dialect sqlite -sql "select sqlite_version()"
ogrinfo foo.shp -dialect sqlite -sql "select spatialite_version()"

Answer 2

My nose was right! It was a multiple path issue in OSX. I had multiple installs of ogr and gdal conflicting despite them not being on my path as defined by .bash_profile but this: https://stackoverflow.com/a/8841666/2062470 sorted me out.