Rozdzielnie nazw ulic

0

Cześć,
W jaki sposób mogę rozdzielić ciąg znaków np z nazwą ulicy w bazie mssql na 3 oddzielnie kolumny „ulica”, „nr domu”, „nr mieszkania”.

Np ulica „Macieja Rataja 10/2B”
Tak jak tym przypadku myślałem coś takiego „LEFT(‚Macieja Rataja 10’, PATINDEX(‚%[0-9]%’, ‚Macieja Rataja 10’)-2)

lub „1 Maja 38/2”
To w tym wypadku to bez sensu bo złapie od razu 1 cyfrę.

5

Tak, żeby dało się sensownie obsłużyć wszystkie przypadki to nie się nie da, więc lepiej nie tracić czasu.

0

W jaki sposób mogę rozdzielić ciąg znaków np z nazwą ulicy w bazie mssql na 3 oddzielnie kolumny „ulica”, „nr domu”, „nr mieszkania”.

To, że te dane zostały tak zapisane w bazie to dług techniczny, który trzeba teraz spłacić. Może trzeba ręcznie przepisać wszystko sprawdzając po kolei ulice?

Ew. wziąć jakieś api do map (Google Maps?) i wyszukiwać nazwy ulic, żeby móc oddzielić faktyczną ulicę. Ale i tak wątpię, czy w ten sposób można by było obsłużyć w 100% wszystkie dziwne przypadki.

„nr domu”, „nr mieszkania”.

a jak ktoś zamiast ul. Koguta 1/10 napisze ulica Koguta 1 m. 10?

I chyba czasem niektóre adresy mają kilka budynków naraz w adresie, tak mi się wydaje przynajmniej.

Albo np. jakiś czas temu byłem na rozmowie w firmie, która miała ulicę, numer ale miała też "budynek E " w adresie, bo pod jednym adresem było wiele budynków.

3

Ogólnie dłubanina i chleb powszedni przy migracji danych :-)

Jeśli chodzi o Polskę, to:

  1. możesz spróbować użyć dostępnych rejestrów: http://eteryt.stat.gov.pl/eTeryt/rejestr_teryt/ogolna_charakterystyka_systemow_rejestru/ogolna_charakterystyka_systemow_rejestru.aspx
  2. niektóre narzędzia ETL potrafią w "probabilistic matching".

Proces mógłby wyglądać tak:

  1. rozbudowujesz model o "elementy terytowe"
  2. synchronizujesz swoje słowniki adresowe danymi z teryta
  3. modyfikujesz procesy by korzystały z rejestrów teryta (API, formatki, ) i nowe dane będą już "dobre"
  4. stare dane naprawiasz przez dopasowywanie adresów do identyfikatorów terytowych - jest to proces iteracyjny, dla którego masz jakieś kryterium stopu (np. chcę poświęcić X czasu i wydać max. Y PLN)
    Iteracja#1 - "dokładne dopasowania" - 70%
    Iteracja#2 - analizujesz niedopasowane i wymyślasz kolejne dokładne reguły - 15% itd.
    ...
    Iteracja#N - "probabilistic matching"
0

podaj jakiś screen czy cokolwiek co tam masz, są funkcje które wybierają pewne wartości z poszczególnych komórek, może coś uda mi się pomóc

0

znalazłem takiego regexa do oddzielania nazw ulic i numerów ale nie wiem jak go przerobić tak aby pasował do polskich nazw ulic, bo u nas są takie ulice jak np

  1. 11 Listopada 123
  2. 11 Listopada 123a
  3. 11 Listopada 123/123
  4. 11 Listopada 123a/123
  5. 11 Listopada 123/123a
  6. 1 Maja 12
  7. Leśna 12
  8. Leśna 12/15
  9. Leśna 12a/15
  10. Leśna 12/15a
  11. gen. Jakiegoś Tam 123/123
  12. Zielonych Ludków 444/432
  13. Rondo Czegoś Tam 123
  14. Rondo Czegoś Tam 123/123
    etc...

nie wiem czy nie przesadziłem ale z niektórymi się spotkałem

0

Chyba rozwiązałem problem z ulicami typu
Dywizjonu 303 lub Bitwy Warszawskiej 1920
zanim podam rozwiązanie wraz z procedurą rozdzielania tych adresów na poszczególne komórki to chciałbym was prosić abyście mi napisali różne kombinacje adresów jakie można spotkać, ponieważ mogłem nie wszystkie przewidzieć

W swoich doświadczeniach przyjąłem takie adresy
1 sierpnia 20
1 sierpnia 20/30
1 sierpnia 20a/30
1 sierpnia 20/30a
1 sierpnia 20/30a 7
1 sierpnia 20 7
1 sierpnia 20 m7

