[postgis-users] Mixing LINESTRING and MULTILINESTRING data
Ben Madin
ben at remoteinformation.com.au
Tue Jan 6 17:46:11 PST 2009
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>
More information about the postgis-users
mailing list