0

have an issue with pgRouting that I've modified to consider multi-floor indoor areas. I've created a mock floor plan of any typical hotel. I copied the floor plan and assigned a different z-value to essentially mirror the floor plan, with one positioned directly above the other. I then made vertical connections between the floors for elevators and stairs to create a complete 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

When constructing the SQL View, I used a modified version of pgr_fromAtoB to consider z-values. I modified as follows (see @underdark's answer to pgRouting with multiple floors):

-- 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;

However, it doesn't appear that the z-value is being considered. For example, I have nodes lying exactly on top of each other with the same lat/long, but with differing z-values. I can issue the ST_GeometryFromText command and limit the results to 2 and see the both of the nodes are being returned at the lat/long I've specified. However, the z-value for the bottom node is 0 and the upper node is 1. No matter what z-value I specify in the request, the upper node is always returned as the top result.

Does anyone know why?

How can I get it to return the bottom node if I specify the z-value as 0?

2

The <-> operator stands for "K-nearest-neighbor" (KNN) search, which does not support 3D but only 2D distances: http://postgis.net/docs/manual-dev/geometry_distance_knn.html

From PostGIS 2.2 you can use <<->> for 3D distances, but that version is not released yet: http://postgis.net/docs/manual-dev/geometry_distance_centroid_nd.html

KNN is very fast and easier to use, but you can also select a BBOX around your point, calculate the distances, order the result by distance and take the smallest one:

SELECT id::integer, 
       ST_3DDistance(
           the_geom, 
           ST_GeometryFromText('POINT(x y z)', 4326)
       ) AS dist 
FROM ways_vertices_pgr  
WHERE the_geom && ST_Expand(ST_GeomFromText('POINT(x y z)', 4326), 0.1) 
ORDER BY dist LIMIT 1";

This is a modified example from the previous pgRouting workshop, so I'm not sure the syntax is 100% correct, but I hope it explains the idea.

  • dkastl, Thanks so much for this, basically exactly what I needed, with one minor caveat. I had to swap out ST_3DDistance for ST_Distance to grab the correct node. Using the ST_Distance kept selecting the bottom node, regardless of the z-value provided. Anyways, changing to ST_3DDistance returned the correct node and lets me distinguish between floors now. Again, thanks so much for your help. – wayoutwest Jul 8 '15 at 16:09
  • @wayoutwest nice to hear that it worked. And thank you for pointing out the error. I updated the answer accordingly. I totally missed that distance function because I assumed it would also work with 3D. – dkastl Jul 10 '15 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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