Kwiatowa 20
Kwiatowa 20a
Kwiatowa 20/30
Kwiatowa 20a/30
Kwiatowa 20/30a
Kwiatowa 20/30 2
Kwiatowa 20a/30 2
Kwiatowa 20/30a 2

Dywizjonu 303 20
Dywizjonu 303 20/30
Dywizjonu 303 20a/30
Dywizjonu 303 20/30a
Dywizjonu 303 20/30 1
Dywizjonu 303 20a/30 1
Dywizjonu 303 20/30a 1

Bitwy Warszawskiej 1920 30
Bitwy Warszawskiej 1920 20/30
Bitwy Warszawskiej 1920 20a/30
Bitwy Warszawskiej 1920 20/30a
Bitwy Warszawskiej 1920 20 8
Bitwy Warszawskiej 1920 20a/30 8
Bitwy Warszawskiej 1920 20/30a 8

gen. Jakiegoś Tam 30
gen. Jakiegoś Tam 30a
gen. Jakiegoś Tam 30/20
gen. Jakiegoś Tam 30a/20
gen. Jakiegoś Tam 30/20a
gen. Jakiegoś Tam 30a/20 5
gen. Jakiegoś Tam 30/20a 5

Astronautów kosmosu 10
Astronautów kosmosu 10/20
Astronautów kosmosu 10a/20
Astronautów kosmosu 10/20a
Astronautów kosmosu 10/20 7
Astronautów kosmosu 10/20a 7
Astronautów kosmosu 10a/20 7

Rondo czegoś tam 1
Rondo czegoś tam 1/15
Rondo czegoś tam 1a/15
Rondo czegoś tam 1/15a
Rondo czegoś tam 1/15 8
Rondo czegoś tam 1a/15 8
Rondo czegoś tam 1/15a 8

0

W Krakowie jest ulica o nazwie ul. 28 lipca 1943 także ten...
Jakby się ktoś zastanawiał to data pacyfikacji Woli Justowskiej.

0

Ostrożnie, np. w W-wie numer1/numer2 często numer2 nie oznacza mieszkania ale drugą część adresu. Nie pytaj mnie dlaczego tak jest, słoik jestem.

Numery to jeszcze nie wszystko
https://warszawa.wyborcza.pl/warszawa/1,34862,19971558,absurdy-z-nazwami-ulic-w-warszawie-gdzie-jest-skrzyzowanie.html

2

Chyba pora iść po popcorn, bo ten serial będzie miał jeszcze wiele odcinków. ;)

4

Dziś jest to ostatni odcinek serialu pt. "rozdzielanie nazw ulic". Mam nadzieję, że będzie budził zainteresowanie i żywą dyskusję.

Zanim zacznę podawać rozwiązania problemów, chciałbym zaznaczyć, że nie wszystko udało mi się rozwiązać, ponieważ są przypadki w których zależy to od interpretacji gdzie jest numer domu a gdzie jest numer mieszkania jak w komentarzach powyżej. Chodzi o to, że są przypadki w których niezwykle ciężko jest określić co jest numerem domu, a co numerem mieszkania jak podał @BraVolt na przykładzie adresu Marszałkowska 104/122 - tutaj wszystkie numery są określone jako numer budynku bez podziału na coś takiego jak numer mieszkania, co w konsekwencji będzie błędem przy rozdzielaniu tego typu adresów gdyż 122 zostanie zakwalifikowane jako "numer mieszkania" i tego niestety nie da się uniknąć przy hurtowym rozdzielaniu adresów.

Dla uproszczenia pominąłem też wiele rodzajów zapisów adresów gdyż nie są one jakoś ustandaryzowane i pominąłem przedrostki "ul", "al" i takie zapisy jak niżej nie były przeze mnie brane pod uwagę:

  1. ul Jagodowa 14 m 5
  2. ul. Jagodowa 14/m5
  3. al. Zbyszka z Bogdańca 14/123/234
  4. Dywizjonu 303 20/30a/1

itp... ponieważ tych kombinacji byłoby od %$^#

W swoich rozważaniach do rozdzielania nazw ulic przyjąłem takie przykłady jak - oto kompletna lista

1 sierpnia 20
1 sierpnia 20/30
1 sierpnia 20a/30
1 sierpnia 20/30a
1 sierpnia 20/30a 7
1 sierpnia 20 7
1 sierpnia 20 m7

Kwiatowa 20
Kwiatowa 20a
Kwiatowa 20/30
Kwiatowa 20a/30
Kwiatowa 20/30a
Kwiatowa 20/30 2
Kwiatowa 20a/30 2
Kwiatowa 20/30a 2
Kwiatowa 20/20 m2

Dywizjonu 303 20
Dywizjonu 303 20/30
Dywizjonu 303 20a/30
Dywizjonu 303 20/30a
Dywizjonu 303 20/30 1
Dywizjonu 303 20a/30 1
Dywizjonu 303 20/30a 1

