Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
здравствуйте, подскажите, можно хоть как-то связать такое:
create table addr_a (addr nvarchar(100))
create table addr_b (street nvarchar(100), building nvarchar(6), fraction nvarchar(5))

insert into dbo.addr_a (addr)
	values 
		  (N'ул.Некрасова, д.4/1')				-- 1
		, (N'Пр Пушкина, д 5')					-- 2
		, (N'Г.Дружбы 12 подъезд 1')				-- 3
		, (N'горка дружбы д.12 подъезд 2')			-- 3
		, (N'г. дружбы, д.12 4 подъезд')			-- 3
		, (N'Г Дружбы 14/5 подъезд 3')				-- 4
		, (N'Пр Строителей, д.11')				-- 5
		, (N'Стрителей 12')					-- -
		, (N'5 мкр., д.2')					-- 6

insert into dbo.addr_b(street, building, fraction)
	values
		  (N'НЕКРАСОВА', N'4', N'1')				-- 1
		, (N'ПУШКИНА', N'5', null)				-- 2
		, (N'ГОРКА ДРУЖБЫ', N'12', null)			-- 3	
		, (N'ГОРКА ДРУЖБЫ', N'14', N'5')			-- 4
		, (N'СТРОИТЕЛЕЙ', N'11', null)				-- 5
		, (N'МК.5', N'2', N'1')					--
		, (N'МК.5', N'2', null)					--
		, (N'ГОРКА ДРУЖБЫ', N'14', N'3')			--

select t1.* from addr_a t1
select t2.* from addr_b t2


то есть в таблице addr_b как бы правильно записанные адреса, как максимально увязаться со всеми в addr_a, согласно номеров в комментариях
28 сен 17, 16:38    [20829574]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Юлия97,

а никак.
1. Обрабатывать вручную.
2. Пить много водки, много плакать, много думать.
3. Сделать интерфейс, чтобы таблицу А можно было заполнить значениями только из таблицы Б. А если нельзя выполнить п.3., см п.2.
4. Изучить полнотекстовый поиск
5. Подключить сборку с регулярными выражениями.
6. Научиться разбивать тексты на токены
7. Узнать, что такое дистанция левенштейна, подключить сборку.
8. Понять, что без п.3 - это пустая трата времени. Повторить п.2
9. Познать дзен употребления водки и глубокого мышления. Написать анализатор на нейронных сетях. Создать скайнет.
10. Проснуться и начать с п.1.
28 сен 17, 19:35    [20830069]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
Руслан Дамирович,

ну я смотрю вы большой специалист по курительным смесям )))

Собственно, оно конечно же понятно, что в таком дурдоме сложно что-то предложить... Но, будем надеяться, что может у кого-то есть хоть какое-то решение, которое хоть что-то из этого сможет вытянуть )
28 сен 17, 20:06    [20830111]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Дедушка
Member

Откуда: Город трёх революций
Сообщений: 5112
Юлия97
Но, будем надеяться, что может у кого-то есть хоть какое-то решение, которое хоть что-то из этого сможет вытянуть )
есть такой сервис DaData (можно даже за бесплатно).
28 сен 17, 23:36    [20830498]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9123
Юлия97,

Это вам для дальнейшей доработки:
+
use master;
create database Test001;
go

use Test001;
go

create table addr_a
(
 id int identity constraint PK_addr_a primary key,
 addr nvarchar(100),
 ft_condition as N'"' + replace(replace(replace(replace(addr, N'.', N' '), N'/', N' '), N',', N' '), N' ', N'" or "') + N'"'
);

create table addr_b
(
 id int identity constraint PK_addr_b primary key,
 street nvarchar(100),
 building nvarchar(6),
 fraction nvarchar(5),
 ft_data as replace(replace(replace(isnull(street, '') + isnull(N' ' + building, '') + isnull(N' ' + fraction, ''), N'.', N' '), N',', N' '), N'/', N' ')
);

