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.

4 Answers 4


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

You can convert multipoint layer to single points with menu Vector -> Geometry tools -> Multipart to Singleparts and import the new layer into PostGIS.

  • Yes, that's got to be easier. The only problem with Multipart to Singleparts is you have to first save the layer as a shapefile - so you lose your nice long column names, but it's certainly easier on my brain!
    – minisaurus
    Mar 22, 2013 at 6:35
  • @minisaurus: your multipoint layer may be spatialite but the result of conversion (single point layer) will be shapefile - so yes, you may lose your nice long column names but you can change them back manually in PostGIS. Mar 22, 2013 at 7:29

Do you have access to ArcGIS? There is "multipart to singlepart" in ArcGIS, see http://resources.arcgis.com/en/help/main/10.1/index.html#//00170000003r000000 .

  • No - I have QGIS tho'
    – minisaurus
    Mar 21, 2013 at 9:13

I've done it but can't remember all the steps unfortunately:

  1. ogr2ogr table to PostGIS
  2. created a new table as a copy of this table's structure
  3. dropped the geometry column in the new table
  4. created a new geometry column as point (select AddGeometryColumn etc)
  5. inserted the data from the multipoint table into the new table (this is the bit I have forgotten how I did)
  6. created a spatial index on the new tables geometry table

There must be an easier way.

Your Answer

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

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