I'm a beginner working with QGIS and PostGIS/pgRouting. The network I want to create consists of a multilinestring layer for the network itself and a point layer for the nodes of this network. The network layer has 2204 features/rows and the point layer has 2455 points. For the point layer, I also have data which allows to create the edge relationships together with costs for consideration later on and other attributes I want to combine and consider as criteria for routing.
I want to be able to create shortest paths between the points using the network layer.
This was the first script I tried to join the network and points,
select st_line_interpolate_point(st_linemerge(net.geom), (st_line_locate_point(st_linemerge(net.geom), pnt.geom))) from net,pnt;
I ran into trouble due to the multilinestring property of the network layer, so I used the MMQGIS plugin to convert the multilinestring to linestring. But even then, I had to use st_linemerge with the network data. The conversion resulted in the linestring data consisting of 2240 features (versus the previous 2204)
Using this I thought I'll get 2455 rows (one per point), but ended up getting 5,499,200 points/rows (which is 2455 x 2240).
I can find many tutorials for pgrouting with road networks etc, but not one for my scenario where I have a network with the nodes separate.
Could someone please point me in the right direction how I can combine these two layers and set them up for pgrouting?