I have recently spent a good deal of time attempting to navigate my way around sql command line code using ogr2ogr, within the current release of gdal. As per the title to this post I need to convert multipoint to point for a sqlite table using open source command line tools. So qgis or arcgis tools, etc are not possible solutions. I am not a programmer so I am looking for as direct a command line solution as possible, which can be scripted. I have explored saga gis command line options but the appropriate algorithm seems to not accept sqlite input? My best hope so far appears to be ogr2ogr in gdal. I have seen many examples of postgis code online (indeed, on stack exchange) which is supposed to facilitate this relatively simple operation, but I have been unable to rejig the code in sqlite to make it work for my input/desired output (again, sqlite), if indeed this is possible? My current command is:

ogr2ogr -gt 65536 -nlt POINT -f SQLite -spat -0.4 51.42 0.29 52.65 -dsco SPATIALITE=YES output.sqlite input.sqlite -nln outputname -dialect sqlite -sql "SELECT (ST_DumpPoints(geometry)) AS geometry from input"

I get a 'function does not exist' error.

Why do I get this?

1 Answer 1


I would start by reading the ogr2ogr manual http://www.gdal.org/ogr2ogr.html and then having a try with -explodecollections

-explodecollections: (starting with GDAL 1.8.0) produce one feature for each geometry in any kind of geometry collection in the source file

Basic command could be like

ogr2ogr -f SQLite -dsco spatialite=yes output.sqlite input.sqlite layer_name -explodecollections

If you can have other sort of geometries than points or multipoints in the input layer you may want to add some SQL for getting rid of polygons etc.

  • 1
    Thanks for this. I had perused the literature in some detail but missed '-explodecollections'. I often find gdal lit references to be rather oblique. Further, adding -unsetFid solved the subsequent 'unique constraint failed' error caused by output points using identifiers that were no longer so unique! Had I not used -unsetFid the operation would have either failed or I would have had to use -skipfailures, which would have removed the offending points from the output.
    – ctlgeowp
    Apr 6, 2017 at 18:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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