Skip to content

Instantly share code, notes, and snippets.

@kylekeesling

kylekeesling/geo-target.sql

Created February 21, 2013 17:03
Show Gist options
  • Save kylekeesling/5006251 to your computer and use it in GitHub Desktop.
Save kylekeesling/5006251 to your computer and use it in GitHub Desktop.
Download ZIP
GeoTargeting SQL for 15km Radius of Zip Code 46254
Raw
geo-target.sql
SELECT
myData.*,
ROUND(6378.137 * ACOS(
CASE
WHEN (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254)))) > 1 THEN 1
WHEN (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254)))) < -1 THEN -1
ELSE (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))))
END),0) AS Distance
FROM
[Zip Codes and Locations] AS geo
JOIN [NAME OF SUBSCRIBER DATA EXTENSION] as myData ON geo.[Zip Code] = myData.[Zip Code]
WHERE
ROUND(6378.137 * ACOS(
CASE
WHEN (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254)))) > 1 THEN 1
WHEN (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254)))) < -1 THEN -1
ELSE (SIN(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * SIN(RADIANS(geo.Latitude))) + (COS(RADIANS((SELECT latitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))) * COS(RADIANS(geo.Latitude)) * COS(RADIANS(geo.Longitude) - RADIANS((SELECT longitude FROM [Zip Codes and Locations] where [Zip Code] = 46254))))
END),0) <= 15
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment