How to get location from latitude and longitude in T-SQL?

I have a mapping table of locationId along with their center latitude and center longitude value like below-

| Location Id | Center_lat | Center_long |
|-------------|------------|-------------|
| 1           | 50.546     | 88.344      |
| 2           | 48.546     | 86.344      |
| 3           | 52.546     | 89.344      |

I have another table where I am getting continuous location data with latitude and longitude for user like below –

+---------+------------+-------------+
| User Id | Center_lat | Center_long |
+---------+------------+-------------+
|     101 |     50.446 |      88.314 |
|     102 |     48.446 |      86.314 |
|     103 |     52.446 |      89.314 |
+---------+------------+-------------+

I want to get the locationId of all users if their latitude and longitude values lies within 1000 meters of lat-long values corresponding to location id. How can I get it done in T-SQL?

Final table should like below –

+---------+------------+-------------+------------+
| User Id | Center_lat | Center_long | LocationId |
+---------+------------+-------------+------------+
|     101 |     50.546 |      88.344 |          1 |
|     102 |     48.546 |      86.344 |          2 |
|     103 |     52.546 |      89.344 |          3 |
+---------+------------+-------------+------------+

Answer

You can use convert the latitude/longitude pairs to geography objects, and then use stdistance():

select u.*, l.location_id
from users u
inner join locations l
    on geography::point(u.center_lat, u.center_long, 4326).stdistance(geography::point(l.center_lat, l.center_long, 4326)) < 1000

Note that it would be much more efficient to store this information as points to start with.

Advertisement

Answer