Jaki jest uniwersalny sposób przechowywania adresu / lokalizacji geograficznej w bazie danych? [Zamknięte]

Jaki jest prawidłowy format adresu / lokalizacji geograficznej, który pasuje do każdego adresu na Ziemi? W tej chwili mam:

• kraj
• Miasto
• ulica
• numer
• dane tekstowe (dla uproszczenia)
• zamek błyskawiczny
• lat / lng

Ale wierzę, że mogę to poprawić: może istnieć stan / region kraju lub coś w rodzaju obszaru. Lub żaden obszar / region / stan, powiedzmy, w Singapurze lub Hongkongu.

Może nie być ulicy, ale droga, bulwar lub coś innego. Pewna liczba budynków może być złożona. Może być podłoga. Numer pokoju Itp....

Google opracował bibliotekę, która pomaga sprawdzać adresy pocztowe dla każdego kraju na świecie, których można użyć do zaprojektowania schematu do przechowywania tych danych.

Aby rozpocząć, poszukaj najczęściej wymaganych pól we wszystkich adresach od docelowej bazy klientów, a gdy identyfikujesz kolejne kraje o różnych wymaganiach, możesz nadal dostosowywać swój schemat.

Uniwersalny sposób przechowywania adresu / lokalizacji geograficznej w bazie danych jest następujący:

[Address] nvarchar(max) not null

Wymaga to najmniejszej ilości kodu programowania (a więc obniża koszty utrzymania) i jest w pełni kompatybilny z dowolnym adresem. Ma jednak trzy duże problemy:

• Brak walidacji danych oznacza, że ​​pole może być wykorzystane do celów innych niż zapisanie adresu. Jednym z celów jest atak DOS, którego celem jest wypełnienie przestrzeni bazy danych poprzez wprowadzenie 2 GB danych w polu adresu.

• Przechowywane w ten sposób dane uniemożliwiają ich przetwarzanie do celów analizy biznesowej i eksploracji danych. Na przykład ilu użytkowników pochodzi z Indii? Nie ma łatwego sposobu na określenie, ponieważ adresy te nie zostaną znormalizowane.

• Użytkownicy mogą omyłkowo wprowadzić niepełny lub po prostu zły adres.

Aby złagodzić pierwszy problem, ogranicz pole do tego, co uważasz za rozsądny limit. Osobiście zaczynałbym od 1000 znaków, a następnie zmniejszałbym go na podstawie długości adresów wprowadzonych przez pierwszych użytkowników, gdy tylko dostaniesz wystarczająco duży zestaw danych.

Aby złagodzić pozostałe dwa problemy, możesz użyć interfejsu API innej firmy, który analizuje adresy i przedstawia dane zawierające kraj, miasto, kod pocztowy itp. Jeśli to możliwe, interfejs API powinien być w stanie wyświetlać adres na mapa z powrotem do użytkownika, aby zmniejszyć ryzyko wprowadzenia niekompletnego lub niewłaściwego adresu: większość użytkowników wie, gdzie mieszkają, a zobaczenie innej pozycji na mapie dałoby od razu wskazówkę, że powinni sprawdzić swój wkład.

Pamiętaj, że bez względu na to, jakiego interfejsu API używasz, nie będzie on idealny. Znajduje większość adresów, ale nie wszystkie. Oznacza to, że jeśli interfejs API mówi, że adres nie istnieje, ale użytkownik nalega, aby tak było, a priori należy mu ufać, nawet jeśli może się mylić.

Oznacza to również, że nadal powinieneś przechowywać dane oryginalnego użytkownika, obok wyników API. Oznacza to, że schemat staje się:

[RawAddress] nvarchar(max) not null
[ParsedAddress] xml null

Nie ma jednego

Każdy kraj ma inny format adresu. Jeśli masz szczęście, a one w ogóle mają format!

Oczywiście szerokość / długość geograficzna da ci punkt na kuli ziemskiej, ale nie jest tak naprawdę przydatna do identyfikacji poszczególnych domów. Rozważmy na przykład wieżowiec.

Najlepiej jest sprawdzić oficjalny format usługi pocztowej w każdym kraju. Może to być świetne dla bazy danych zaplecza. Ale prawdopodobnie będziesz musiał go uprościć dla użytkowników końcowych, ponieważ będzie on zawierał o wiele więcej pól niż większość ludzi jest przyzwyczajona.

Na przykład w Wielkiej Brytanii znajdują się takie rzeczy, jak „podwójnie zależna lokalizacja”, ale nikt nie wiedziałby, co to znaczy, jeśli je zapytasz.

Jedynym uniwersalnym formatem jest posiadanie pojedynczego pola tekstowego, które może zawierać wiele wierszy tekstu. Umożliwi to każdy możliwy adres na ziemi.