Bitwy Warszawskiej 1920 30
Bitwy Warszawskiej 1920 20/30
Bitwy Warszawskiej 1920 20a/30
Bitwy Warszawskiej 1920 20/30a
Bitwy Warszawskiej 1920 20 8
Bitwy Warszawskiej 1920 20a/30 8
Bitwy Warszawskiej 1920 20/30a 8

gen. Jakiegoś Tam 30
gen. Jakiegoś Tam 30a
gen. Jakiegoś Tam 30/20
gen. Jakiegoś Tam 30a/20
gen. Jakiegoś Tam 30/20a
gen. Jakiegoś Tam 30a/20 5
gen. Jakiegoś Tam 30/20a 5

Astronautów kosmosu 10
Astronautów kosmosu 10/20
Astronautów kosmosu 10a/20
Astronautów kosmosu 10/20a
Astronautów kosmosu 10/20 7
Astronautów kosmosu 10/20a 7
Astronautów kosmosu 10a/20 7

Rondo czegoś tam 1
Rondo czegoś tam 1/15
Rondo czegoś tam 1a/15
Rondo czegoś tam 1/15a
Rondo czegoś tam 1/15 8
Rondo czegoś tam 1a/15 8
Rondo czegoś tam 1/15a 8

28 lipca 1943 2
28 lipca 1943 2/12
28 lipca 1943 2a/12
28 lipca 1943 2/12a
28 lipca 1943 2/12 2
28 lipca 1943 2a/12 2
28 lipca 1943 2/12a 2

Byłych Więźniów Twierdzy Zakroczymskiej 11
Byłych Więźniów Twierdzy Zakroczymskiej 11/15
Byłych Więźniów Twierdzy Zakroczymskiej 11a/15
Byłych Więźniów Twierdzy Zakroczymskiej 11/15a
Byłych Więźniów Twierdzy Zakroczymskiej 11/15 2
Byłych Więźniów Twierdzy Zakroczymskiej 11a/15 2
Byłych Więźniów Twierdzy Zakroczymskiej 11/15a 2

Chciałbym jeszcze wspomnieć, że przy pełnym adresie takim jak Dywizjonu 303 20/30a 1 przyjąłem założenie, że numerem mieszkania będzie 1 a poprzedzająca część 20/30a będzie numerem domu, aby uniknąć i tym samym częściowo rozwiązać problem z adresem typu Marszałkowska 104/122 w którym część liczbowa określa tylko budynek.

Do rozwiązania problemu użyłem wyrażeń regularnych REGEX i pracowałem na silniku bazodanowym MariaDB (xampp).

Zanim zaczniemy cokolwiek dalej robić, to polecam zrobienie kopii zapasowej tabeli która zawiera pełne dane adresowe. Jak to zrobić ?

1. Tworzymy nową kolumnę obok kolumny źródłowej

ALTER TABLE `tabela2` ADD COLUMN `pAdresCopy` varchar(100) CHARACTER SET utf8 COLLATE utf8_polish_ci AFTER `pAdres`

2. Robimy kopię zapasową tabeli źródłowej

UPDATE `tabela2` SET `pAdresCopy`=`pAdres`

Jak już mamy zrobioną kopię kolumny z adresami, to polecam całą robotę wykonać na kopii zapasowej.

Ja u siebie robiłem na oryginale, gdyż po prostu cała baza była testowa i nie miałem tam żadnych istotnych danych które mógłbym przez nieuwagę zmienić.

Chcąc rozdzielić nazwy ulic przyjrzałem się ich wszystkich znakom nawet tym białym, ponieważ ułatwiają one rozwiązanie problemu na części składowe typu ulica, numer domu i numer mieszkania, a więc do rzeczy.

Na początku, trzeba WYDZIELIĆ WSZYSTKIE nazwy ulic typu -> Kwiatowa, Dywizjonu 303, Bitwy Warszawskiej 1920, 28 lipca 1943, 1 sierpnia, 1 Maja etc... i selekcjonuję wszystkie potencjalne nazwy wyrażeniem regularnym które jest przedstawione w pkt. 1

Podczas pisania tego posta, odkryłem, że problemem może być nazwa ulicy np Jana Pawła 2 chociaż wydaje mi się, że powinno być to zapisane jako Jana Pawła II poniższe wyrażenie radzi sobie z drugą formą zapisu, natomiast z pierwszą już nie.

1. Wyrażenie regularne wygląda tak

^\S*\b(\D*[^\d\s\/])\b\s\d[^\/]\d{1,4}\s|^\S*\b(\D*[^\d\s\/])\b

2. Zapytanie SQL wygląda tak

SELECT `pAdres`, 
REGEXP_SUBSTR(`pAdres`,'^\\S*\\b(\\D*[^\\d\\s\\/])\\b\\s\\d[^\\/]\\d{1,4}\\s|^\\S*\\b(\\D*[^\\d\\s\\/])\\b') AS 'Ulica' 
FROM `tabela2`

