calculate routes between arbitrary points in the network

[dollarklavs]

Is any new function or extension of existing functions planned, that would enable the calculation of routes between points that are not nodes or vertexes in the network?

I think that such a function would be very usefull.

[Zia-]

If the POI is not in the network then how will you decide where to go next?

If you are talking about a situation where user will not provide the nodes/vertices ids for routing query but some coordinates, then one can run a quick spatial query to locate the nearest node wrt to the provided coordinates and pass its id to the routing query. Something like the following

SELECT id::integer FROM ways_vertices_pgr ORDER BY the_geom <->        
ST_GeometryFromText(''POINT(x y)'',4326) LIMIT 1

Follow the pgRouting workshop for details http://workshop.pgrouting.org/chapters/wrapper.html

[woodbri]

On 7/8/2015 11:26 AM, dollarklavs wrote:

Is any new function or extension of existing functions planned, that would enable the calculation of routes between points that are not nodes or vertexes in the network?

I think that such a function would be very usefull.

In addition to @Zia- comments. pgr_trsp() has the ability to route from a point along an edge to another point along an edge. You can use the ideas in @Zia- post to select the closest edge to you lat/lon, then use postGIS linear referencing function to get the distance along the edge where you lat/lon is projected onto that edge.

-Steve

[dollarklavs]

Thank you both for your responce. It seems that I don't know enough about postGIS and pgrouting to make a feature request. And though this isn't stackexchange I hope you can answer this specific question. Would it, by using pgrouting and having a table containing points and table with a network (and associated topology tables)), be possible to create routes between the points and not to the nearest node?

[Zia-]

But the routes between POIs and corresponding nearest nodes will not be different.

I am afraid if I have understood your need exactly. Would be better if you can give an example or attach a figure explaining what you are looking for.

[woodbri]

On 7/9/2015 7:41 AM, dollarklavs wrote:

Thank you both for your responce. It seems that I don't know enough about postGIS and pgrouting to make a feature request. And though this isn't stackexchange I hope you can answer this specific question. Would it, by using pgrouting and having a table containing points and table with a network (and associated topology tables)), be possible to create routes between the points and not to the nearest node?

pgRouting requires and edge table as input to most functions. You nodes and topology imply an edge table assuming straight lines between the nodes in the topology.

You might be able to create a view to represent the equivalent edge table and then be able to work with that.

I think you need to show the create table structure of your data if you expect more specific help. You should also run through the workshop examples so you have a better idea of how pgrouting works.

-Steve

[dollarklavs]

I'm still looking into this problem and I'm struggling to find a method were I can do what I want, which is to calculate routes between the points representing ground radars in my network. As can be seen from the screenshot, using the vertices as points is not very accurate (I of course tried the pgr_fromatob from the workshop, which works great for calculating to the nearest node).

I was also wondering if it's possible break the lines from the taxiways2 schema at the point were the groundradar points intersect it, so that when I use pgr_createtopology the vertice table will contain vertices at the same points as the where the ground radars a located, but I haven't had any success yet using st_split.

I'm not sure I understand your answers, that is why I'm trying to write you again, in the hope that these more detailed information on my data and problem, can result in some help or tips..

/Jonas

The schema for my network, originally created from a .shp file describing the paint on the pavement in Copenhagen airport. Imported using shp2sql and converted from MultiLinestring to Linestring using "ALTER COLUMN geom TYPE geometry(linestring,0) USING ST_GeometryN(geom, 1);":

CREATE TABLE taxiways2 ( gid serial NOT NULL, id integer, geom geometry(LineString), source integer, target integer, CONSTRAINT taxiways2_pkey PRIMARY KEY (gid) )

The vertices created when using pgr_createtopology:

CREATE TABLE taxiways2_vertices_pgr ( id bigserial NOT NULL, cnt integer, chk integer, ein integer, eout integer, the_geom geometry(Point), CONSTRAINT taxiways2_vertices_pgr_pkey PRIMARY KEY (id) )

Ground radars (previously snapped to the taxiways2 geometry), which I would like to use as the source and destination of route calculations:

