<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">G'day all,<div><br></div><div>I'm merging a number of data sources, and have endured a fair bit of learning!</div><div><br></div><div>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 :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'">shp2pgsql -s 4326 /Users/19022662/Geodata//LAO/roads gis_roadsl | psql -U gms_web prices</font></div><div><br></div><div>which gave me :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'"> f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type </font></div><div><font class="Apple-style-span" face="'Courier New'">-----------------+----------------+----------------+-------------------+-----------------+-------+-----------------</font></div><div><font class="Apple-style-span" face="'Courier New'"> | public | gis_roadsl | the_geom | 2 | 4326 | MULTILINESTRING</font></div><div><br></div><div>I then repeated this for some other countries, and created a new table :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'">create table gis_roads (gid serial unique, country varchar(2), origin varchar(10), name varchar(16), roadtype int);</font></div><div><font class="Apple-style-span" face="'Courier New'">SELECT AddGeometryColumn( 'gis_roads', 'the_geom', 4326, 'MULTILINESTRING', 2);</font></div><div><font class="Apple-style-span" face="'Courier New'">INSERT INTO gis_roads (country, origin, name, roadtype, the_geom) SELECT 'LA', 'OU', nam, rdlntype, the_geom FROM gis_roadsl;</font></div><div><div><br></div></div></div><div>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 !) :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'">ogr2ogr -f "PostgreSQL" PG:dbname=prices /Users/19022662/Geodata/KHM/COVERS/PROJECT/RD_LIN -nln gis_roadsk -overwrite -t_srs EPSG:4326</font></div><div><br></div><div>gives me :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'"> f_table_catalog | f_table_schema | f_table_name | f_geometry_column | coord_dimension | srid | type </font></div><div><font class="Apple-style-span" face="'Courier New'">-----------------+----------------+----------------+-------------------+-----------------+-------+-----------------</font></div><div><font class="Apple-style-span" face="'Courier New'"> | public | gis_roadsl | the_geom | 2 | 4326 | MULTILINESTRING</font></div><div><div><font class="Apple-style-span" face="'Courier New'"> | public | gis_roadsv | the_geom | 2 | 4326 | MULTILINESTRING</font></div><div></div><div><font class="Apple-style-span" face="'Courier New'"> | public | gis_roadsc | the_geom | 2 | 4326 | MULTILINESTRING</font></div><div></div><div><font class="Apple-style-span" face="'Courier New'"> | public | gis_roadsk | wkb_geometry | 2 | 4326 | LINESTRING</font></div><div><br></div></div></div></div><div>and then added it to the table :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'">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);</font></div><div><br></div><div>and got this message back :</div><div><br></div><div><div><font class="Apple-style-span" face="'Courier New'">ERROR: new row for relation "gis_roads" violates check constraint "enforce_geotype_the_geom"</font></div><div><br></div><div>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.</div><div><br></div><div>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).</div><div><br></div><div>In the event they are not the same, I have a few questions :</div><div><br></div><div>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)</div><div><br></div><div>2. is ST_Multi() the best function to use to import them?</div><div><br></div><div>3. Is there any reason I wouldn't want to mix the two data types... are linestrings more efficiently handled etc.</div><div><br></div><div>cheers</div><div><br></div><div>Ben</div><div><br></div><div><br></div></div></div></div></div><div><br></div><div><br></div><div><br><div apple-content-edited="true"> <span class="Apple-style-span" style="border-collapse: separate; border-spacing: 0px 0px; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; text-align: auto; -khtml-text-decorations-in-effect: none; text-indent: 0px; -apple-text-size-adjust: auto; text-transform: none; orphans: 2; white-space: normal; widows: 2; word-spacing: 0px; "><div style="word-wrap: break-word; -khtml-nbsp-mode: space; -khtml-line-break: after-white-space; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">-- </div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Ben Madin</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">REMOTE INFORMATION</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">t : +61 8 9192 5455</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">f : +61 8 9192 5535</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">m : 0448 887 220</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; ">Broome WA 6725</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><a href="mailto:ben@remoteinformation.com.au">ben@remoteinformation.com.au</a></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; min-height: 14px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; "><span class="Apple-tab-span" style="white-space:pre"><span class="Apple-style-span" style="white-space: pre; "> </span></span>Out here, it pays to know...</div></div><br class="Apple-interchange-newline"></span> </div><br></div></body></html>