Welche Möglichkeiten gibt es, Straßenadressen in SQL Server abzugleichen?

Question

Wir haben eine Spalte für Straßenadressen:

123 Maple Rd.
321 1st Ave.
usw...

Gibt es eine Möglichkeit, diese Adressen mit einer bestimmten Eingabe abzugleichen? Die Eingabe wäre eine Straßenadresse, die aber möglicherweise nicht das gleiche Format hat. Zum Beispiel:

123 Maple Road
321 1st Avenue

Unser erster Gedanke ist, alle Straßenbegriffe (rd, st, ave, blvd, etc.) aus der Eingabe zu entfernen.

Das wird natürlich nicht immer zuverlässig funktionieren. Gibt es andere Möglichkeiten, Straßenadressen in SQL Server abzugleichen?

Wir können benutzerdefinierte Funktionen, gespeicherte Prozeduren und normales altes t-sql verwenden. Wir können nicht clr verwenden.

Answer 1

Anstatt die Dinge, die variabel sein können, herauszunehmen, sollten Sie versuchen, sie in eine "kanonische Form" umzuwandeln, die verglichen werden kann.

Ersetzen Sie zum Beispiel "rd" oder "rd." durch "road" und "st" oder "st." durch "street", bevor Sie vergleichen.

Answer 2

Sie sollten die Verwendung des Levenshtein-Abstand Algorithmus.

Sie können sie als benutzerdefinierte Funktion in SQL Server erstellen, die die Anzahl der Operationen zurückgibt, die an String_A durchgeführt werden müssen, damit er zu String_B wird. Sie können dann das Ergebnis der Levenshtein-Distanz-Funktion mit einem festen Schwellenwert oder mit einem von der Länge der Zeichenketten abgeleiteten Wert vergleichen.

Sie verwenden es einfach wie folgt:

... WHERE LEVENSHTEIN(address_in_db, address_to_search) < 5;

Als Mark Byers schlug vor Die Konvertierung variabler Terme in die kanonische Form ist hilfreich, wenn Sie die Levenshtein-Distanz verwenden.

Verwendung von Volltextsuche kann eine weitere Option sein, zumal Levenshtein normalerweise einen vollständigen Tabellenscan erfordern würde. Diese Entscheidung kann davon abhängen, wie häufig Sie diese Abfragen durchführen wollen.

Vielleicht möchten Sie die folgende Levenshtein-Distanz-Implementierung für SQL Server ausprobieren:

• Levenshtein-Distanz-Algorithmus: TSQL-Implementierung

Hinweis: Für die obige Implementierung müssten Sie eine MIN3-Funktion implementieren. Sie können die folgende verwenden:

CREATE FUNCTION MIN3(@a int, @b int,  @c int)
RETURNS int
AS
BEGIN
    DECLARE @m INT
    SET @m = @a

    IF @b < @m SET @m = @b
    IF @c < @m SET @m = @c

    RETURN @m
END

Vielleicht interessieren Sie sich auch für die folgenden Artikel:

• Geokodierung von Adressen mit Fuzzy String Matching [Verwendet Levenshtein-Distanz]
• Stack Overflow - Strategien zum Auffinden von doppelten Mailing-Adressen
• Zusammenführen/Säubern und Erkennung von Duplikaten

Answer 3

Um einen korrekten Abgleich von Straßenadressen durchführen zu können, müssen Sie Ihre Adressen in eine standardisierte Form bringen. Werfen Sie einen Blick auf die USPS-Poststandards aquí (Ich nehme an, Sie haben es mit US-Adressen zu tun). Es ist keineswegs ein einfaches Verfahren, wenn Sie mit ALLEN Arten von US-Postadressen zurechtkommen wollen. Es gibt Software von Unternehmen wie QAS und Satori Software, mit der Sie die Standardisierung durchführen können. Sie müssen Ihre Adressen exportieren, sie durch die Software laufen lassen und dann die Datenbank mit den aktualisierten Adressen laden. Es gibt auch Drittanbieter, die die Adressstandardisierung ebenfalls durchführen. Wenn die Adressen in Ihrer Datenbank standardisiert sind, haben Sie eine bessere Chance, sie abzugleichen (vor allem, wenn Sie auch die Eingabe standardisieren können).

Answer 4

Ich denke, der erste Schritt für Sie besteht darin, besser zu definieren, wie großzügig oder nicht großzügig Sie bei unterschiedlichen Adressen sein werden. Zum Beispiel, welche dieser Adressen übereinstimmen und welche nicht:

123 Maple Street
123 Maple St
123 maple street
123 mpale street
123 maple
123. maple st
123 N maple street
123 maple ave
123 maple blvd

Gibt es sowohl eine Maple Street als auch einen Maple Blvd in demselben Gebiet? Was ist mit Oak Street vs. Oak Blvd.

Zum Beispiel gibt es dort, wo ich wohne, viele Straßen, die alle Owasso heißen. Ich wohne in der Owasso Street, die mit dem North Owasso Blvd. verbunden ist, der wiederum mit dem South Owasso Blvd. verbunden ist. Es gibt jedoch nur eine Victoria Ave.

In Anbetracht dieser Tatsache müssen Sie entweder eine Datenbank mit allen Straßennamen haben und die nächstgelegene Straße suchen (und die Nummer gesondert behandeln)

OR

Entscheiden Sie im Voraus, worauf Sie bestehen werden und worauf Sie nicht bestehen werden.

Answer 5

Adressabgleich und Deduplizierung sind eine chaotische Angelegenheit. Andere Poster haben Recht, wenn sie sagen, dass die Adressen zunächst an die lokale Postnormungsbehörde (z. B. den USPS, wenn es sich um US-Adressen handelt) standardisiert werden müssen. Sobald die Adressen im Standardformat sind, ist der Rest einfach.

Es gibt mehrere Dienste von Drittanbietern, die Duplikate in einer Liste für Sie kennzeichnen. Wenn Sie dies ausschließlich mit einer MySQL-Subabfrage tun, werden die Unterschiede in den Adressformaten und -standards nicht berücksichtigt. Der USPS (für US-Adressen) hat bestimmte Richtlinien, um diese zu einem Standard zu machen, aber nur eine Handvoll von Anbietern ist für die Durchführung solcher Operationen zertifiziert.

Ich würde Ihnen daher empfehlen, die Tabelle z. B. in eine CSV-Datei zu exportieren und sie an einen leistungsfähigen Listenverarbeiter zu übermitteln. Ein solcher ist SmartyStreets' Bulk-Adressvalidierungstool die dies in wenigen Sekunden bis Minuten automatisch für Sie erledigt. Es kennzeichnet doppelte Zeilen mit einem neuen Feld namens "Duplicate" und einem Wert von Y darin.

Versuchen Sie, eine Reihe von Adressen zu standardisieren und zu validieren aquí um eine Vorstellung davon zu erhalten, wie die Ausgabe aussehen wird.

Vollständige Offenlegung: Ich arbeite für SmartyStreets