Problem loading OS OpenData multipoint shapefile into PostGIS

Question

I am trying to load the OS OpenData Strategi shapefile dataset into a PostGIS database. I have had no problems with the polyline and polygon shapefiles, but I am unable to get the point shapefiles to load.

I am using the following shp2pgsql command line to create the table and load the data:

shp2pgsql -c -I -s 27700 admin_font_point strategi_point | psql -d opendata

The error I get is:

Shapefile type: MultiPoint
Postgis type: MULTIPOINT[2]
SET
SET
BEGIN
NOTICE:  CREATE TABLE will create implicit sequence "strategi_point_gid_seq" for serial column "strategi_point.gid"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "strategi_point_pkey" for table "strategi_point"
CREATE TABLE
                         addgeometrycolumn                         
-------------------------------------------------------------------
public.strategi_point.the_geom SRID:27700 TYPE:MULTIPOINT DIMS:2 
(1 row)

ERROR:  new row for relation "strategi_point" violates check constraint "enforce_geotype_the_geom"
ERROR:  current transaction is aborted, commands ignored until end of transaction block

The last error just repeats for each insert. The generated SQL looks like:

SET CLIENT_ENCODING TO UTF8;
SET STANDARD_CONFORMING_STRINGS TO ON;
BEGIN;
CREATE TABLE "strategi_point" (gid serial PRIMARY KEY,
"code" int4,
"legend" varchar(42),
"file_name" varchar(16),
"number" numeric(11,0),
"name" varchar(180),
"number0" varchar(64),
"admin_name" varchar(50),
"type" varchar(40),
"ferry_from" varchar(50),
"ferry_to" varchar(50),
"ferry_time" varchar(10),
"ferry_type" varchar(20),
"restrictio" varchar(20),
"access" varchar(30),
"amended" date,
"usage" varchar(64),
"location" varchar(30),
"gis" varchar(80),
"owner" varchar(60),
"north" varchar(60),
"south" varchar(60),
"east" varchar(60),
"west" varchar(60),
"clockwise" varchar(60),
"anticlockw" varchar(60),
"imperial" int4,
"metric" int4);
SELECT AddGeometryColumn('','strategi_point','the_geom','27700','MULTIPOINT',2);
INSERT INTO "strategi_point" ("code","legend","file_name","number","name","number0","admin_name","type","ferry_from","ferry_to","ferry_time","ferry_type","restrictio","access","amended","usage","location","gis","owner","north","south","east","west","clockwise","anticlockw","imperial","metric",the_geom) VALUES ('5734','Administrative Detached Attribute Point','gb_north','5307',NULL,NULL,'ISLE OF MAN',NULL,NULL,NULL,NULL,NULL,NULL,NULL,'20000413',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'-9999','-9999','0101000020346C000000000000302A0A4100000000304F1C41');

and then lots more inserts all looking much the same.

I have tried all sorts of variations; with and without SRID; with and without .prj file; created a new test database with no other data; using simple geometries; but all to no effect.

I have made zip file of shapefile available if anyone has the time to try this themselves:

http://www.passback.org.uk/tmp/admin_font_point.zip

I am using a fully up to date Fedora 14, Postgres 8.4.6, PostGIS 1.5.1.

Some updated notes based on the suggestion to use SPIT:

If I use shp2pgsql in "create table only" mode I get a table with the following constraints:

Check constraints:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_geotype_the_geom" CHECK (geometrytype(the_geom) = 'MULTIPOINT'::text OR the_geom IS NULL)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 27700)

Looking at the table created by SPIT I get slightly different constraints:

Check constraints:
    "enforce_dims_the_geom" CHECK (st_ndims(the_geom) = 2)
    "enforce_srid_the_geom" CHECK (st_srid(the_geom) = 27700)

I guess I could use shp2pgsql -p to create the table and then use psql to remove the enforce_geotype_the_geom constraint and my bulk import from the command line using shp2pgsql -a should work.

I would still like to understand why that constraint is causing a problem?

Thanks,

Keith.

Answer 1

As suggested by other answers, the problem is related with the POINT and MULTIPOINT types. shp2pgsql (and ogrinfo) detects the geometry type as MULTIPOINT, but then, when creating the EWKB representation of the geometry (the value to insert in the_geom column), it seems to be generating a POINT geometry:

select geometrytype('0101000020346C000000000000302A0A4100000000304F1C41');
--POINT

(I took the first generated record)

According to the shp2pgsql man page, there is a flag that should force new geometries to be created simple, but apparently, it does not work on points:

   -S     Generate simple Geometries instead of MULTIgeometries. Shape files don't differ between LINESTRINGs and MULTILINESTRINGs, so shp2pgsql generates MUL‐
          TILINESTRINGs  by  default. This switch will produce LINESTRINGs instead, but shp2pgsql will fail when it hits a real MULTILINESTRING. The same works
          for POLYGONs vs. MULTIPOLYGONs.

Instead of dropping the offending constraint, I will suggest changing the geometry column in the generated SQL script before executing it with psql:

SELECT AddGeometryColumn('','strategi_point','the_geom','27700','POINT',2);

This way you will get your records loaded, and at the same time keep a constrain that may ensure your data integrity in the future.

Answer 2

I don't know why you are getting that error, but I just tried to load your zipped shapefile using the SPIT plugin in QGIS, and it imported just fine. This surprised me as I assumed SPIT was just a gui for shp2pgsql! I am using QGIS 1.6 on windows but it should work just as well on fedora.

Hope this helps

Jo

Answer 3

Based on the pointers from @Archaogeek and @underdark I've come up with a solution.

Use shp2pgsql to create the basic table structure:

shp2pgsql -p -s 27700 admin_font_point strategi_point | psql -d opendata

Fire up psql and remove the offending constraint:

$ psql -d opendata
psql (8.4.6)
Type "help" for help.

opendata=# ALTER TABLE strategi_point DROP CONSTRAINT enforce_geotype_the_geom;
ALTER TABLE

Then go back to using shp2pgsql to bulk load all my point data (driven by a shell script):

shp2pgsql -a -s 27700 admin_font_point strategi_point | psql -d opendata

I would still like to understand the problem with that constraint.

Keith.

Answer 4

This is now fixed on PostGIS trunk, though I'm not sure what release it will wind up in.

-S now works for MULTIPOINT -> POINT (http://trac.osgeo.org/postgis/ticket/779)

And single-vertex points in a multipoint shapefile now correctly load as single-vertex multipoints rather than trying to insert a POINT. (http://trac.osgeo.org/postgis/ticket/864)

Answer 5

I think the problem is that there probably is some MULTIPOINT features in the shape, and because of that, shp2pgsql have to use MULTIPOINT instead of POINT. Then when a POINT comes instead of MULTIPOINT there is a constraint problem.

I had expected an option to force all points to MULTIPOINT, and I thought there where, but I can not find it in documentation. There is a function to do it at ones it is loaded in the database: ST_Multi, but that is of little help when trying to load it.

I guess this must have been discussed in the PostGIS lists, but I couldn't find it easily.

/Nicklas