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.
I saw the same thing. I was able to view it in QGIS with a bit of geometry fixing.
If you run Vector > Geometry Tools > Check Geometries
, and write the corrected geometry to a new shapefile with the Output Vector Layer
options, you get this...
This is core functionality but was only added fairly recently (2.12, I think) so it's worth checking if you have this available. I did this on 2.14.0 Essen.
EDIT
It turns out you need to manually install this plugin, it's not in core (I saw it was implemented as an .so - the Linux equivalent of a .dll, and wrongly assumed it was part of QGIS... just means it was done in C++)
You can find it in the Plugins manager under Geometry Checker. According to this blog post the fixing functionality was added in 2.12.
Best Answer
Converting polylines to polygons is not as straight forward operation as it may seem. The "polyline layer" in your example looks to be EXTREMELY complex. The only way for any polyline to polygon operation will succeed is if the all polylines are closed and simple. If there are any dangling lines, duplicate path segments or polylines that are not closed then a polygon will not be created.
Ensure all polylines are simple and closed and try again.