CREATE TABLE groundradars ( gid serial NOT NULL, name character varying(254), lat numeric, lon numeric, way character varying(254), comments character varying(254), geom geometry(Point), CONSTRAINT groundradars_pkey PRIMARY KEY (gid) )

Below is a screenshot of the three tables visualised in qgis.

[Zia-]

@dollarklavs I found this problem interesting. I think your best bet is to split the taxiways2 line according to groundradars points before running pgr_createtopology. Although I was trying to find a quick workflow to obtain this using postgis and qgis separately but couldn't succeed. I'll try to use python along with qgis to see what can be done, later today. Meanwhile, if you are comfortable to share taxiways and groundradars shp files, plz do it. That will be helpful for better testing. Otherwise i'll test on some synthetic data.

[dollarklavs]

I'll send the data in a few hours. Thank you so much for looking into it

Jonas Pedersen Mobil: 51212034

On 14/07/2015, at 14.21, Mohammed Zia notifications@github.com<mailto:notifications@github.com> wrote:

@dollarklavshttps://github.com/dollarklavs I found this problem interesting. I think your best bet is to split the taxiways2 line according to groundradars points before running pgr_createtopology. Although I was trying to find a quick workflow to obtain this using postgis and qgis separately but couldn't succeed. I'll try to use python along with qgis to see what can be done, later today. Meanwhile, if you are comfortable to share taxiways and groundradars shp files, plz do it. That will be helpful for better testing. Otherwise i'll test on some synthetic data.

— Reply to this email directly or view it on GitHubhttps://github.com/pgRouting/pgrouting/issues/355#issuecomment-121220225.

[dollarklavs]

Hi Zia This is the link to a backup of my database without the vertices (I guess they are easier to just generate...) https://www.dropbox.com/s/gd7bl2qxr41z42a/airport?dl=0

I'll just link to a dump with the vertices as well... https://www.dropbox.com/s/1z6lo4fg7mpbx67/airportdump?dl=0

[Zia-]

@dollarklavs I tried almost the whole day to solve this scenario but unfortunately couldn't succeed :-( Since your groundradars points are not lying exactly on the taxiways lines, we have to project them first of all on to the nearest line. But this wasn't the main prob, prob was how to break the line at a given point? I found this thread http://gis.stackexchange.com/questions/64006/how-to-split-a-line-by-a-point-which-is-on-the-line-but-not-in-the-linestring-it , where it has been demonstrated how to split a line at a given point. But since in your case, there are lines where there are more than one groundradar points. Unfortunately we couldn't pass more than one point into that ST_Line_Locate_Point function. Using python was driving me crazy. Since I couldnt find any QGIS plugin or processing tool to break a line at a given point, I was trying to use the following approach using Python.

1. Select one line.
2. Convert it into text so that we can select each vertices within that line.
3. Select first-second, second-third, third-fourth and so on pairs from the above generated python list, one pair at a time.
4. Check if any groundradars coordinates line on the line connecting those above selected pair. Plus that coordinate must be within the pair coordinates extent.
5. If yes, then return first-groundradar, groundradar-second like wise. Otherwise return just the pair ie. first-second etc etc.

But it was way too complicated than I thought. I see a possibility of making a QGIS plugin solving this task.

