CODEWITHSUNDEEP
google-bigquerygis
ronencozen
ronencozen

Reputation: 2201

BigQuery Geography function to compute cartesian product of distances

I need to calculate the cartesian product of distances for an array of GEO points. More specifically to compute the distance from the first point to all the other 5 points, then the distance from the second point to all the other points etc..

Wondering if there is a BigQuery Geography function that can carry out this type of computation efficiently.

The alternative is to do it explicitly pair by pair which is kind of a brute force approach.

POINT(-95.665885 29.907145)
POINT(-95.636533 29.757219)
POINT(-95.652796 29.89204)
POINT(-84.27087 33.991642)
POINT(-84.466853 33.987008)

Upvotes: 0

Views: 219

Answers (1)

rmesteves
rmesteves

Reputation: 4075

I tried to create an example using your example data:

//Creating a temporary table with your data
with t as 
(Select * from UNNEST(
['POINT(-95.665885 29.907145)',
'POINT(-95.636533 29.757219)',
'POINT(-95.652796 29.89204)',
'POINT(-84.27087 33.991642)',
'POINT(-84.466853 33.987008)']) f
)

//Doing a cross join of the created table with itself, filtering some cases to avoid calculating the distance of a point to itself and calculating the distance between the points

select
  t.f point_1, 
  t2.f point_2, 
  ST_DISTANCE(ST_GeogFromText(t.f), ST_GeogFromText(t2.f))
from t cross join t t2
where t.f <> t2.f
group by point_1, point_2

Is it what you are looking for? Of course it can be optimized if you consider that distance between two points are the same doesn't matter their order.

Upvotes: 1

Related Questions