CODEWITHSUNDEEP
sqljoinlocationgeospatialsnowflake-cloud-data-platform
stevenmuraco
stevenmuraco

Reputation: 21

Geospatial join in snowflake

I am looking to do some sort of geospatial join in snowflake. Currently, running this query takes over a day.

I have one table of location data points (~1 billion records), and another table of POI locations (~6k), (both have lat/lon as coordinates). I want to plot how many data points I am seeing within X meters of a POI.

Can only use snowflake, or any open source program. Any help is appreciated.

I would like to see a table with the columns below:

POI_id Radius Data Points

Upvotes: 2

Views: 2258

Answers (2)

ffi23
ffi23

Reputation: 121

Update on the answer, Snowflake now recommends using the ST_DISTANCE function instead:

ST_DISTANCE ( <geography_or_geometry_expression_1> , <geography_or_geometry_expression_2> )

So the code would be:

select *
from table1 a
inner join table2 b
on st_distance(st_point(a.long, a.lat), st_point(b.long, b.lat)) <= 10*1000
;

Also note that this code will duplicate your location points if you have more than 1 Point of Interest within 10km of a location: that location would join to both POIs, creating 2 rows in the output.

Use select distinct to solve that.

Upvotes: 1

Ryoichi Sato
Ryoichi Sato

Reputation: 21

Haversine Function

the way to do this is via a haversine function within the 'on' clause joining the two tables.

select *
from table1 a
inner join table2 b
on haversine(a.lat,a.long,b.lat,b.long) <= 10
;

so join if 10km or less as example above

Upvotes: 2

Related Questions