Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Обновление  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 26
Несколько обновлю тему. Причин две:
1. пришел запрос на почту с просьбой выложить текущую версию
2. текущая версия работы с КЛАДР несколько отличается от опубликованной ранее.
начало загрузки осталось прежним:
1. заполняем таблицы tmp_kladr_altnames, tmp_kladr_doma, tmp_kladr_flat, tmp_kladr_kladr, tmp_kladr_socrbase и tmp_kladr_street. Как именно заполнять не рассказываю, предлагаю решить самостоятельно в рамках своего бизнес-процесса
2. готовим таблицу KLADR_ABBREV, в которой указываем как работать с сокращениями адресных объектов на различных уровнях. заполнять ее предварительно.
3. заполнение таблицы KLADR_FULL в несколько этапов, по уровням объектов. Особое внимание хотелось бы обратить на последний, шестой уровень - в исходных таблицах используются т.н. диапазоны домов, которые неудобны для поиска оператором. Поэтому в моей структуре предусмотрено увеличение длины кода до 23 символов (против 19 обычных) и последний шестой уровень разворачивается в мой придуманный седьмой. в котором каждый дом имеет свой уникальный код. Оператору будет выводиться список конкретных домов.
все это было описано ранее, далее начинается новый этап - подготовка полнотекстового индекса. Основная идея:
подготовить таблицу соответствия слов и кодов КЛАДР в декодировании которых встречаются эти слова:
CREATE TABLE [dbo].[KLADR_Words](
[code] [varchar](23) NOT NULL,
[Word] [varchar](40) NOT NULL,
[Level] [int] NULL
) ON [PRIMARY]
далее по таблице KLADR_FULL применяется функция KLADR_GetFullName_User, которая производит декодирование кода в полную строку адреса. И по полученной строке применяется еще одна табличная функция, которая строку адреса разделяет на слова:
CREATE FUNCTION [dbo].[FT_Split](@SearchStr varchar(3000))
RETURNS @tmp_Words TABLE (Word varchar(40))
AS
begin
declare @Word varchar(40)
delete from @tmp_Words
set @SearchStr=replace(@SearchStr, 'ё', 'е')
set @SearchStr=replace(@SearchStr, 'й', 'и')

set @SearchStr=replace(@SearchStr, '-', ' ')
set @SearchStr=replace(@SearchStr, '"', ' ')
set @SearchStr=replace(@SearchStr, '(', ' ')
set @SearchStr=replace(@SearchStr, ')', ' ')
set @SearchStr=replace(@SearchStr, ',', ' ')
set @SearchStr=replace(@SearchStr, '.', ' ')
set @SearchStr=replace(@SearchStr, '/', ' ')
set @SearchStr=replace(@SearchStr, '_', ' ')
set @SearchStr=replace(@SearchStr, 'N', ' ')