3. A wyniki zapytania SQL wyglądają tak
screenshot-20200405154456.png
screenshot-20200405154503.png
screenshot-20200405154511.png

Skoro wiemy jak będą wyglądały wyniki po rozdzieleniu, to czas na załadowanie wyników do kolumny pUlica. Zapytanie które to robi wygląda jak niżej

UPDATE `tabela2`
SET 
`pUlica`= REGEXP_SUBSTR(`pAdres`,'^\\S*\\b(\\D*[^\\d\\s\\/])\\b\\s\\d[^\\/]\\d{1,4}\\s|^\\S*\\b(\\D*[^\\d\\s\\/])\\b')

Po wykonaniu tego polecenia nazwy samych ulic powinny zapisać się w kolumnie pUlica (zamieszczam tylko fragment wyników)

screenshot-20200405175748.png

teraz przyszedł czas na rozdzielenie numerów domów od numerów mieszkań - jak wspomniałem na początku, w pewnych przypadkach należy się pogodzić z tym, że numer budynku będzie rozdzielony na numer budynku i na numer mieszkania...

Zauważyłem, że są cztery rodzaje numerów budynków i numerów mieszkań (jeżeli chodzi o przypadki które uwzględniłem do rozwiązania problemu i rozdzielania pełnego adresu na poszczególne składowe)

  1. 20 - po prostu sam numer budynku
  2. 20 5 - numer budynku i numer mieszkania, ten przypadek jest problematyczny, zważywszy na to, jak wystąpi w takiej postaci jak Dywizjonu 303 20 5 - wtedy trzeba uciekać się do sztuczki ze znakiem podkreślenia _ w taki sposób, aby adres wyglądał jak jeden długi string i miał na końcu części numerycznej, która zawiera się nazwie ulicy, ten znak, aby łatwiej było wyciągnąć numer budynku np Dywizjonu_303_20 5 i mamy już wzorzec dzięki któremu już łatwo oznaczyć numer domu/budynku czyli **_20 ** - ZA liczbą 20 jest "spacja"
  3. 20/30 - numer budynku i numer mieszkania, ten przypadek również jest problematyczny w zależności od kontekstu jak wspominałem na początku (dla uproszczenia pomijam tutaj literę dla budynku lub dla mieszkania np 20a/30 lub 20/30a może być i 20a/30a)
  4. 20/30 5 - numer budynku i numer mieszkania

rozdzielenie numeru budynku od numeru mieszkania będzie polegać na znalezieniu jakiegoś łącznika np ukośnika / lub białego znaku tj. "spacji" oraz wykorzystaniu go do odseparowania z lewej lub z prawej strony i usunięcia go (o co chodzi, to zobaczycie dalej).

##Rozdzielanie numeracji z pkt. 1.
W całym adresie np Kwiatowa 20 szukamy liczby która występuje na końcu - niech będzie to jak w przykładzie 20. Tą liczbę interpretuję jako numer mieszkania, ponieważ wskazuje nam bezpośrednio lokal do którego należy się udać - może to być dom jednorodzinny bądź mieszkanie w bloku. Poniżej podaję wyrażenie regularne do oznaczenia szukanego wzorca. W znalezieniu tej liczby pomocne są następujące szczegóły - liczba znajduje się na samym końcu adresu i przed tą liczbą występuje "spacja".

1. Wyrażenie regularne które szuka takich liczb to - UWAGA, przed \d jest "spacja"

 \d{1,4}$| m\d{1,4}$| \d{1,4}[a-zA-Z]$| m\d{1,4}[a-zA-Z]

2. Zapytanie SQL

SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,' \\d{1,4}$| m\\d{1,4}$| \\d{1,4}[a-zA-Z]$| m\\d{1,4}[a-zA-Z]') AS 'Numer mieszkania' 
FROM `tabela2`

3. Wyniki zapytania (dla uproszczenia podaję częściowy wynik)

screenshot-20200407143316.png

4. Przyszedł czas do załadowania numerów mieszkania do kolumny pNr_mieszkania

UPDATE `tabela2`
SET
`pNr_mieszkania` = REGEXP_SUBSTR(`pAdres`,' \\d{1,4}$| m\\d{1,4}$| \\d{1,4}[a-zA-Z]$| m\\d{1,4}[a-zA-Z]')

5. Rezultat (tylko częściowe wyniki)

screenshot-20200407143620.png

Rozdzielenie numeracji z pkt. 2

Ten punkt polecam wykonać na samym końcu, gdyż jest on jednym z najtrudniejszych etapów i mogą być problemy z rozdzieleniem numerów domów od numerów mieszkań. Przed przystąpieniem do realizacji tej części wykonaj dodanie gwiazdek * z punktu 3 lub 4.

