PostCodeDistanceCalculator

SQL Server UK Postleitzahlen-Entfernungsrechner

Ein Tool zum Berechnen der Entfernung zwischen zwei britischen Postentfernungen und zum Auffinden einer Liste aller Postleitzahlen innerhalb einer bestimmten Kilometerzahl.

Klicken Sie unten, um das Walkthrough-Video anzusehen:

Finden aller Postleitzahlen im Umkreis von X Kilometern

Um eine Liste von Postleitzahlen im Umkreis von X Kilometern zu finden, verwenden Sie die Tabellenfunktion postCodesWithinXkm . Um beispielsweise alle Postleitzahlen mit 5 Kilometern SW1A 0AA zu finden, können Sie den folgenden Code verwenden.

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

Berechnung der Entfernung zwischen zwei Postleitzahlen

Um den Abstand zwischen zwei Postleitzahlen zu bestimmen, verwenden Sie die Skalarfunktion distanceBetweenPostCodes . Beispiel: Um den Abstand zwischen SW1A 0AA und SE10 0DX zu ermitteln, können Sie den folgenden Code verwenden.

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

Rückgabe einer Tabelle mit Abstand von einem Beitrag zu allen anderen

Um eine Liste aller Postleitzahlen und deren Entfernung zu einem bestimmten Beitrag zu finden, verwenden Sie die Tabellenfunktion distanceToAllPostCodes

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

Anwendungsfälle

Angenommen, wir haben eine CRM-Datenbank mit einer Tabelle Prospects , die eine Liste potenzieller Kundenadressen enthält, und einer Tabelle Branch die alle Adressen unserer Filialen enthält.

Verwenden von postCodesWithinXkm

Wenn in einer bestimmten Filiale eine Werbeaktion durchgeführt wird und wir alle potenziellen Kunden im Umkreis von 5 km um die Filiale benachrichtigen möchten, können wir den folgenden Code verwenden, um eine Liste aller potenziellen Kunden zu erhalten, die wir kontaktieren sollten.

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

Die Ausgabe würde etwa so aussehen:

Interessentenname	InteressentenE-Mail	Postleitzahl
Acme Corp Ltd	[email protected]	PO18 0AE
Tools4u Ltd	[email protected]	PO20 2GU
Hals Fleisch	[email protected]	PO19 7EY

Verwenden von distanceBetweenPostCodes

Mit dem folgenden Code können wir die Anzahl der Interessenten im Umkreis von 5 km um jede unserer Filialen ermitteln.

 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

Die Ausgabe würde etwa so aussehen:

Postleitzahl der Filiale	Zweig	Kunden
BN1 2RE	Brighton	15
PO19 1EL	Chicester	37
W4 5TA	Chiswick	89

Verwenden von distanceToAllPostCodes

Angenommen, wir möchten für jeden potenziellen Kunden die nächstgelegene Filiale ermitteln. Wir können distanceToAllPostCodes verwenden, um die Entfernung jeder Filiale zu jedem potenziellen Kunden zu bestimmen und schrankweise zu bestellen.

 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

Die Ausgabe würde etwa so aussehen:

Interessentenname	Filialname	Distanz
Acme Corp Ltd	Brighton	7.477788367078679
Acme Corp Ltd	Chicester	82.13621491096778
Acme Corp Ltd	Chiswick	86.65279491895977

So stellen Sie die Datenbank auf Ihrer lokalen SQL Server-Instanz bereit

1. Klonen Sie das Repo
2. Öffnen Sie die Lösung in Visual Studio
3. Öffnen Sie PopPostCodes.sql und bearbeiten Sie den Dateispeicherort 'C:UserstimgasourcereposPostCodeDistanceCalculatorPostCodeDistanceCalculatorFilesukpostcodes.csv' um auf den richtigen Speicherort zu verweisen.
4. Öffnen Sie PostCodeDistanceCalculator.publish.xml und wählen Sie die Zielinstanz und Datenbank für die Bereitstellung aus.

---

Enthält Ordnance Survey-Daten. © Crown Copyright und Datenbankrecht 2021

Enthält Royal Mail-Daten. © Royal Mail Copyright und Datenbankrecht 2021

Quelle: Office for National Statistics, lizenziert unter der Open Government License v.3.0