insert into dbo.addr_a (addr)
values 
 (N'ул.Некрасова, д.4/1')				-- 1
 , (N'Пр Пушкина, д 5')					-- 2
 , (N'Г.Дружбы 12 подъезд 1')				-- 3
 , (N'горка дружбы д.12 подъезд 2')			-- 3
 , (N'г. дружбы, д.12 4 подъезд')			-- 3
 , (N'Г Дружбы 14/5 подъезд 3')				-- 4
 , (N'Пр Строителей, д.11')				-- 5
 , (N'Строителей 12')					-- -
 , (N'5 мкр., д.2');					-- 6

insert into dbo.addr_b(street, building, fraction)
values
 (N'НЕКРАСОВА', N'4', N'1')				-- 1
 , (N'ПУШКИНА', N'5', null)				-- 2
 , (N'ГОРКА ДРУЖБЫ', N'12', null)			-- 3	
 , (N'ГОРКА ДРУЖБЫ', N'14', N'5')			-- 4
 , (N'СТРОИТЕЛЕЙ', N'11', null)				-- 5
 , (N'МК.5', N'2', N'1')					--
 , (N'МК.5', N'2', null)					--
 , (N'ГОРКА ДРУЖБЫ', N'14', N'3');			--

select * from dbo.addr_a;
select * from dbo.addr_b;
go

create fulltext catalog FTC1;
go

create fulltext index on dbo.addr_b (ft_data) key index PK_addr_b on FTC1 with stoplist = off, change_tracking = auto;
go

while fulltextcatalogproperty('FTC1', 'PopulateStatus') <> 0
 waitfor delay '00:00:01';
go

create function dbo.fnSearchSimilarAddresses
(
 @condition nvarchar(200)
)
returns @r table (id int)
as
begin
 insert into @r
  (id)
  select top (1) with ties
   [KEY]
  from
   containstable(dbo.addr_b, ft_data, @condition)
  order by
   [RANK] desc;

 return;
end;
go

select
 a.id, a.addr, b.id, b.street, b.building, b.fraction
from
 dbo.addr_a a outer apply
 dbo.fnSearchSimilarAddresses(a.ft_condition) s left join
 dbo.addr_b b on b.id = s.id;
go

alter database Test001 set single_user with rollback immediate;
use master;
drop database Test001;
go
28 сен 17, 23:50    [20830521]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
tip78
Member

Откуда: Москва
Сообщений: 986
Юлия97
здравствуйте, подскажите, можно хоть как-то связать такое:
create table addr_a (addr nvarchar(100))
create table addr_b (street nvarchar(100), building nvarchar(6), fraction nvarchar(5))

insert into dbo.addr_a (addr)
	values 
		  (N'ул.Некрасова, д.4/1')				-- 1
		, (N'Пр Пушкина, д 5')					-- 2
		, (N'Г.Дружбы 12 подъезд 1')				-- 3
		, (N'горка дружбы д.12 подъезд 2')			-- 3
		, (N'г. дружбы, д.12 4 подъезд')			-- 3
		, (N'Г Дружбы 14/5 подъезд 3')				-- 4
		, (N'Пр Строителей, д.11')				-- 5
		, (N'Стрителей 12')					-- -
		, (N'5 мкр., д.2')					-- 6

разбирайте первую таблицу, где можно на автомате, а оставшееся - руками
например:
выкиньте строчки без запятых
и те, которые не подошли под правила

г. дружбы
г дружбы
г.дружбы
ЭТО регексп (PHP): #г.+?\b(.+?)\b#iu
подъезды можно вычислить
домА...
например, можно вычислить, что в строке сначала 1-2 символа, а за ними обрыв и название улицы
ну как-то так
29 сен 17, 02:12    [20830626]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Юлия97
Руслан Дамирович,
ну я смотрю вы большой специалист по курительным смесям )))
Собственно, оно конечно же понятно, что в таком дурдоме сложно что-то предложить... Но, будем надеяться, что может у кого-то есть хоть какое-то решение, которое хоть что-то из этого сможет вытянуть )


