Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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

Any help appreciated

Thanks

Mini

share|improve this question
add comment

4 Answers 4

up vote 4 down vote accepted

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

share|improve this answer
    
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 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. –  Cao Minh Tu Mar 22 '13 at 7:29
add comment

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
share|improve this answer
add comment

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 .

share|improve this answer
    
No - I have QGIS tho' –  minisaurus Mar 21 '13 at 9:13
add comment

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?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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