We currently have a working script to return a list of all our products within a radius of a postcode/zipcode. It searches for all products that have locations in area codes within the @miles parameter of @lookingfor postcode, also returning the distance.
The PostodeLocation table contains four columns: the town/city, the postcode (pcd) and the Northing (grn) and Easting (gre) grid references. The primary key is on the postcode.
The problem is: I do not know the centre centre of each town or the postcodes for the outer radius of each town. I cannot adapt the script to give me all products in locations within a radius of a town, or even a town centre.
Has anybody else encountered this requirement and found a solution? I'm looking for a technique more than the actual code requirement.
Current script:
declare @lookingfor nvarchar(10) = 'sw1a 1aa'
declare @miles int = 5
DECLARE @UserCoordX bigint
DECLARE @UserCoordY bigint
SELECT @UserCoordX = GRE
, @UserCoordY = GRN
FROM MyCompany.dbo.PostcodeLocations pcls
WHERE pcls.PCD = @lookingfor
select pcls.PCD
, p.ProdName
, sqrt((((pcls.GRE - @UserCoordX) * (pcls.GRE - @UserCoordX)) + ((pcls.GRN - @UserCoordY) * (pcls.GRN - @UserCoordY)))/(160.9334*160.9334)) as Distance
from MyCompany.dbo.pcls_disk pcls
inner join MyCompany.dbo.Loc l on l.Postcode = pcls.pcd
inner join MyCompany.dbo.X_ProdLoc xpl on xpl.LocId = l.LocId
inner join MyCompany.dbo.Prod p on p.ProdId = xpl.ProdId
where p.StatusId = 4 and p.HideFromSearch = 0
and ((pcls.GRE - @UserCoordX) * (pcls.GRE - @UserCoordX)) + ((pcls.GRN - @UserCoordY) * (pcls.GRN - @UserCoordY))
<= cast(@Miles * 160.9334 * @Miles * 160.9334 as bigint)
order by [Distance]