One thing which you can do is to split your taxiways into smaller sections using some vector mesh (take the help of https://github.com/Zia-/OSM_route_splitting_mesh/blob/master/script.py). This way there will be high possibility of you finding a nearest node really near to that groundradar point. But I am not happy with this approach.

I will keep on searching. There must be one clean approach. But will give a break for now since I need some time to think. Give gis.stackexchange a try. And do let me know if come up with something.

[woodbri]

I think the way to approach this problem is to write a stored procedure like:

select project_node_and_split_edge(
    'select edge_id as id, geom_colum as geom from edges',
    'select point_id as id, geom_column as geom from points'
);

where each point is projected onto the nearest edge, and then the edge is split into two pieces and the original edge is deleted. After that is done, it would probably be a good idea to run pgr_nodenetwork() to make sure the new network is properly noded.

NOTE: that 2.0 has a bug in pgr_nodenetwork(), there was a fix for this bug in a pull request. I can't remember if that was merged into develop or not, so check the issues and pull requests.

[dollarklavs]

@Zia- First of all, thanks a bunch for the effort. I tried st_union on the taxiways to create a table with just one geometry, but when using st_split (with groundradars) on the resulting geometry, it generated a table with 190994 rows and 1 geometry column, which I couldn't display using qgis.

Regarding the location of the two geometries, I actually thought that the groundradars would be located exactly on the taxiways because of the snapping and I don't understand why they are not (I used my student version of ArcMap to do the snap if that makes any difference).

I'll look into your splitting mesh python script to see if that will work for me.

@woodbri I have a very limited understanding of stored procedures, but I will look into it.

Thanks for helping out

[Zia-]

@dollarklavs Your welcome :-) It was worth giving it a try. I have no idea why the groundradars points are not lying exactly on the taxiways. May be coz of coordinates round off or may be I dunno. I used 5mX5m mesh to break your taxiways shapefile and found output good enough for your purpose, I guess. Here it is https://github.com/Zia-/Blog_Codes/blob/master/compressed/taxiways.zip

But still there is a room for some development in this regard.

@woodbri I'll look into this later, if not sooner. Although I am not sure which PostGIS function to call for

then the edge is split into two pieces

if there is any?

[woodbri]

http://www.postgis.org/documentation/manual-svn/ST_Line_Substring.html http://www.postgis.org/documentation/manual-svn/ST_Line_Locate_Point.html

so:

split_at := ST_Line_Locate_Point(geometry a_linestring, geometry a_point);
part1 := ST_Line_Substring(geometry a_linestring, 0.0, split_at);
part2 := ST_Line_Substring(geometry a_linestring, split_at, 1.0);

[Zia-]

Oh ! I didn't know you meant those functions. I knew them. Thx anyways.

[Zia-]

@woodbri I was trying to write something but am stuck now. Look at the code below.

CREATE OR REPLACE FUNCTION pgr_projectNodeAndSplitEdge(
edge_table_rows text,
points_table_rows text,
projectnodeandsplitedge_table text)
---- above points_table_rows would be something like 'select point_id as id, geom_column as geom from points'  ----
---- Some codes here ----
sql1 := ''|| points_table_rows ||'';
For rec1 in execute sql1 
            Loop
                Execute 'with line as (select geom from '||pgr_quote_ident(projectnodeandsplitedge_table)||' edge order by st_distance(edge.geom, '||pgr_quote_ident(rec1.geom)||') limit 1), 
                frac as (select ST_LineLocatePoint(st_linemerge(line.geom),'||pgr_quote_ident(rec1.geom)||') as fraction from line) 
                select ST_LineSubstring (line.geom,0,frac.fraction) as first_half, 
                ST_LineSubstring (line.geom,frac.fraction,1) as second_half from line, frac' into rec_break;

            raise notice '%', rec_break.first_half;
        End Loop;

The problem is I don't know how to use record.geom value inside a for loop (i.e. '||pgr_quote_ident(rec1.geom)||'). Following is the SQL message.

ERROR:  column "0101000020E6100000802D9F5417482940B054BEA407CF4B40" does not exist
LINE 1: ...geom from x2 edge order by st_distance(edge.geom, "010100002...

Other solutions could be either obtaining the coordinate values directly as an argument from the user (like 'select point_id as id, st_x(geom_column) as x, st_y(geom_column) as y from points') or I should ask user to pass point table name also.

What's ur suggestion?

[Zia-]

I guess I found it.

sql1 := ''|| points_table_rows ||'';
For rec1 in execute sql1 
            Loop
                    x_pt := st_x(rec1.geom);
                    y_pt := st_y(rec1.geom);
                    ---- Using x_pt and y_pt inside Execute -----
End Loop;

[cvvergara]

@dollarklavs , @Zia- for version 2.2 as proposed functions are the withPoints family of functions, you might want to have a look

http://docs.pgrouting.org/dev/src/withPoints/doc/withPoints.html#withpoints

You can still use trsp, @woodbri suggested.

[cvvergara]

I'll close because of the withPoints family of functions

[cqychen]

HERE https://workshop.pgrouting.org/2.4.11/en/chapters/function.html?highlight=point#id2