[postgis-users] Mixing LINESTRING and MULTILINESTRING data

[Simon Greener]

Ben,

> INSERT INTO gis_roads (country, origin, name, roadtype, the_geom)
> SELECT 'KH', 'MU', userid, code, wkb_geometry FROM gis_roadsk order by
> centroid(wkb_geometry);
>
> and got this message back :
>
> ERROR:  new row for relation "gis_roads" violates check constraint
> "enforce_geotype_the_geom"

Grab the definition of this CHECK constraint by clicking on the table in pgAdminIII. Then do this:

ALTER TABLE gis_roads DROP CONSTRAINT enforce_geotype_the_geom;
ALTER TABLE gis_roads ADD CONTRAINT enforce_geotype_geom CHECK (geometrytype(the_geom) IN ('LINESTRING'::text,'MULTILINESTRING'::text) OR gethe_geomom IS NULL);

It will now allow linestrings and multilinestrings to be inserted into the table.

> 1. Is there any reason I shouldn't use the GEOMETRY type for my
> aggregate table? (There must be or there wouldn't be any other types)

No

> 2. is ST_Multi() the best function to use to import them?

Well, I would change the defintiion of the enforce_geotype_geom CHECK constraint and then import the data from the shapefiles regardless as to type. Then you could fix any wrongly typed MULTILINESTRING geometries (ie those made up of a single geometry) as follows:

UPDATE gis_roads
   SET the_geom = ST_GeometryN(the_geom, 1)
 WHERE the_geom is not null
   AND ST_NumGeometries(the_geom) = 1;

Then, you can check if there are any multilinestring geometries in your table as follows:

SELECT distinct GeometryType(the_geom) FROM gis_roads;

If you get two rows then the correct enforce_geotype_geom CHECK constraint is as above (ie two geometry types).

If you get one row then you can change your CHECK constraint to only allow that geometry (or you might decide that the table should support two types anyway).

> 3. Is there any reason I wouldn't want to mix the two data types...
> are linestrings more efficiently handled etc.

I'm not an expert on the specifics of PostGIS but if the object is a linestring then store and index it as a linestring and remove the additional handling required to extract the linestring from the multilinestring for processing and rendering. Has to be a little bit faster.

Hope this helps

S.

-- 
SpatialDB Advice and Design, Solutions Architecture and Programming,
Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional
Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, Radius Topology and Studio Specialist.
39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.
Website: www.spatialdbadvisor.com
  Email: simon at spatialdbadvisor.com
  Voice: +613 9016 3910
Mobile: +61 418 396391
Skype: sggreener
Longitude: 147.20515 (147° 12' 18" E)
Latitude: -43.01530 (43° 00' 55" S)
NAC:W80CK 7SWP3