Weźmy na warsztat pełny adres taki jak Dywizjonu 303 20 5 lub Bitwy Warszawskiej 1920 20 5. Tutaj naszym zadaniem będzie oznaczenie pełnej nazwy ulicy w taki sposób aby to był jeden string który zawiera widoczne znaki (zdaję sobie sprawę, że "spacja" też jest znakiem ale bardziej chodzi mi o to, aby były pewne różnice w odpowiednich miejscach i tym samym ułatwić sobie robotę w rozdzieleniu właściwego numeru budynku od pełnej nazwy ulicy). Przytoczone przykłady ulic doprowadzamy do takiej postaci jak niżej:

a) Dywizjonu_303_20 5
b) Bitwy_Warszawskiej_1920_20 5
c) Kwiatowa_20 5

Dlaczego tak ? No w sumie tak mi przyszło do głowy bo taka fantazja mnie naszła :D a po drugie, wystarczy jedno krótkie wyrażenie regularne które wyszuka nam według wzorca znak _ potem liczbę, a następnie spację lub inny znak np # który znajduje się za tą liczbą i już mamy oznaczony numer budynku ;) wtedy mamy bardzo ułatwione zadanie i pozostanie nam tylko oddzielenie numeru domu od pełnej nazwy ulicy.

Żeby nie kopać się z pojedynczym wklejaniem zapytania po to, aby dodać podkreślniki w każdym rekordzie znajdującym się w tabeli, to utworzyłem funkcję która to robi automatycznie. Nie było łatwo ale warto było nad tym posiedzieć. Nie będę tłumaczył co oznaczają poszczególne fragmenty kodu, bo artykuł byłby zbyt długi ale w komentarzach zawarłem najistotniejsze informacje. Poniżej funkcja:

Funkcja przyjmuje jeden parametr typu INT jest to ID rekordu który trzeba zmienić

1. Funkcja SQL która dodaje znak _ (najpierw sprawdzamy czy zapytanie zadziała właściwie wywołując ją z jednym parametrem który przyjmuje)

DELIMITER //
CREATE FUNCTION ZmienWybranyTekst(id int) -- nazwa funkcji wraz z argumentem który podajesz w wywołaniu
RETURNS int DETERMINISTIC READS SQL DATA
	BEGIN 
    	DECLARE DoZmiany varchar(100) DEFAULT '';
    	DECLARE WydobytaWartosc varchar(100) DEFAULT '';
        DECLARE WstawianieWartosci varchar(100) DEFAULT '';
        DECLARE WstawianieWartosci2 varchar(100) DEFAULT '';
        DECLARE PoZmianie varchar(100) DEFAULT '';
        
        SET DoZmiany=(SELECT `pAdres` FROM `tabela2` WHERE `pID`=id);
        
        SET WydobytaWartosc=(REGEXP_substr(DoZmiany,'^\\S*\\b\\D*[^\\d\\/]\\b\\d[^\\/]\\d{1,4}|^\\S*\\b\\D*[^\\d\\/ ]\\b')); 
            
        SET WstawianieWartosci=(REGEXP_replace(WydobytaWartosc,'\\b \\b','_'));
        SET WstawianieWartosci2=(rpad(mid(WstawianieWartosci,1,char_length(WstawianieWartosci)),char_length(WstawianieWartosci)+1,'_'));
        
        SET PoZmianie=(REGEXP_replace(DoZmiany,'\\S*\\b\\D*[^\\d\\/]\\b\\d[^\\/]\\d{1,4} |^\\S*\\b\\D*[^\\d\\/ ]\\b ',WstawianieWartosci2));
        
        UPDATE `tabela2` SET `pAdres`=PoZmianie WHERE `pID`=id; -- te zapytanie musisz dostosować do swojej tabeli
            
       	RETURN 0; 
   	END; //
DELIMITER ;

Jeżeli chcesz tą funkcję zaadaptować do swojej tabeli, to musisz w zapytaniu UPDATE zmienić nazwę tabeli i kolumny na swoją. Następnie należy kliknąć przycisk WYKONAJ w xampp

2. Wywołanie funkcji z jednym parametrem

SELECT ZmienWybranyTekst(55) AS 'Wywołanie funkcji';

3. Kilka przykładowych poleceń które ułatwią zarządzanie funkcjami

a) wywołanie funkcji

SELECT nazwa_funkcji();

b) sprawdzenie statusu funkcji

SHOW FUNCTION STATUS;

c) usuwanie funkcji - to jest opcjonalne i przydaje się w sytuacji gdy funkcja nie jest już potrzebna

DROP FUNCTION nazwa_funkcji;

Powyższa funkcja wywołana jest tylko JEDEN RAZ - w sumie mogłem napisać jedną która wywoła się setki razy ale musiałem się nauczyć pisać te funkcje i napisałem drugą funkcję która wywoła powyższą setki razy tj. tyle razy ile jest rekordów w bazie.

