0

This question already has an answer here:

I am doing indoor multi-floor routing using pgRouting.

At the moment, I have a dummy network system created to analyze shortest routes between locations with the results served as a WMS layer in GeoServer. My initial test of the system only included a simple network created on the same z-plane, and everything works perfectly.

At that point, I created some mock interior floor plans to create indoor networks on two separate floors. For simplicity, I simply assigned the z-elevation of the bottom floor as ‘0’ and the upper floor as ‘10’. I then created the vertical corridors (e.g. elevators and stairs) in ArcScene to make sure the vertical connections existed, resulting in a fully-connected 3D network.

Using the SQL code found here https://github.com/mapcentia/geocloud2/blob/master/app/scripts/sql/pgr_createTopology3D.sql, I created the modified pgr_createTopology3d and pgr_pointToIdZ functions to build topology on the 3D network. Following that, I followed normal procedure for building the SQL View to serve the queried results as a layer to GeoServer.

Now that I have the 3D network in the routing system, I tried running some test queries across different floors to ensure that it routes appropriately; unfortunately it does not. Instead, the system continues to operate on the same z-plane, and seems to ignore the z-values in the vertices, meaning a route is returned, but only on the plane with z-levels assigned as ‘0’. I’m guessing this has to do with the SQL Statement that’s entered when creating the SQL View in GeoServer. Right now, the statement is as follows:

SELECT ST_MakeLine(route.geom) FROM (
    SELECT geom FROM pgr_fromatob('ways', %x1%, %y1%, %x2%, %y2%
) ORDER BY seq) AS route

That statement above is passing to/from lat/longs to the table ‘ways’ to produce the resulting shortest path; however, there’s nothing in there to give the query any sort of z-information.

Is that where things are going wrong and not correctly routing across multiple floors?

If so, can anyone provide insight on how to correct the SQL Statement to consider z-values?

I’m no SQL expert and been trying a number of different modifications, but nothing working as of yet.

marked as duplicate by underdark Jul 12 '15 at 13:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

1

pgr_fromatob() from http://workshop.pgrouting.org/chapters/wrapper.html is unsuitable for 3D routing since it only covers finding the nearest network node in 2D. You need to adapt the code to find the nearest network node in 3D. Afterwards, everything else should work as in the 2D case.

Something like (untested!)

-- Find nearest node
EXECUTE 'SELECT id::integer FROM ways_vertices_pgr 
        ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' 
        || x1 || ' ' || y1 || ' ' || z1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;

EXECUTE 'SELECT id::integer FROM ways_vertices_pgr 
        ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' 
        || x2 || ' ' || y2 || ' ' || z2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;
  • Thanks for that underdark, unfortunately, not quite getting me there. For whatever reason, when I issue the request, no geometry is returned, but no errors are thrown either. The odd thing is that the network is fully connected, and when I issue the code above for simply finding the correct node, the correct node is returned; no issue whatsoever. So, the nodes are being found, but the geometry is not being returned. – wayoutwest Jul 6 '15 at 19:07
  • Sry, hit return too quickly. ;) Here's some more detail about the network. I've simply created a simple mock floor plan typical of any hotel. I created the lower floor first, and then copied that as the second floor and assigned different elevation values to give them vertical separation. I then created vertical corridors for elevators and stairs to connect the floors. I ran the modified pgr_createTopology3D function on the table and it seems to have executed just fine. When I issue the request to return the node IDs at the specified lat/long, all works fine. But no geometry is returned – wayoutwest Jul 6 '15 at 19:14
  • So made a little headway, but no resolution yet. It seems that the ST_GeometryFromText command is not considering the z value. When I issue the command to two separate nodes that have the same lat/long, but different z values (e.g. lie directly on top of each other) the top node is always returned. Any ideas? – wayoutwest Jul 6 '15 at 22:37

Not the answer you're looking for? Browse other questions tagged or ask your own question.