Sea routes network dataset

Question

I am now working on a system to perform routing on a network of shipping lanes. See this dataset that I have as the base: http://geocommons.com/maps/109850 I have postgres and pgrouting up and running and I was able to run basic queries on the above dataset after importing it to postgres/postgis. However I have encountered some problems for which I seek advice from you.

1. A lot of lines have a duplicate, so say line A goes from source id 1 to source id 2 and line B goes from source id 2 to source id 1 and they overlap completely. This can be experienced a lot of times, and frankly, I would be happy to get rid of them so that finally I could have a network with only one polyline per direction.

2. I discovered that the distance value attributes of the referenced dataset are false. I noticed this when I checked the dataset with Global Mapper by using the Info tool. In the window that pops up, you can see the geometry textbox with bounds and length data. For a line I inspected that displayed a length value of 5300 some kilometres while the max value in the length column of the dataset was 110. I seek for a way to acquire those polyline length values, and assign them to a new attribute column. I have already found posts about how to do this in qgis and arcgis, but neither of them provided the results I wanted to get. The route dataset is in WGS84, latlon

I hope the lines above make sense, and I appreciate any help! Thanks in advance!

Tamas

Answer 1

The first step of removing duplicate geometry is to run a query like this:

DELETE FROM lanes WHERE lanes.gid < (SELECT MAX(l.gid) FROM lanes As l WHERE l.the_geom && lanes.the_geom AND ST_Equals(lanes.the_geom, l.the_geom));

When I execute that on the same dataset as you, it removes 1984 geometries. You could also remove geometries that have equal from/to fields, but I didn't because there might be more than one valid route between nodes, for instance if there are depth restrictions due to shoals or wrecks.

As for the length column, it is the length in degrees. Now this doesn't really make any sense because degrees aren't a measure of length, they're a measure of angular separation. So to calculate the length in metres over the WGS84 spheroid, you can use:

SELECT "Length0" as degrees, ST_Length2D_Spheroid(the_geom, 'SPHEROID["WGS84",6378137,298.257223563]') as metres FROM lanes WHERE gid = 4512;

Which returns the length for a trans-Atlantic route:

 degrees |      metres
---------+------------------
  77.946 | 6571424.23409884

I've added an extra column and calculated the length using the ST_Length2D_Spheroid() function:

ALTER TABLE lanes ADD COLUMN length_m integer;
UPDATE lanes SET length_m = ROUND(ST_Length2D_Spheroid(the_geom, 'SPHEROID["WGS84",6378137,298.257223563]'));

Here I've just rounded off to the nearest metre, but there's no reason not to store them as double precision values should you need micron accuracy.

As an addendum, the regular 10-degree grid that covers most of the globe will not necessarily return the shortest route because they're no great-circle lines but rhumb lines. That said, navigators sometimes use rhumb lines for ease of drawing them on a chart, although GPS usually removes this need nowadays.

Also, you will have issues if you want to cross the international date line because there is no topological connection between nodes either side of the map. This is an area I don't know much about, but you may want to query for coincident nodes and add lines with 0 in the length_m column joining the two nodes.