Is there a way to compare Lat/long of two tables

I have two tables:

• address_points

• kmldata

  address_points table columns:    ID         address    Latitude1      Longitude2
  kmldata table columns:           Locname    Lat        Long
  

Now I want to see all the records of the address_points table whose Latitude1 and Longitude2 values fall in the range of Lat and Long values of kmldata table.

I have not handled comparison of locations before in the SQL server so don’t know which function I can use here. I thought of the BETWEEN operator but can seem to use it here properly here. Any guidance on how I can acheive this?

Answer

You need to use the spatial functions within SQL Server. First you will need to aggregate all the points in your kmldata table to create an area, and then use STWithin to check which points fall within that area:

declare @kmlarea geography

select @kmlarea = geography::EnvelopeAggregate(geography::Point(Lat, Long, 4326))
from kmldata

select *
from address_points a
where geography::Point(Latitude1, Longitude2, 4326).STWithin(@kmlarea) = 1