Load multi point shapefiles into postgresql

Question

I am so curious to know how I can change the constraint to accept multipoint when I try to use pgsql to load shap files. I searched a lot, but I could not find the answer. My command line is:

C:\Program Files\PostgreSQL\9.0\bin>shp2pgsql -p -c -s 2400 -W LATIN1 C:\Projects\Mandana\OBO\Oversiktskartans_texter.shp | psql -U postgres -d postgis

I would appreciate if you tell me how we can add constraint here to accept multipoint.

Answer 1

According to the shp2pgsql man entry, the command create MULTI* by default :

-S

Generate simple Geometries instead of MULTIgeometries. Shape files don't differ between LINESTRINGs and MULTILINESTRINGs, so shp2pgsql generates MULTILINESTRINGs 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.

Answer 2

I am a bit confused. In your shp2pgsql command you define the parameters -p which:

Only produces the table creation SQL code, without adding any actual data. This can be used if you need to completely separate the table creation and data loading steps.

AND -c which:

Creates a new table and populates it from the Shape file. This is the default mode.

I think these are mutually exclusive? Is your table populated with data or you just want to create the structure of the table?

In either case, once the table is created it should also have created a contraint which controls the type of spatial data allowed (point, multipoint, line, etc). The constraint is usually named enforce_geotype_. So you can drop the constraint and create a new one indicating the allowable spatial types (this is assuming that the geometries currently in the table ARE of this type) e.g.

ALTER TABLE Oversiktskartans_texter DROP CONSTRAINT enforce_geotype_shape;

and then:

ALTER TABLE Oversiktskartans_texter   ADD CONSTRAINT enforce_geotype_shape
CHECK (geometrytype(shape) = 'MULTIPOINT'::text);