cancel
Showing results for 
Search instead for 
Did you mean: 

HANA SPATIAL + GROUP BY using only one column

0 Kudos

Hello,

another topic related to HANA SPATIAL. I've managed to combine all possible spots from two tables and find the minumum distance between them (using ST_DISTANCE method):

SELECT ROUTE_ID ,
min("PETROL_LIST_2"."POINT".ST_Distance("ROUTE_POINT"."POINT" , 'meter')) AS DISTANCE FROM "xxxxxx"."PETROL_LIST_2" , "xxxx"."ROUTE_POINT" GROUP BY ROUTE_ID

My current problem is though, I can see what is the distance, what is the starting point but...can't find out which destination is the nearest.

ROUTE_ID; DISTANCE
1; 1.044,0266467415372
2; 1.155,550272948335
3; 1.475,2037133655504

using Group By for more than column route_ID makes the result much bigger (min DISTANCE is somewhere among the other values). I've been trying to use the Where clause:

SELECT ROUTE_ID, "POINT_B", DISTANCE
FROM (SELECT ROUTE_ID, "ID_Stacji", min("PETROL_LIST_2"."POINT".ST_Distance("ROUTE_POINT"."POINT" , 'meter'))
AS DISTANCE FROM "xxxx"."PETROL_LIST_2" , "xxxxx"."ROUTE_POINT" GROUP BY ROUTE_ID, "ID_Stacji")
WHERE DISTANCE = (SELECT min("PETROL_LIST_2"."POINT".ST_Distance("ROUTE_POINT"."POINT" , 'meter'))
FROM "xxxxx"."PETROL_LIST_2" , "xxxxx"."ROUTE_POINT" )
GROUP BY ROUTE_ID, "POINT_B", DISTANCE

but it gives me only one record (the min value which comes from the "FROM" clause before). Any idea how to "attach" another column "POINT B" to make it look like this? :

ROUTE_ID; DISTANCE;POINT B
1; 1.044,0266467415372; 5
2; 1.155,550272948335; 10
3; 1.475,2037133655504; 15

thank you in advance!

Accepted Solutions (0)

Answers (1)

Answers (1)

mkemeter
Product and Topic Expert
Product and Topic Expert

Hi Sebastian,

you probably already figured out that you could potentially join back the second table by using the distance value. Of course it is not guaranteed that you receive exactly one match, since there may be several points with the same distance. However, for this approach you need another join and another evaluation of ST_DISTANCE, which seems cumbersome.

It may be more convenient to use the RANK window function. The statement will then look similar to this one:

SELECT *
FROM
(
  SELECT 
    a."id" aid, 
    b."id" bid,
    a.shape ashape,
    b.shape bshape,
    RANK() OVER (PARTITION BY a."id" ORDER BY a.shape.ST_DISTANCE(b.shape) ASC) AS rnk
  FROM a, b
  WHERE a."id" <> b."id"
)
WHERE RNK = 1

One advantage is that you can also filter for 'RNK <= 5' at the end to get the 5 closest points in the corresponding table (instead of only the closest one).

If you really want to get only the closest one, there is another approach by using Voronoi cells. It will be a bit of a brain teaser to see that this is really working and I recommend to simply use RANK. However, for the sake of completeness, I will also provide the Voronoi statement, which does not make use of ST_Distance at all 🙂

SELECT *
FROM 
(
  SELECT
    *,
    ST_VoronoiCell(a.SHAPE, 10) OVER () vcell
  FROM a
 ) v
 LEFT JOIN b ON v.vcell.ST_Intersects(b.shape) = 1

Depending on your scenario, the Voronoi approach may even yield a better performance.

Note, that to my knowledge you need the sub-select in both cases as window functions are only supported in the SELECT and ORDER BY part of your statement (...and not in the WHERE clause)

Regards,
Mathias

Hello Mathias,

thank you for your reply. I've managed to adjust my query to get the result (yes, the RANK clause helped me out):

SELECT "ID_Stacji", ROUTE_ID, DISTANCE FROM(
SELECT "ID_Stacji", ROUTE_ID, DISTANCE, RANK() OVER (PARTITION BY ROUTE_ID ORDER BY DISTANCE) AS RANK FROM
(SELECT *, "PETROL_LIST_2"."POINT".ST_Distance("ROUTE_POINT"."POINT" , 'meter')
AS DISTANCE FROM "xxxxxx"."PETROL_LIST_2" , "xxxxxx"."ROUTE_POINT")
ORDER BY DISTANCE ASC )
WHERE RANK = 1 ORDER BY ROUTE_ID

VoronoiCell Method is unfortunately available from the SP05, our HANA is 04.