I’m trying to find the shortest distance between the places and the user’s location and I have two table namely: Users_Location and Places
User’s Table:
x
|------------|------------|------------|| user_id | latitude | longitude ||------------|------------|------------|| 1 | 21.89027 | -53.03772 || 2 | 23.87847 | -41.78172 || 3 | 39.62847 | -80.19892 || 4 | 77.87092 | -96.39242 || 5 | 17.74962 | -28.56972 ||------------|------------|------------|Places Table
|--------------|------------|------------|| Places | latitude | longitude ||--------------|------------|------------|| New York | 72.92629 | -12.89272 || Chicago | 93.62789 | -83.10172 || Dallas | 68.92612 | -67.17242 || Cincinnati | 41.62729 | -37.19067 || Milwaukee | 86.90617 | -22.82997 || Philadelphia| 38.91682 | -93.90692 ||--------------|------------|------------|I want to find the shorted distance between user’s latitude-longitude to all other places.
The final table would look like:
|------------|-----------------|| user_id | nearest_place ||------------|-----------------|| 1 | New York || 2 | Milwaukee || 3 | Chicago || 4 | New York || 5 | Philadelphia ||------------|-----------------|I’m using Snowflake as a data-warehouse and tried to use the HAVERSINE function to calculate the geospatial distance. But I don’t get how do I loop over each place in Places table to get the shortest distance using SQL
Advertisement
Answer
This should out put the closest city to user:
SELECT u.user_id, p.Places as nearest_place, haversine( u.latitude, u.longitude, p.latitude, p.longitude) as distance FROM users u, places p qualify row_number() over (partition by user_id order by distance) = 1;