Деточка, я в этой области съел собак и скурил мануалов столько, что вам столько водки не выпить.
Мои инструменты могут преобразовать адреса в вашем случае процентов так на 65%. И это очень хороший показатель - лучше только нейронные сети.
Но увы, написаны они были для одной конторы и поделиться ими - никак нельзя. Да и на их создание ушло около 2 лет. Реальных, мать-их, человеколет.

А потому изобретайте свои велосипеды - обратите внимание на пункты с 3 по 7 - в них квинтэссенция поиска адресов.
4 пункт вам уже предоставил invm.

P.S. А бардак, он не в адресах, он в головах.
29 сен 17, 10:43    [20831045]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3462
Юлия97
здравствуйте, подскажите, можно хоть как-то связать такое:
create table addr_a (addr nvarchar(100))
create table addr_b (street nvarchar(100), building nvarchar(6), fraction nvarchar(5))

insert into dbo.addr_a (addr)
	values 
		  (N'ул.Некрасова, д.4/1')				-- 1
		, (N'Пр Пушкина, д 5')					-- 2
		, (N'Г.Дружбы 12 подъезд 1')				-- 3
		, (N'горка дружбы д.12 подъезд 2')			-- 3
		, (N'г. дружбы, д.12 4 подъезд')			-- 3
		, (N'Г Дружбы 14/5 подъезд 3')				-- 4
		, (N'Пр Строителей, д.11')				-- 5
		, (N'Стрителей 12')					-- -
		, (N'5 мкр., д.2')					-- 6

insert into dbo.addr_b(street, building, fraction)
	values
		  (N'НЕКРАСОВА', N'4', N'1')				-- 1
		, (N'ПУШКИНА', N'5', null)				-- 2
		, (N'ГОРКА ДРУЖБЫ', N'12', null)			-- 3	
		, (N'ГОРКА ДРУЖБЫ', N'14', N'5')			-- 4
		, (N'СТРОИТЕЛЕЙ', N'11', null)				-- 5
		, (N'МК.5', N'2', N'1')					--
		, (N'МК.5', N'2', null)					--
		, (N'ГОРКА ДРУЖБЫ', N'14', N'3')			--

select t1.* from addr_a t1
select t2.* from addr_b t2


то есть в таблице addr_b как бы правильно записанные адреса, как максимально увязаться со всеми в addr_a, согласно номеров в комментариях


Сколько записей в таблицах?
Это разовое преобразование или оно должно работать в процессе изменения этих таблиц?

Почитайте советы, которые здесь даны, и определяйтесь, помня, что в вашем случае одного хорошего решения нет, потому, что кроме вашей головки и головок коллег никто не в курсе:
N'Стрителей 12' - это N'СТРОИТЕЛЕЙ'

МК 5 - что это 5 мкр., тем более, что если не настроить, то FTS цифры не идексирует.

г дружбы, г. дружбы, горка дружбы - это то же

и т.д..
29 сен 17, 11:43    [20831275]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
при действительно хреновых исходный, я бы смотрел в сторону отдачи этого на сторону гугла (выгружаем, пишем/находим приложение на отправку запроса к геолокации, получаем более менее корректное значение). Мы так делали для небольшого проекта с определением координат, но также паршивым источником.
29 сен 17, 11:49    [20831292]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
Руслан Дамирович,
да очень даже приятного аппетита вам, я же не против

P.S. А бардак, он не в адресах, он в головах. - ну так я и не против, но вы, возможно, и живете в идеальном мире, где все идеально правильно, а мы, убогие, живем в реальном мире, и не мною придумана ни первая, ни вторая база, а вот что-то делать с ними приходится мне

invm,
спасибо, но вот мне предоставили место на сервере mssql, но сказали, что я там не могу пользоваться полнотекстовым поиском (да и пока я еще не знаю что это такое и чем его едят), а можно это как-то с использованием like, или это слишком извратно? простые запросы с оператором like я делала, но тут, видимо, слишком туплю, т.к. ничего не получается
на excell-е тоже пока ничего хорошего не вышло

