Converting multipoint layer to point using PostGIS?

Question

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.

Answer 1

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

Answer 2

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

• minisaurus: 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!
• Cao Minh Tu: @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.

Answer 3

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 .

• minisaurus: No - I have QGIS tho'

Answer 4

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.