[postgis-users] Mixing LINESTRING and MULTILINESTRING data

[Ben Madin]

G'day all,

I'm merging a number of data sources, and have endured a fair bit of  
learning!

The basic data I had was in shapefiles, so I have use the loader  
(shp2pgsql), loaded each one for different countries and merged them  
into one table :

shp2pgsql -s 4326 /Users/19022662/Geodata//LAO/roads gis_roadsl | psql  
-U gms_web prices

which gave me :

  f_table_catalog | f_table_schema |  f_table_name  |  
f_geometry_column | coord_dimension | srid  |      type
-----------------+----------------+----------------+------------------- 
+-----------------+-------+-----------------
                  | public         | gis_roadsl     |  
the_geom          |               2 |  4326 | MULTILINESTRING

I then repeated this for some other countries, and created a new table :

create table gis_roads (gid serial unique, country varchar(2), origin  
varchar(10), name varchar(16), roadtype int);
SELECT AddGeometryColumn( 'gis_roads', 'the_geom', 4326,  
'MULTILINESTRING', 2);
INSERT INTO gis_roads (country, origin, name, roadtype, the_geom)  
SELECT 'LA', 'OU', nam, rdlntype, the_geom FROM gis_roadsl;

however, for one I had the data as arc covers? (I think that is the  
name) so, using a (in my opinion undersung) piece of software (I don't  
have access to ARC/INFO, at am never likely to !) :

ogr2ogr -f "PostgreSQL" PG:dbname=prices /Users/19022662/Geodata/KHM/ 
COVERS/PROJECT/RD_LIN -nln gis_roadsk -overwrite -t_srs EPSG:4326

gives me :

  f_table_catalog | f_table_schema |  f_table_name  |  
f_geometry_column | coord_dimension | srid  |      type
-----------------+----------------+----------------+------------------- 
+-----------------+-------+-----------------
                  | public         | gis_roadsl     |  
the_geom          |               2 |  4326 | MULTILINESTRING
                  | public         | gis_roadsv     |  
the_geom          |               2 |  4326 | MULTILINESTRING
                  | public         | gis_roadsc     |  
the_geom          |               2 |  4326 | MULTILINESTRING
                  | public         | gis_roadsk     |  
wkb_geometry      |               2 |  4326 | LINESTRING

and then added it to the table :

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"

Using the -nlt "MULTILINESTRING" option for ogr2ogr only brought the  
problem back to that step, as it then tried to insert LINESTRINGS into  
the MULTILINESTRING field.

I can see that LINESTRING is not MULTILINESTRING, but to my slowing  
mind I would have thought that a linestring might have been handled  
the same thing as a multilinestring with only one segment (maybe not).

In the event they are not the same, I have a few questions :

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)

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

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

cheers

Ben





-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20090107/6519f77e/attachment.html>