PostCodeDistanceCalculator

Calculateur de distance de code postal SQL Server UK

Un outil pour calculer la distance entre deux distances UK Post et trouver une liste de tous les codes postaux dans un certain nombre de kilomètres.

Cliquez ci-dessous pour regarder la vidéo pas à pas :

Trouver tous les codes postaux dans un rayon de X kilomètres

Pour trouver une liste de codes postaux dans un rayon de X kilomètres, utilisez la fonction Table postCodesWithinXkm . Par exemple, pour trouver tous les codes postaux avec 5 kilomètres de SW1A 0AA, vous pouvez utiliser le code ci-dessous.

 SELECT * FROM postCodesWithinXkm('SW1A 0AA',5)

Calculer la distance entre deux codes postaux

Pour déterminer la distance entre deux codes postaux, utilisez la fonction scalaire distanceBetweenPostCodes . Par exemple : pour trouver la distance entre SW1A 0AA et SE10 0DX, vous pouvez utiliser le code ci-dessous.

 SELECT dbo.distanceBetweenPostCodes('SW1A 0AA','SE10 0DX')

Renvoyer une table avec la distance d'un message à tous les autres

Pour trouver une liste de tous les codes postaux et leur distance par rapport à un poste particulier, utilisez la fonction de tableau distanceToAllPostCodes

 SELECT * FROM dbo.distanceToAllPostCodes('SW1A 0AA') a

Cas d'utilisation

Supposons que nous ayons une base de données CRM contenant une table Prospects contenant une liste d'adresses de clients potentiels et une table Branch contenant toutes les adresses de nos succursales.

Utilisation de postCodesWithinXkm

Si une succursale particulière organisait une promotion et que nous souhaitions informer tous les clients potentiels dans un rayon de 5 km de la succursale, nous pourrions utiliser le code ci-dessous pour obtenir une liste de tous les clients potentiels que nous devrions contacter.

 SELECT      a.ProspectName, a.ProspectEmail, a.PostCode
FROM        CRM..Prospects a
INNER JOIN  postCodesWithinXkm('PO19 1EL',5) b on a.PostCode = b.PostCode

La sortie ressemblerait à quelque chose comme :

Nom du prospect	ProspectE-mail	Code postal
Acme Corp Ltd	[email protected]	PO18 0AE
Outils4u Ltd	[email protected]	PO20 2GU
Les viandes de Hal	[email protected]	PO19 7EY

Utilisation de distanceBetweenPostCodes

Nous pourrions utiliser le code ci-dessous pour déterminer le nombre de Prospects dans un rayon de 5 km de chacune de nos agences.

 SELECT      a.PostCode As BranchPostcode, a.BranchName, count(b.Id) As CustomerCount
FROM        CRM..Branches a
INNER JOIN  CRM..Prospects b 
ON PostCodeDistanceCalculator.dbo.distanceBetweenPostCodes(a.PostCode,b.PostCode)<5
GROUP BY    a.PostCode

La sortie ressemblerait à quelque chose comme :

Code postal de la succursale	Bifurquer	Clients
BN1 2RE	Brighton	15
PO19 1EL	Chicester	37
W4 5TA	Chiswick	89

Utilisation de distanceToAllPostCodes

Supposons que nous voulions déterminer la succursale la plus proche pour chaque client potentiel. Nous pouvons utiliser distanceToAllPostCodes pour déterminer la distance de chaque succursale à chaque client potentiel et commander par placard.

 SELECT  a.ProspectName, c.BranchName, b.distance, 
        ROW_NUMBER() OVER (PARTITION BY a.ProspectName ORDER BY b.distance ) As DistanceRank
FROM        CRM..Prospects a
CROSS APPLY PostCodeDistanceCalculator.dbo.distanceToAllPostCodes(a.PostCode) b
INNER JOIN  CRM..Branches c on b.Postcode = c.Postcode

La sortie ressemblerait à quelque chose comme :

Nom du prospect	Nom de la branche	Distance
Acme Corp Ltd	Brighton	7.477788367078679
Acme Corp Ltd	Chicester	82.13621491096778
Acme Corp Ltd	Chiswick	86.65279491895977

Comment déployer la base de données sur votre instance SQL Server locale

1. Cloner le dépôt
2. Ouvrez la solution dans Visual Studio
3. Ouvrez PopPostCodes.sql et modifiez l'emplacement du fichier 'C:UserstimgasourcereposPostCodeDistanceCalculatorPostCodeDistanceCalculatorFilesukpostcodes.csv' pour pointer vers l'emplacement correct.
4. Ouvrez le PostCodeDistanceCalculator.publish.xml et sélectionnez l'instance cible et la base de données sur lesquelles déployer.

---

Contient des données de l'Ordnance Survey © Copyright de la Couronne et droit de base de données 2021

Contient des données Royal Mail © Royal Mail copyright et droit de base de données 2021

Source : Office des statistiques nationales sous licence Open Government License v.3.0