Pracuję nad oprogramowaniem dla wielu krajów. Rozwiązujemy ten problem, zaczynając od większego podmiotu, tzn. Kraj ma pola do najmniejszej wspólnej lub najmniejszej. Działa dobrze we wszystkich krajach, z którymi do tej pory eksperymentowaliśmy. Mamy również inteligentny system zapobiegania duplikatom i łączenie się z tymi, którzy w jakiś sposób dostali się do systemu, ponieważ użytkownicy są bardzo „kreatywni”. W sekcji administracyjnej mamy ustawienie pola adresu w zależności od kraju. tj. Japonia ma najpierw kod pocztowy / pocztowy, a jako ostatni - UK / US.

Ogólnie używamy:

• Kraj
• Kod pocztowy
• Stan / Prowincja / Prefektura / Hrabstwo
• Miasto / miejscowość / wieś
• Ulica / droga / blok
• Nazwa / numer budynku
• Informacje szczegółowe / niestandardowe

Po wprowadzeniu i zapisaniu może zostać wyświetlona wersja sprzężona, z pominięciem pól niepotrzebnych.

Jak powiedziałem, działa to we wszystkich krajach, w których mamy oprogramowanie, i jest wynikiem rozwoju od 1989 roku.

Mam nadzieję, że to jakoś pomaga lub przynajmniej zapewnia inny wgląd.

Jak już wspomniano, najbardziej uniwersalnym (ale niepraktycznym do sprawdzenia i być może najmniej użytecznym) jest pojedyncze duże pole Unicode.

Możesz oddzielić kraj od reszty adresu i zapisać go jako kod kraju ISO. Normalizowałoby to kraj i oferowało pewną użyteczność w sprawdzaniu reszty adresu.

Możesz także oddzielić kod pocztowy, czyli kod pocztowy, od reszty adresu. Byłoby to również przydatne w sprawdzaniu poprawności pozostałej części adresu i mogłoby być pomocne (choć nieprecyzyjne) w geolokalizacji. Na przykład: w Kanadzie możesz jednoznacznie zidentyfikować dowolny adres, podając tylko kod pocztowy i numer ulicy (inaczej numer domu); może nie być to prawdą we wszystkich krajach.

Dedykowanie pól stanom / prowincjom lub miastom staje się coraz bardziej problematyczne z powodu różnic w sposobie formułowania adresu przez każdy kraj. Skonfigurowałem tabele adresowe zawierające takie pola, ponieważ początkowa grupa odbiorców skupia się na Ameryce Północnej, wiedząc, że publiczność międzynarodowa stanowiłaby problem z dopasowaniem się. W większości przypadków mogą być „rogaci w buty”, ale jest to niewygodny i potencjalnie podatny na kompromisy - zdecydowanie nie uniwersalny.

W przeciwieństwie do odpowiedzi Mitchdava odradzam korzystanie z biblioteki Google. Przeszukałem repozytorium w poszukiwaniu różnych miejsc międzynarodowych z niekonwencjonalnymi schematami adresowania, mając nadzieję na znalezienie danych z testu jednostkowego, ale niepokojąco znalazłem zero trafień w całym repozytorium.

Myślę, że najlepszym rozwiązaniem jest traktowanie adresu jako tekstu wielowierszowego o dowolnym kształcie. Boli mnie to, że może nie można zweryfikować wszystkich adresów, ale niektóre formaty adresowania są naprawdę dziwne i być może nieprzewidziane, a na końcu odpowiedzialność za wypełnienie prawidłowego adresu spoczywa na użytkowniku, aw większości aplikacji użytkownik ponosi wszelkie negatywne konsekwencje wypełnienia formularza błędny adres.

Być może możesz użyć walidatora, aby uzyskać ostrzeżenie , ale nic więcej. Ale nie odrzucaj adresów, które nie sprawdzają poprawności, ponieważ w przeciwnym razie możesz stracić niektórych klientów. Co prowadzi do pytania, w jaki sposób przekazać ostrzeżenie użytkownikowi w taki sposób, aby poinformował, że jeśli użytkownik mieszka w obszarze o dziwnym formacie adresu, można bezpiecznie zignorować ostrzeżenie ...

Jak mówisz, każdy adres na Ziemi jest tylko długi lub ...

https://what3words.com

To, co 3 słowa, to algorytm (więc nie baza danych, więc można go osadzić w czymkolwiek), który może zdefiniować łatę 3x3 metrów w dowolnym miejscu na Ziemi.

Tonga i kilka innych stanów przyjęło go jako swój system kodów pocztowych, chociaż nie zastąpi go jako nakładka, jest dość fajny, bardzo dobrze zbudowany i przemyślany.