ST_DISTANCE Method to calculate distance for all values from two tables

[former_member735962]

Hello everyone,

I'm trying to find a way to calculate distance for each value from table A for each value from table B. I've got my ST_POINT calculated in table A (POINT) and B (ROUTE_POINT). Following statement allows me to calculate the distance between all records from table A and ONLY one value from table B as I need to use the WHERE clause: (note: ROUTE ID = key value which represents each coordinate from table B)

SELECT DISTINCT POINT.ST_Distance((SELECT DISTINCT POINT FROM "xxxx"."ROUTE_POINT" WHERE ROUTE_ID =4), 'meter') AS DISTANCE FROM "xxxx"."A";

when I get rid of the WHERE clause I receive following error message:

SAP DBTech JDBC: [305]: single-row query returns more than one row

I was also thinking about the cross join where all values from both tables are combined, but whould it be to complicated, wouldn't? Do I need a procedure in my case or is there any simplier way to solve it?

Thank you in advance!

[Vitaliy-R · Accepted solution]

Something like

SELECT "A"."POINT".ST_Distance("B"."POINT", 'meter') AS "DISTANCE"
FROM "A", "B";

should give you a cross-join between all rows or "A" and "B" tables.

Example:

CREATE TABLE A (ID int, POINT ST_POINT);
CREATE TABLE B (ID int, POINT ST_POINT);


INSERT INTO "A" VALUES(1, new st_point(-1,-1));
INSERT INTO "A" VALUES(2, new st_point(-1, 0));
INSERT INTO "A" VALUES(3, new st_point(-1, 1));
INSERT INTO "B" VALUES(1, new st_point( 1,-1));
INSERT INTO "B" VALUES(2, new st_point( 1, 0));
INSERT INTO "B" VALUES(3, new st_point( 1, 1));


SELECT "A"."POINT".ST_asWKT() AS "A", 
"B"."POINT".ST_asWKT() AS "B",
"A"."POINT".ST_Distance("B"."POINT", 'meter') AS "DISTANCE"
FROM "A", "B";

Pozdrawiam,
-Witalij

[former_member735962 · Accepted solution]

Thank you Witalij that's what I was looking for! Case closed 🙂

[agentry_src]

From what you describe, it is a cross join query. Not sure why you had Distinct in your query (two places)? If this is a Transportation Management calculation, you might be better to use the tools TM provides instead of doing it with a HANA DB query.

Cheers, Mike

[former_member735962]

Thank you for respond,

in this case it needs to be a hana query. The distinct values i can be deleted, data has been loaded twice so I just added this clause quickly.