[GIS] Problem loading OS OpenData multipoint shapefile into PostGIS

ordnance-surveyos-opendatapostgisshapefile

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.

Best Answer

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.

Related Question