A simple way to convert multipoint to point in sqlite table, using gdal ogr2ogr?

Question

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?

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.