set @SearchStr=replace(@SearchStr, '''', ' ')
set @SearchStr=replace(@SearchStr, '\', ' ')
set @SearchStr=replace(@SearchStr, '|', ' ')

set @SearchStr=replace(@SearchStr, '[', ' ')
set @SearchStr=replace(@SearchStr, ']', ' ')
set @SearchStr=replace(@SearchStr, '#', ' ')
set @SearchStr=replace(@SearchStr, ':', ' ')
set @SearchStr=replace(@SearchStr, ';', ' ')
set @SearchStr=replace(@SearchStr, '№', ' ')
set @SearchStr=replace(@SearchStr, '@', ' ')
set @SearchStr=replace(@SearchStr, '?', ' ')
set @SearchStr=replace(@SearchStr, '=', ' ')
set @SearchStr=replace(@SearchStr, '+', ' ')
set @SearchStr=replace(@SearchStr, '!', ' ')
set @SearchStr=replace(@SearchStr, '–', ' ')
set @SearchStr=replace(@SearchStr, '*', ' ')
set @SearchStr=replace(@SearchStr, '$', ' ')
set @SearchStr=replace(@SearchStr, '>', ' ')
set @SearchStr=replace(@SearchStr, '<', ' ')
set @SearchStr=replace(@SearchStr, '»', ' ')
set @SearchStr=replace(@SearchStr, '«', ' ')
set @SearchStr=replace(@SearchStr, '%', ' ')
set @SearchStr=replace(@SearchStr, '`', ' ')
set @SearchStr=replace(@SearchStr, '”', ' ')
set @SearchStr=replace(@SearchStr, '‰', ' ')
set @SearchStr=replace(@SearchStr, '•', ' ')
set @SearchStr=replace(@SearchStr, '¶', ' ')
set @SearchStr=replace(@SearchStr, '{', ' ')
set @SearchStr=replace(@SearchStr, 'ї', ' ')
set @SearchStr=replace(@SearchStr, '}', ' ')
set @SearchStr=replace(@SearchStr, '…', ' ')
set @SearchStr=replace(@SearchStr, '‡', ' ')
set @SearchStr=replace(@SearchStr, 'µ', ' ')
set @SearchStr=replace(@SearchStr, '&', ' ')
set @SearchStr=replace(@SearchStr, '&', ' ')
set @SearchStr=replace(@SearchStr, '~', ' ')
set @SearchStr=replace(@SearchStr, '‘', ' ')
set @SearchStr=replace(@SearchStr, '’', ' ')
set @SearchStr=replace(@SearchStr, '‚', ' ')
set @SearchStr=replace(@SearchStr, '°', ' ')
set @SearchStr=replace(@SearchStr, '€', ' ')

set @SearchStr=replace(@SearchStr, '­­­­­­—', ' ')
set @SearchStr=replace(@SearchStr, 'ґ', ' ')
set @SearchStr=replace(@SearchStr, 'ѓ', ' ')
set @SearchStr=replace(@SearchStr, 'Ђ', ' ')
set @SearchStr=replace(@SearchStr, 'є', ' ')
set @SearchStr=replace(@SearchStr, 'Њ', ' ')
set @SearchStr=replace(@SearchStr, 'Џ', ' ')
set @SearchStr=replace(@SearchStr, '¦', ' ')
set @SearchStr=replace(@SearchStr, '®', ' ')
set @SearchStr=replace(@SearchStr, '•', ' ')
set @SearchStr=replace(@SearchStr, char(0), ' ')
set @SearchStr=replace(@SearchStr, char(9), ' ')
set @SearchStr=replace(@SearchStr, char(10), ' ')
set @SearchStr=replace(@SearchStr, char(13), ' ')
set @SearchStr=replace(@SearchStr, char(30), ' ')
set @SearchStr=replace(@SearchStr, char(31), ' ')
set @SearchStr=replace(@SearchStr, char(94), ' ')
set @SearchStr=replace(@SearchStr, char(127), ' ')
set @SearchStr=replace(@SearchStr, char(147), ' ')
set @SearchStr=replace(@SearchStr, char(151), ' ')
set @SearchStr=replace(@SearchStr, char(160), ' ')
set @SearchStr=replace(@SearchStr, char(163), ' ')
set @SearchStr=replace(@SearchStr, char(169), ' ')
set @SearchStr=replace(@SearchStr, char(172), ' ')
set @SearchStr=replace(@SearchStr, char(173), ' ')
set @SearchStr=replace(@SearchStr, char(177), ' ')
set @SearchStr=replace(@SearchStr, char(178), ' ')
set @SearchStr=replace(@SearchStr, char(189), ' ')

while charindex(' ', @SearchStr)<>0
begin
set @SearchStr=replace(@SearchStr, ' ', ' ')
end

set @SearchStr=ltrim(rtrim(@SearchStr))

while CHARINDEX(' ', @SearchStr, 1)<>0
begin
set @Word=ltrim(rtrim(SUBSTRING(@SearchStr, 1, CHARINDEX(' ', @SearchStr, 1)-1)))
set @Word=upper(SUBSTRING(@Word, 1, 40))
set @SearchStr=ltrim(rtrim(SUBSTRING(@SearchStr, CHARINDEX(' ', @SearchStr, 1)+1, LEN(@SearchStr))))
if (select count(*) from @tmp_Words where Word=@Word)=0
insert into @tmp_Words values(@Word)
end

if @SearchStr<>''
begin
set @SearchStr=Upper(substring(@SearchStr, 1, 40))
if (select count(*) from @tmp_Words where Word=@SearchStr)=0
insert into @tmp_Words values(@SearchStr)
end
RETURN
end

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

На этом процедуру импорта можно считать завершенной.
Далее происходит поиск по этой таблице со словами:
строку запроса разделяем на слова вышеприведенной функцией во временную таблицу.
Если слово во временной таблице есть в таблице со словами (точное соответствие), тогда оставляем его без изменений, иначе добавляем к нему % в начале (считаем, что начало слова оператор вводит точно). В принципе, можно немного модифицировать алгоритм для того, чтобы можно было не требовать от оператора начала слова (аналог первого процента), но применительно к моей ситуации оказалось, что таблица со словами разрастается очень сильно, время на загрузку увеличивается (текущая версия работает примерно полтора часа, с полным разделением около трех). Ну и начало слов операторы вводят как правило верно.

Основной набор скриптов во вложении, индексы не включаю, они вполне очевидны.

К сообщению приложен файл (КЛАДР.sql - 35Kb) cкачать
5 ноя 13, 14:26    [15078832]     Ответить | Цитировать Сообщить модератору
 Re: Обновление  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Девиченский Василий
индексы не включаю, они вполне очевидны.

Не понимаю. Это чтобы сэкономить десяток-другой строк кода, или просто чтобы развлечь потенциального пользователя скриптов решением задачки "найди недостающие индексы"?
5 ноя 13, 15:10    [15079234]     Ответить | Цитировать Сообщить модератору
 Re: Обновление  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 26
Гость333,

не, все проще:
1. генерить скрипты по боевому серверу не хочется, т.к. могу раскрыть структуру наших рабочих баз. КЛАДР общий, выложить считаю возможным. А проводить анализ влом.
2. все недостающие индексы клепаются на счет раз при помощи вьюшек sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details. Человеку, который с ними не знаком наверное не стоит заниматься прикручиванием подобных механизмов поиска.
5 июн 14, 09:01    [16123830]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить