I have looked at your data and the book example, the problem is that there are three invalid polygons in data that are processed in the book:
GSHHS_l_L1.shp
ID = 92-W
POLYGON ((-180.0 71.514793999999995,-179.69008299999999 71.577888999999999,-178.648889 71.577416999999997,-178.40644399999999 71.549916999999994,-177.406306 71.244167000000004,-177.877444 71.022889000000006,-179.500111 70.863749999999996,-179.93011100000001 70.979583000000005,-180.0 70.962072000000006))
ID = 486-W
POLYGON ((-180.0 -16.799126,-179.84419399999999 -16.691278,-179.80041700000001 -16.789193999999998,-179.850472 -16.878361000000002,-180.0 -16.959561))
GSHHS_l_L2.shp
ID = 7333-W
POLYGON ((-180.0 65.393473,-179.76583299999999 65.428332999999995,-179.95416700000001 65.385555999999994,-179.90972199999999 65.316389,-180.0 65.321635))
Because this is an example it would be easiest to delete those polygons from the dataset or just add one if statement in you code
if geometry.IsValid():
cursor.execute("INSERT INTO gshhs (level, geom) VALUES (%s, ST_GeomFromText(%s, 4326))", (level, wkt))
All you need if you want to run the shortest path (Dirkjtra algorithm) is a basic set if colomns listed in this workshop :
http://workshop.pgrouting.org/chapters/shortest_path.html
You need only one polylines or lines layer. The function assign vertex creates the topology.
source and target are the identifier for the first and last node of each geometry. It is used to build the topology. You have to use the funciton assign_vertex in the link above.
Then you need a colomn that is your cost. Again all explained the document above. All the algorithm except the shooting star crash if cost are negative.
You can use the reverse_cost is you want a cost from source to target and a different cost from target to node.
Algorithms A* and Dirkjtra are from node to node. The example of queries are in the link above. Shooting * goes from segment to segment. Shooting * accepts cost that are negative as a way to prevent one way of a segment to be used.
A* need additionnal fields compared to Dirkjtra, becauses it uses heuristics relying on geography. Shooting * requires even more field, for it can implements turn restrictions. Go through the document.
This website also register other tutos that are not official.
http://ebmgh.com/blog/2009/12/computing-directions-with-pgrouting/
Take it step by step, READ the documentation and for Dirkjtra and A* there is no real difficulty.
Good luck. As for the data, if you can build a topology, you're good to go.
Best Answer
This query should do the trick:
At first you select the distinct linestrings from your polylinestring column with
ST_Dump
Then you have to define the measure elements with
ST_AddMeasure
, starting with 0 (begin of the linestring) and the end of the linestring (same as the length of the linestring).Generate_series
creates a series over the this measurement by the step of 10. Here you can define"n metres"
(in this example 10 meters). Thei
value begins anew for every linestring.With
ST_LocateAlong
andST_GeometryN
you create a multidimensional point geometry.Finally you have to extract the X and Y values of this geometry and create a point from it.
The result could look like this:
EDIT
To be fair, I want to add this information: The idea and the query in this answer is extracted from a query that I'm using to create drape lines from DEMs in PostGIS. The excellent implementation of this is explained in this article from Mathieu Leplatre.