Finding shortest geospatial distance in SQL-CodePudding

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:

|------------|------------|------------|
|   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

CodePudding user response：

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;