DELIMITER //
CREATE FUNCTION UruchomZmianeTekstu() -- nazwa funkcji która nie przyjmuje argumentów
RETURNS int DETERMINISTIC MODIFIES SQL DATA
    BEGIN
    	DECLARE done int DEFAULT false;
        DECLARE i int DEFAULT 0;
    	DECLARE id int DEFAULT 0;
    	DECLARE cur CURSOR FOR SELECT `pID` FROM `tabela2`;
        
        DECLARE CONTINUE HANDLER FOR NOT found SET done=true;
        
        OPEN cur;
        petla: LOOP
        	FETCH cur INTO id;
            IF(done=true) THEN
            	LEAVE petla;
            ELSE
            	SET i=(SELECT ZmienWybranyTekst(id)); -- tutaj jest wywołanie funkcji która dodaje znaki _
            END IF;
        END LOOP;
        
        CLOSE cur;
        
        RETURN i;
    END; //
    
DELIMITER ;

4. Wywołanie funkcji która doda znaki _ do wszystkich rekordów

SELECT UruchomZmianeTekstu() AS 'Wywolanie funkcji';

5. Rezultat tego zapytania (tylko częściowy wynik)

screenshot-20200414112333.png

No i fajnie wyszło :D

6. Rozdzielenie numerów domów od pełnego adresu zapytaniem SQL (wykorzystując znak _ i "spację" między liczbą "_20 ")

SELECT `pAdres`, 
REGEXP_SUBSTR(`pAdres`,'_\\d{1,4} ') AS 'Numer domu' 
FROM
`tabela2`

7. Rezultat zapytania

screenshot-20200414122043.png

8. Załadowanie numerów domów do właściwej kolumny zapytaniem SQL

UPDATE `tabela2`
SET `pNr_domu`=REGEXP_SUBSTR(`pAdres`,'_\\d{1,4} ')
WHERE `pNr_domu` IS null;

9. Rezultat zapytania SQL

screenshot-20200414123515.png

10. Usunięcie znaków _ i ewentualnych spacji które na pewno są (wystarczy zamiast znaku _ wpisać znak "spacji")

UPDATE `tabela2` 
SET `pNr_domu`= REPLACE(`pNr_domu`,'_','') 

11. Rezultat

screenshot-20200414124643.png

I już wszystko jest rozdzielone, tak jak powinno być.

Jeżeli ten punkt został wykonany na samym końcu, to można usunąć skopiowaną kolumnę.

ALTER TABLE `tabela2` DROP COLUMN `pAdresCopy` 

Rozdzielanie numeracji z pkt. 3

Tutaj szukamy numeru domu z zapisu 20/30 lub 20a/30 lub 20/30a lub 20a/30a i pojawiają się tutaj schody, ponieważ w tym przypadku szukamy czegoś co jest PRZED znakiem / tj. 20/ itp...

Wyrażenie regularne które chcemy użyć, zaznaczy nam wszystko co do tego wzorca pasuje, nawet część z zapisu 20/30 5 gdzie 20/30 to numer budynku, a 5 to jest numer domu. A w tym przypadku tego nie chcemy.

Jak uniknąć takiej pułapki ? Odpowiemy w następnym odcinku :D (żart).

Wpadłem na pomysł aby napisać wyrażenie regularne które obejmie TYLKO zapis 20/30 5. I tam gdzie występują te zapisy, to za pomocą zapytania SQL dodam w jakieś wygodne miejsce np "gwiazdkę" aby te wpisy nam się nie pomieszały i było łatwiej wyselekcjonować wyrażeniem regularnym to, co nas interesuje.

1. Wyrażenie regularne które oznacza nam numeracje w adresie według pożądanego wzorca to:

\d{1,4}[a-zA-Z][\/]\d{1,4} \d{1,3}|\d{1,4}[\/]\d{1,4}[a-zA-Z] \d{1,3}|\d{1,4}[\/]\d{1,4} \d{1,3}|\d{1,4}[\/]\d{1,4} m\d{1,3}|\d{1,4}[\/]\d{1,4}\/\d{1,3}

2. Zapytanie SQL

SELECT `pAdres`, 
REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[a-zA-Z][\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4}[a-zA-Z] \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} m\\d{1,3}|\\d{1,4}[\\/]\\d{1,4}\\/\\d{1,3}') AS 'Numer' 
FROM
`tabela2`

3. Rezultaty zapytania

screenshot-20200405211844.png

Wiedząc, że wyrażenie regularne oznacza rekordy które chcemy, to czas je zamienić na */ a w tym celu użyjemy zapytania SQL

4. Zapytanie SQL

