Finding Nearby Accounts in SQL – Geo Coding

Ever wonder how to find MAX Customers, Vendors or Synergy Accounts that are nearby another location? This can be handy to locate distributors to provide service, customer to visit near other customers and many other conveniences.

Here is SQL Code to find MAX Customer that are nearby another customer based on the distance using the Latitude and Longitude of the zip code for the customers.

The ZipCode table contains the latitude and longitude of the center of that zip code.   This table can be created as follows:

  1. Create a ZIPCODES database
  2. Restore the Zipcode.BAK file into the ZipCodes database, this creates a ZipCodes table

The “haversine” function is used to find the distance between to point on the surface of a sphere — the Earth’s surface.

The routine GetNearbyCustomers calls the haversine function to get the customers within a given distance in miles from a start point; such as customer defined by the Customer ID.

For this process to work, create the “haversne” function in the Master database in the Programmability, Functions, Scalar Functions section. This makes the function available to all databases on the server so you can use it with MAX, Synergy or any other database accessible to SQL.

USE Master

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE Function [dbo].[udf_Haversine]

(

@Lat1 Float(18),

@Long1 Float(18),

@Lat2 Float(18),

@Long2 Float(18),

@ReturnType VarChar(10)

)

Returns Float(18)

AS

Begin

Declare @R Float(8);

Declare @dLat Float(18);

Declare @dLon Float(18);

Declare @a Float(18);

Declare @c Float(18);

Declare @d Float(18);

Set @R =

Case @ReturnType

When ‘Miles’ Then 3956.55

When ‘Kilometers’ Then 6367.45

When ‘Feet’ Then 20890584

When ‘Meters’ Then 6367450

Else 20890584 — Default feet (Garmin rel elev)

End

Set @dLat = Radians(@lat2 – @lat1);

Set @dLon = Radians(@long2 – @long1);

Set @a = Sin(@dLat / 2)

* Sin(@dLat / 2)

+ Cos(Radians(@lat1))

* Cos(Radians(@lat2))

* Sin(@dLon / 2)

* Sin(@dLon / 2);

Set @c = 2 * Asin(Min(Sqrt(@a)));

 

Set @d = @R * @c;

Return @d;

 

End

Then create the MAX function for nearby customers in the MAX database using the following code.

Note that this has two parameters; the first for the Customer ID that is the start point and the second is the range in miles from that staet point.

USE [MAX Database Name]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[sp_GetNearbyCustomers]

@CustomerID CHAR(20),

@Range INT = 10         — Default is 10 miles

AS

BEGIN

 

SELECT      dbo.Customer_Master.NAME_23, dbo.Customer_Master.ADDR1_23, dbo.Customer_Master.CITY_23,

dbo.Customer_Master.STATE_23, dbo.Customer_Master.ZIPCD_23,

ROUND(master.dbo.udf_Haversine(ZipCodes_1.Latitude, ZipCodes_1.Longitude, ZipCodes.dbo.ZipCodes.Latitude, ZipCodes.dbo.ZipCodes.Longitude, ‘miles’), 1) AS Miles

FROM   ZipCodes.dbo.ZipCodes AS ZipCodes_1 INNER JOIN

dbo.Customer_Master AS Customer_Master_1 ON ZipCodes_1.PostCode = Customer_Master_1.ZIPCD_23 CROSS JOIN

dbo.Customer_Master INNER JOIN

ZipCodes.dbo.ZipCodes ON dbo.Customer_Master.ZIPCD_23 = ZipCodes.dbo.ZipCodes.PostCode

WHERE   (Customer_Master_1.CUSTID_23 = @CustomerID )

AND (master.dbo.udf_Haversine(ZipCodes_1.Latitude, ZipCodes_1.Longitude, ZipCodes.dbo.ZipCodes.Latitude, ZipCodes.dbo.ZipCodes.Longitude, ‘miles’) > 0

AND master.dbo.udf_Haversine(ZipCodes_1.Latitude, ZipCodes_1.Longitude, ZipCodes.dbo.ZipCodes.Latitude, ZipCodes.dbo.ZipCodes.Longitude, ‘miles’) < @Range)

ORDER BY master.dbo.udf_Haversine(ZipCodes_1.Latitude, ZipCodes_1.Longitude, ZipCodes.dbo.ZipCodes.Latitude, ZipCodes.dbo.ZipCodes.Longitude, ‘miles’)

END

To run this code in SQL enter…

EXEC sp_GetNearbyCustomers ‘100’, 25

Where ‘100’ is the start point Customer ID and 25 is the range in miles.

Here is an example of the result…

PlusPoint Bullet