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.

MappaGnosis · Answer 1 · 2013-03-21T09:25:07.727

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

score 12 · Accepted Answer · answered Mar 22 '13 at 01:50

12

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

answered Mar 22 '13 at 01:50

Cao Minh Tu

1,994
4
19
33

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 '13 at 06: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. – Cao Minh Tu Mar 22 '13 at 07:29

score 0 · Answer 3 · answered Mar 21 '13 at 08:22

0

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 .

answered Mar 21 '13 at 08:22

til_b

5,074
1
19
36

No - I have QGIS tho' – minisaurus Mar 21 '13 at 09:13

score 0 · Answer 4 · edited Jun 30 '17 at 05:45

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

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

There must be an easier way.

Converting multipoint layer to point using PostGIS?

4 Answers4

Linked