UPDATE `tabela2`
SET
`pAdres`= REPLACE(`pAdres`,'/','*/')
WHERE
LENGTH(REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[a-zA-Z][\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4}[a-zA-Z] \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} \\d{1,3}|\\d{1,4}[\\/]\\d{1,4} m\\d{1,3}|\\d{1,4}[\\/]\\d{1,4}\\/\\d{1,3}'))

5. Rezultat

screenshot-20200407164549.png

Po oznaczeniu adresów które chcemy wykluczyć z wyszukiwania wyrażeniem regularnym mamy już ułatwioną drogę wyselekcjonowania tego co chcemy.

6. Wyrażenie regularne które to robi

\d{1,4}[\/]|\d{1,4}[a-zA-Z][\/]

7. Zapytanie SQL

SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]') AS 'Numer domu' FROM `tabela2`

8. Rezultat zapytania (częściowe wyniki)

screenshot-20200407165639.png

Z tak wyselekcjonowanym numerem można przystąpić do załadowania tego numeru do kolumny zawierającej numer domu

9. Załadowanie rozdzielonych liczb do kolumny pNr_domu

UPDATE `tabela2`
SET `pNr_domu`=REGEXP_SUBSTR(`pAdres`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]')

10. Rezultat (częściowe wyniki)

screenshot-20200407170944.png

11. Usunięcie / zapytaniem SQL z kolumny pNr_domu

UPDATE `tabela2`
SET
`pNr_domu`= REPLACE(`pNr_domu`,'/','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_domu`,'\\d{1,4}[\\/]|\\d{1,4}[a-zA-Z][\\/]'))

12. Wyniki zapytania (częściowy wynik)

screenshot-20200407172807.png

Teraz zaczniemy rozdzielać numer mieszkania od numeru domu i w kolejnych punktach numer mieszkania zostanie przeniesiony do kolumny pNr_mieszkania. Do tego celu użyjemy wyrażenia regularnego które wyszukuje nam wszystko co znajduje się ZA znakiem / czyli /30, /30a itp... - ale UWAGA, tutaj też nie chcemy aby to wyrażenie uwzględniło nam wszystkie rekordy zawierające cokolwiek ZA znakiem / gdyż jest taki zapis jak 20/30 7 - tutaj też dodamy gwiazdkę w dogodne miejsce. Więc zrobimy to następującym zapytaniem SQL.

13. Zapytanie SQL

UPDATE `tabela2`
SET
`pAdres`= REPLACE(`pAdres`,'*/','*/*')
WHERE
LENGTH(REGEXP_SUBSTR(`pAdres`,'\\*[\\/]\\d{1,4}[a-zA-Z] \\d{1,4}|\\*[\\/]\\d{1,4} \\d{1,4}|\\*[\\/]\\d{1,4} m\\d{1,4}'))

14. Wynik zapytania

screenshot-20200407180839.png

Po takim oznaczeniu rekordów których nie chcemy brać pod uwagę, wyselekcjonujemy tylko te które nas interesują poniższym wyrażeniem regularnym

15. Wyrażenie regularne

[\/]\d{1,4}[a-zA-Z]|[\/]\d{1,4}

16. Zapytanie SQL

SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}') AS 'Numer mieszkania' FROM `tabela2`

17. Rezultat zapytania SQL

screenshot-20200407182035.png

18. Przeniesienie wyselekcjonowanego wzorca zapytaniem SQL do kolumny pNr_mieszkania

UWAGA - przed wykonaniem tego zapytania puste pola w kolumnie ustaw na wartość null takim zapytaniem

UPDATE `tabela2`
SET `pNr_mieszkania`=null
WHERE 
`pNr_mieszkania`=''

po ustawieniu pól na wartość NULL czas wypełnić te pola właściwymi wartościami

UPDATE `tabela2` 
SET `pNr_mieszkania`= REGEXP_SUBSTR(`pAdres`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}') 
WHERE `pNr_mieszkania` IS null 

dlaczego w zapytaniu jest IS null ? Dlatego, że jak nie będzie tego warunku, to wykonanie tego zapytania spowoduje, że wszystko to, co tam wcześniej wstawiliśmy usunie.

19. Rezultat dodania numerów mieszkania do kolumny pNr_mieszkania

screenshot-20200407214658.png

20. Usuwamy / zapytaniem SQL

