1

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.

2

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()"
  • It seems that my sqlite command isn't aliased to sqlite3 and that is the source of the issue with not finding a valid sqlite. My installation of GDAL is the KyngChaos build and should have everything set correctly otherwise (no guarantees of course). – mercergeoinfo Dec 3 '13 at 9:21
  • If you execute the two queries above and post the versions, maybe we can understand where is the issue. However, the error message is well self-explanatory, so you need to update or build GDAL yourself. – Antonio Falciano Dec 3 '13 at 9:25
  • Running the commands "as is" I get the same error, if I adjust the dialect flag to sqlite3 I get ERROR 1: SQL Expression Parsing Error: syntax error – mercergeoinfo Dec 3 '13 at 9:28
  • And what says gdalinfo --version ? This is the latest GDAL complete build on KyngChaos: kyngchaos.com/files/software/frameworks/GDAL_Complete-1.10.dmg It should solve the issue. Hope this helps. – Antonio Falciano Dec 3 '13 at 9:51
  • I get GDAL 1.10.1, released 2013/08/26 – mercergeoinfo Dec 3 '13 at 10:04
1

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.

  • Can you mark this as the answer? – BradHards Jan 7 '14 at 9: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.