How do I Do a Radius Search?

Radius Search

There are (at least) two ways of conducting a radius search. The first (and simplest) makes use of SQL Servers Geography data type. Unfortunately, this approach can be quite slow, making the second technique a better approach. The second approach is to use trigonometric functions to calculate the distance

Geometry

The following code snippet demonstrates a radius search used to identify all properties located within a 1-mile radius of a central point in Ventura County (34.28250,-119,29317). This approach simplifies the geography data type, whuich simplifies the calculation but usually takes longer to run.

Declare @Lat Float = 34.28250
Declare @Lon Float = -119.29317
Declare @Base Geography = geography::Point(@Lat,@lon,4326)

SELECT
    x.[ATTOM ID],
    x.SitusAddress,
    x.Distance
FROM
    (
        SELECT
            [ATTOM ID],
            SitusAddress,
            Distance = @Base.STDistance(geography::Point(Latitude,Longitude,4326)) / 1609.344
        FROM
            Property.PropertyFeature
        Where
            SitusStateCountyFIPS = '06111' And DataLength(Latitude) > 0 And DataLength(Longitude) > 0 And Latitude <> 0 And Longitude <> 0
    ) as x
Where
    x.Distance < 1

.Note: This example takes approximately 3 times longer to run than the Trigonometry approach (below)

Trigonometry

The following code snippet performs the same task but uses trigonometric functions rather than the geography data type to conduct to calculate the distance between two points.

Declare @Lat Float Declare @Lon Float
Set @Lat = 34.28250
Set @Lon = -119.29317

SELECT
    x.[ATTOM ID],
    x.SitusAddress,
    x.Distance
FROM
    (
        SELECT
            [ATTOM ID],
            SitusAddress,
            Distance = (3959 * acos(cos(radians(@Lat)) * cos(radians(Latitude)) * cos(radians(Longitude) - radians(@Lon)) + sin(radians(@Lat)) * sin(radians(Latitude))))
        FROM
            Property.PropertyFeature
        Where
            SitusStateCountyFIPS = '06111'
    ) as x
Where
    x.Distance < 1

Note: This is a simplified version of a more complicated formula that can applied because all US addresses are located in the Northern Hemisphere and West of GMT.