UPDATE `tabela2`
SET
`pNr_mieszkania`= REPLACE(`pNr_mieszkania`,'/','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_mieszkania`,'[\\/]\\d{1,4}[a-zA-Z]|[\\/]\\d{1,4}'))

21. Rezultat

screenshot-20200407220134.png

##Rozdzielanie numeracji z pkt. 4
i teraz przyszła kolej na ostatnią część przy rozdzielaniu - chodzi o zapis 20/30 5.
W pierwszej kolejności przeniesiemy sobie wszystkie zapisy które zawierają gwiazdkę PRZED / oraz PO / -> */*. Najpierw wykonamy zapytanie SQL aby upewnić się czy wyrażenie regularne właściwie oznacza to co chcemy.

1. Wyrażenie regularne

\d{1,4}\*\/\*\d{1,4}[a-zA-Z]|\d{1,4}\*\/\*\d{1,4}|\d{1,4}[a-zA-Z]\*\/\*\d{1,4}

2. Zapytanie SQL

SELECT `pAdres`, REGEXP_SUBSTR(`pAdres`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}') AS 'Numer mieszkania **' 
FROM `tabela2`

3. Rezultat zapytania (wyniki są częściowe)

screenshot-20200407230606.png

4. Wyselekcjonowane rekordy przenosimy do kolumny pNr_domu
Przed wykonaniem tego polecenia polecam ustawienie we wszystkich polach które są puste - wartość NULL

UPDATE `tabela2` 
SET `pNr_domu`= REGEXP_SUBSTR(`pAdres`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}') 
WHERE `pNr_domu` IS null 

5. Rezultat wykonanego przeniesienia

screenshot-20200407230526.png

6. Na koniec pozostaje pozbyć się "gwiazdek"

UPDATE `tabela2`
SET
`pNr_domu`= REPLACE(`pNr_domu`,'*','')
WHERE
LENGTH(REGEXP_SUBSTR(`pNr_domu`,'\\d{1,4}\\*\\/\\*\\d{1,4}[a-zA-Z]|\\d{1,4}\\*\\/\\*\\d{1,4}|\\d{1,4}[a-zA-Z]\\*\\/\\*\\d{1,4}'))

7. Rezultat

screenshot-20200407230915.png

To już wszystko w związku z rozdzielaniem nazw ulic, numerów domów i numerów mieszkań z pełnego adresu. Kolumnę która zawiera pełny adres można usunąć, w przypadku jeżeli jest już niepotrzebna. Twoja tabela jest już odpicowana i możesz spać spokojnie :D

Spójrz jeszcze na pkt. 2 który wykonałem na samym końcu, z tego względu iż jest to najtrudniejszy etap i wszystko będzie wykonane jak należy.

Nie da się ? Te, to poczymaj mi piwo... :D

Proszę o krytykę i inne uwagi, nawet te pozytywne ;) no i ciekawe co na to sam zainteresowany ? :D bo jakoś długo go nie widać ;)

2

Wysiłek godny lepszego celu ;) Im więcej regułek, tym większe pokrycie.

Grójecka 80/102 - 102 to nie numer mieszkania i regexpem nie rozróżnisz znaczenia symbolu.
Mnichowo 123 (nie ma ulic)

Warianty można mnożyć (i regexpy też):
ul. Bitwy Warszawskiej 1920 r. 7A m. 10
ul. Bitwy Warszawskiej 1920 roku 7A m. 10a/3
ul. Bitwy Warszawskiej 1920 r. nr 12

2

@zkubinski: W tym cała trudność, że ciężko przetwarzać pola typu "free text".
Możesz zerknąć do tego co publikuje Poczta Polska: https://www.poczta-polska.pl/hermes/uploads/2013/11/spispna.pdf (albo wspomniane wcześniej zasoby rejestru Teryt).
W końcu PP powinna umieć dostarczać pod różne adresy.

Może warto zacząć od końca i wychodzić od kodu pocztowego -> nazwa ulicy, wycinać nazwę ulicy z adresu i resztę traktować jako numer lokalu? Nie wiem.

1

Na marginesie wątku.
Któreś z wdrożeń rządowych (z otoczenia KRS chyba) domyślam się wobec ogromu błędów jakie mogą / zachodzą, dla czterech miast w PL (Wawa, Łodź i jeszcze dwa) wymaga wybrania ulicy z Terytu. Dla innych miejscowości (jeszcze) nie.

0
yarel napisał(a):

Wysiłek godny lepszego celu ;) Im więcej regułek, tym większe pokrycie.

Grójecka 80/102 - 102 to nie numer mieszkania i regexpem nie rozróżnisz znaczenia symbolu.
Mnichowo 123 (nie ma ulic)

Warianty można mnożyć (i regexpy też):
ul. Bitwy Warszawskiej 1920 r. 7A m. 10
ul. Bitwy Warszawskiej 1920 roku 7A m. 10a/3
ul. Bitwy Warszawskiej 1920 r. nr 12

Pierwszej Brygady
I Brygady
1szej Brygady

Chyba tzreba się architektonicznie przestawić z regexów:

  • z naszego posiadanego wpisu próbowac dopasować ulicę do jakiejś oficjalnej bazy "funkcja podobieństwa" czy "funkcja bliskości"
  • mając ulicę odciętą, przetwarzamy resztę

1 użytkowników online, w tym zalogowanych: 0, gości: 1