[GIS] Converting multipoint layer to point using PostGIS

ogr2ogrpostgisspatialite

I have a Spatialite layer which is multipoint (following a QGIS Save As from shapefile). I need it to be PostGIS and POINT. I have tried ogr2ogr -nlt POINT and got 'new row for relation "xxx" violates check constraint "enforce_geotype_geometry"' error on insert.

I've also tried converting within PostGIS – i.e. bringing the layer in as multipoint and converting to point – no joy.

I'm using PostGIS 1.5.3 PostGreSQL 9.1.8. (considering upping to PostGIS2 after reading this: How to change the geometry type from Point to Multipoint within an existing table in PostGIS?) but would prefer to stay at 1.5 for now if there's a way round this. Happy to do this in either spatialite or PostGIS.

Best Answer

SpatialLite has no way of converting multi-geometries to single-parts itself. There are some 'CastTo' functions but they are for special cases (where your multigeometry contains a single geometry - it won't fan-out). I have seen a reference to a function in the SpatialLite GUI but never found it (perhaps you need to compile from the latest source code. I'm just using a pre-compiled binary). So your best bets are:

In PostGIS you can use ST_Dump. This is a useful function for expanding multi-geometries.
In QGIS you could use Vector->Geometry Tools->Multipart to singlepart

Related Solutions

[GIS] Split line using line/point in PostGIS 1.5

ST_Difference(master_line, break_lines) looks like it'll do this for you: http://www.postgis.org/documentation/manual-1.5/ST_Difference.html

# SELECT ST_AsText(ST_Difference(
                       ST_GeomFromText('LINESTRING(0 100, 0 -100)'),
                       ST_GeomFromText('LINESTRING(-100 0, 100 0)')));
                 st_astext                 
-------------------------------------------
 MULTILINESTRING((0 100,0 0),(0 0,0 -100))
(1 row)

PostGIS – Multipoint vs Point in PostGIS

To convert "Multipoint" to "Point", you have to use ST_Dump, for example:

SELECT (ST_Dump(the_geom)).geom AS the_POINT_geom
  FROM MULTIPOINT_table;

On the question of using "Multi" or single geometries, I use this logic:

if every geometry has different attributes -> Single
if a group of geometries have same attributes -> "Multi"

Best Answer

Related Solutions

[GIS] Split line using line/point in PostGIS 1.5

PostGIS – Multipoint vs Point in PostGIS

Related Question