я понимаю, что много прошу и к сожалению я не программист, а так нахваталась верхушек от программистов, которые раньше у нас работали, а также прекрасно понимаю, что написать такую умную программу, которая из бардак на 100% вытащит все данные - это вообще невозможно, но все таки хочу хотя бы попытаться облегчит себе хоть на сколько-то дальнейший труд по разбору чьей-то каши....

Спасибо за помощь! )
29 сен 17, 11:59    [20831333]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
в таблицах: (addr_b) 1704 и (addr_a) 4287 записей, но addr_a должны все увязаться с addr_b и даже в addr_b еще могут остаться несвязанные записи

я просто подумала, что из таблицы addr_a удалить все "пр.", "ул.", "пер." и т.д., во всех их вариациях, также, после названия улицы удалить запятую, если есть и "д", если есть, если после номера дома есть еще цифра, учитывая возможность наличия дроби, то это считать номером подъезда
в таблице addr_b микрорайоны и кварталы обозначаются одинаково "МК.1", "КВ.100", плюс название улиц там написано одинаково, поэтому ее можно считать за образцовую
а вот с ошибками написания, наверное, уже ничего не поделать - Строителей - Стрителей - ну тут только вручную, но это, наверное, уже не так много
29 сен 17, 12:13    [20831365]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Юлия97,

автор
я просто подумала, что из таблицы addr_a удалить все "пр.", "ул.", "пер."

у вас не бывает улиц и переулков с одинаковым названием, однако
29 сен 17, 12:16    [20831368]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
TaPaK,

упс, об этом я и не подумала ))) спасибо!

ну тогда, наверное, привести их все в одинаковый вид
29 сен 17, 12:19    [20831374]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Юлия97
TaPaK,

упс, об этом я и не подумала ))) спасибо!

ну тогда, наверное, привести их все в одинаковый вид

пусть все будут "ул. Ленина"
29 сен 17, 12:20    [20831379]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
TaPaK,

ну я же не это имела ввиду, а все разнообразные записи: "ул.Пушкина", "Ул Пушкина", "ул. Пушкина" преобразовать в единый вид "ул.Пушкина"
29 сен 17, 12:24    [20831387]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
rnk
Member

Откуда:
Сообщений: 126
Юлия97,
Надеюсь, не сочтут за рекламу, но есть такая замечательная контора, работающая с адресами и распознающая их в код КЛАДР/ФИАС -iqsystems.ru. На их сервере поднят вебсервис, который по запросу выдаёт коды КЛАДР/ФИАС, почтовый индекс и нормализованный адрес как вразбивку по полям (регион, район, город, населённый пункт, улица, дом, корпус и т.д.), так и одной строкой. Вебсервис обладает стабильным API и легко интегрируется в любые информационные системы. Есть пробный бесплатный доступ.

В вашей задаче, как я понимаю, город и/или населённый пункт априори известны, достаточно распознать и сличить КЛАДР улиц и номера домов.
29 сен 17, 12:25    [20831391]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3462
TaPaK
при действительно хреновых исходный, я бы смотрел в сторону отдачи этого на сторону гугла (выгружаем, пишем/находим приложение на отправку запроса к геолокации, получаем более менее корректное значение). Мы так делали для небольшого проекта с определением координат, но также паршивым источником.


они действительно хреновые, судя даже по тому куску, который привела ТС.
Или отдать, или делать в несколько этапов, включая руками перемолотить
29 сен 17, 12:28    [20831401]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
rnk
Юлия97,
Надеюсь, не сочтут за рекламу...


Спасибо, но, во-первых, я не из России, во-вторых, если сама не смогу все прикрутить, то оплачивать могу, если только из своей зарплаты )))
29 сен 17, 12:29    [20831403]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
iii2
Member

Откуда:
Сообщений: 202
Юлия97
Руслан Дамирович,

ну я смотрю вы большой специалист по курительным смесям )))

Собственно, оно конечно же понятно, что в таком дурдоме сложно что-то предложить... Но, будем надеяться, что может у кого-то есть хоть какое-то решение, которое хоть что-то из этого сможет вытянуть )

Да нет, он прав на самом деле. По всем пунктам.
И там от объёма всё зависит.
Если вам, например, 10 тысяч адресов разобрать - то это одно, а если 10 миллионов - совсем другое.
Режьте записи по запятым, или, например, пробелам, или и тем и другим и третьим.
Составляйте частотные словари для полученных кусков. Причем, желательно, с учетом предыдущих и последующих слов. Ищите куски строки в словарях с учетом возможного искажения искомого значения (см. дистанцию редактирования), но учтите, что, к примеру, Лыткин и Лызин - это разные люди, а Островский - он вообще хрен пойми какой, и хорошо если все эти Островские в разных районах.
Ну и т.д.
29 сен 17, 12:31    [20831408]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Юлия97,

кстати, советую держать бекап объекта. И логировать скрипты закомиченных изменений, ибо откатов будет скорее всего масса, а так весёлейшее развлечение, если не надо ничего другого делать и за это платят :)
29 сен 17, 12:38    [20831431]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
aleks222
Guest
Юлия97
rnk
Юлия97,
Надеюсь, не сочтут за рекламу...


Спасибо, но, во-первых, я не из России, во-вторых, если сама не смогу все прикрутить, то оплачивать могу, если только из своей зарплаты )))


Щас фсе за деньги, только работать можно бесплатно.

ЗЫ.
На самом деле ничего сложного в задаче тредстартерши нету.

1. Следует осознать, что 100% раcпознание адреса невозможно.
2. Для всего остального есть CHARINDEX элементов РЕГУЛЯРНОГО адреса в строке (90% отсеивается)
3. проверка порядка следования элементов.
4. и ограничителей до и после каждого найденного элемента.
29 сен 17, 12:41    [20831439]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
iii2,

да мне хотя бы совместить, что более менее совмещается ))) а остальное - придется ручками %)

можно, я тогда задам дурацкий вопрос:
как мне написать запрос, ну что-то в духе: select * from dbo.addr_a a join dbo.addr_b b on a.addr like '%''b.[street]''%'
простите, но я же не программист, потому такая вот фигня получается )))
или мне нужно сначала в отдельное поле вырезать улицу из a.addr? а только потом совмещать
29 сен 17, 12:41    [20831440]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
defragmentator
Member

Откуда:
Сообщений: 20504
Юлия97,

посмотри как люди делают.
Погугли, что такое кладр :)
29 сен 17, 12:50    [20831464]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
rnk
Member

Откуда:
Сообщений: 126
Юлия97
Спасибо, но, во-первых, я не из России
А в чём проблема? У них есть хорошие базы по Украине и Казахстану, неплохая база по Беларуси. Проблемы с проплатой в другую страну? Думаю, это тоже более или менее решаемый вопрос, иначе международная торговля просто остановилась бы. Если вообще нету денег, какое-то количество адресов (приличное, не менее 100 в день), насколько я знаю, можно чистить бесплатно.

Юлия97
во-вторых, если сама не смогу все прикрутить, то оплачивать могу, если только из своей зарплаты )))
Там особо прикручивать нечего, вебсервис есть вебсервис, следуйте инструкции, и будет вам щястье.
29 сен 17, 12:53    [20831476]     Ответить | Цитировать Сообщить модератору
 Re: базы адресов, как связать бардак в данных  [new]
Юлия97
Guest
rnk,

уже попробовала, видимо наши городские власти или кто там еще, не особенно стараются, нет нас там (( по крайней мере не нашла пару центральных улиц
да это и не важно, мне, как минимум, даже близко непонятно, как я могла бы это использовать в силу своих знаний
так что спасибо, но пока пробую другие советы
29 сен 17, 13:07    [20831521]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить