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

Откуда: Москва
Сообщений: 27
Пришлось столкнуться с поиском адреса по базе. Реализации, которые я видел не удовлетворяют по части пользовательского интерфейса - поисковая строка должна быть одна, т.к. при поиске заранее не всегда можно сказать на каком уровне расположен объект (город, населенный пункт, регион), например.
Выношу на ваш суд (а кто-то может быть и воспользуется) мое решение:
1. загрузка таблиц кладра в БД (MS SQL)
2. преобразование таблиц кладра в свою структуру
3. поиск адреса

по первому этапу:
тонкости загрузки примененные у меня описывать не буду (я загружаю из своей информационной системы, можно импортировать штатными средствами)
приведу только структуру таблиц:
+ Стена кода
CREATE TABLE [dbo].[tmp_kladr_altnames](
	[oldcode] [char](19) NULL,
	[newcode] [char](19) NULL,
	[level] [char](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tmp_kladr_doma](
	[Name] [char](40) NULL,
	[Korp] [char](10) NULL,
	[Socr] [char](10) NULL,
	[code] [char](19) NULL,
	[Index] [char](6) NULL,
	[gninmb] [char](4) NULL,
	[uno] [char](4) NULL,
	[ocatd] [char](11) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tmp_kladr_flat](
	[name] [char](20) NULL,
	[code] [char](23) NULL,
	[index] [char](6) NULL,
	[gninmb] [char](4) NULL,
	[uno] [char](4) NULL,
	[np] [char](4) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tmp_kladr_kladr](
	[name] [char](40) NULL,
	[socr] [char](10) NULL,
	[code] [char](13) NULL,
	[index] [char](6) NULL,
	[gninmb] [char](4) NULL,
	[uno] [char](4) NULL,
	[ocatd] [char](11) NULL,
	[status] [char](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tmp_kladr_socrbase](
	[level] [char](5) NULL,
	[scname] [char](10) NULL,
	[socrname] [char](29) NULL,
	[kod_t_st] [char](3) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tmp_kladr_street](
	[name] [char](40) NULL,
	[socr] [char](10) NULL,
	[code] [char](17) NULL,
	[index] [char](6) NULL,
	[gninmb] [char](4) NULL,
	[uno] [char](4) NULL,
	[ocatd] [char](11) NULL
) ON [PRIMARY]

GO

Их структура совпадает со структурой таблиц кладра, на этапе импорта никаких преобразований не производится. Заполнение таблиц оставляю для самостоятельного решения.

Второй этап:
основных таблиц две:
четыре - две хранят данные кладра и две хранят кэш поиска
CREATE TABLE [dbo].[KLADR_ABBREV](-- таблица с сокращениями названий объектов
	[ABBREV_LEVEL] [smallint] NOT NULL,-- уровень сокращения (регион, район, город...)
	[ABBREV_ID] [smallint] NOT NULL,-- идентификатор сокращения
	[ABBREV_POS] [smallint] NOT NULL,-- позиция сокращения в названии (начало, конец, не требуется)
	[ABBREV_NAME] [varchar](40) NOT NULL,-- полное название (не используется)
	[ABBREV_SHNAME] [varchar](10) NOT NULL,-- сокращенное название
 CONSTRAINT [PK_KLADR_ABBREV] PRIMARY KEY CLUSTERED 
(
	[ABBREV_LEVEL] ASC,
	[ABBREV_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[KLADR_FULL](-- основная таблица хранения объектов
	[Region] [char](2) NULL, -- регион
	[Level] [int] NULL,-- уровень объекта
	[name] [char](40) NULL,-- название объекта
	[socr] [char](10) NULL,-- сокращение
	[code] [char](23) NULL,-- код кладр объекта (обращаю внимание на длину!!! на 4 символа больше описанного в спецификации)
	[Parent] [char](23) NULL, -- код КЛАДР родительского объекта
	[index] [char](6) NULL,-- почтовый индекс объекта
	[gninmb] [char](4) NULL,-- х.з.
	[uno] [char](4) NULL,--х.з.
	[ocatd] [char](11) NULL,--х.з.
	[status] [char](1) NULL--х.з.
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[KLADR_CASH_Header](-- заголовки результатов поиска
	[id] [int] IDENTITY(1,1) NOT NULL,-- идентификатор заголовка
	[query] [varchar](50) NOT NULL,-- поисковая строка
	[ok] [tinyint] NOT NULL,-- флаг завершения поиска
	[Start] [datetime] NULL,-- дата и время начала поиска
	[end] [datetime] NULL,-- дата и время завершения поиска
	[Adv] [tinyint] NOT NULL,-- флаг расширенного поиска
 CONSTRAINT [PK_KLADR_CASH_Header] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[KLADR_CASH_Result](-- табличная часть результатов поиска
	[Header] [int] NOT NULL,-- ссылка на заголовок
	[index] [char](6) NOT NULL,-- почтовый индекс
	[code] [char](19) NOT NULL,-- код кладр (здесь уже длина соответствует спецификации - 19 символов)
	[Result] [varchar](max) NOT NULL,-- строка результата поиска в служебном формате
	[Result_User] [varchar](max) NOT NULL-- строка результатов поиска в пользовательском формате
) ON [PRIMARY]

GO

CREATE CLUSTERED INDEX [Header] ON [dbo].[KLADR_CASH_Result] 
(
	[Header] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [code] ON [dbo].[KLADR_FULL] 
(
	[code] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [level] ON [dbo].[KLADR_FULL] 
(
	[Level] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [LevelName] ON [dbo].[KLADR_FULL] 
(
	[Level] ASC,
	[name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Name] ON [dbo].[KLADR_FULL] 
(
	[name] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [Parent] ON [dbo].[KLADR_FULL] 
(
	[Parent] ASC
)
INCLUDE ( [code],
[index]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

ALTER TABLE [dbo].[KLADR_ABBREV]  WITH CHECK ADD  CONSTRAINT [CK_KLADR_ABBREV LEVEL] CHECK  (([ABBREV_LEVEL]>=(1) AND [ABBREV_LEVEL]<=(6)))
GO

ALTER TABLE [dbo].[KLADR_ABBREV] CHECK CONSTRAINT [CK_KLADR_ABBREV LEVEL]
GO

ALTER TABLE [dbo].[KLADR_ABBREV]  WITH CHECK ADD  CONSTRAINT [CK_KLADR_ABBREV POS] CHECK  (([ABBREV_POS]>=(0) AND [ABBREV_POS]<=(2)))
GO

ALTER TABLE [dbo].[KLADR_ABBREV] CHECK CONSTRAINT [CK_KLADR_ABBREV POS]
GO

ALTER TABLE [dbo].[KLADR_ABBREV] ADD  CONSTRAINT [DF__KLADR_ABB__ABBREV_POS default]  DEFAULT ((0)) FOR [ABBREV_POS]
GO

ALTER TABLE [dbo].[KLADR_CASH_Header] ADD  CONSTRAINT [DF_KLADR_CASH_Header_Adv]  DEFAULT ((1)) FOR [Adv]
GO

ALTER TABLE [dbo].[KLADR_CASH_Result]  WITH CHECK ADD  CONSTRAINT [FK_KLADR_CASH_Result_KLADR_CASH_Header] FOREIGN KEY([Header])
REFERENCES [dbo].[KLADR_CASH_Header] ([id])
GO

ALTER TABLE [dbo].[KLADR_CASH_Result] CHECK CONSTRAINT [FK_KLADR_CASH_Result_KLADR_CASH_Header]
GO

несколько слов по структуре:
длина кода кладр в основной таблице увеличена на 4 символа для разрешения проблем поиска адреса на 6 уровне. Если дома указаны как диапазон (например, 199В,199Г,200Б,Ч(202-204),Н(201-237)) то вывести это пользователю - будет издевательство.
поэтому такие диапазоны разворачиваются до каждого дома. Для идентификации таких разверток используются последние 4 символа (в остальных случаях они заполнены нулями)

в таблице строк результата было бы полезно заменить varchar(max) на ограниченную длину (я сначала собирался это сделать, а потом скорость работы с кэшем полностью удовлетворяла пользователей, да и моя лень никуда не делась :))

индексы привел все, какие есть - возможно тоже есть и лишние.

Теперь по процедуре импорта несколько слов:
в моей реализации требуется поиск только актуального адреса, поэтому все объекты, у которых признак актуальности не равен '00' я пропускаю.
В нашей системе требуется уточнение адреса только до 5-го уровня (на самом деле 6 уровень заполнен не самым лучшим образом, поэтому на него опираться все равно нельзя), почтовый индекс (для отправки через почтовыю службу), код КЛАДР для маршрутизации службы доставки.
Исходя из этого я принял решение модификации основной таблицы относительно таблиц КЛАДР:
1. если у объекта пятого уровня есть почтовый индекс, то шестой уровень игнорируется (не импортируется)
2. если у объекта пятого уровня индекса нет, но на шестом уровне вариант индекса только один (очень часто там встречается диапазон 1-999), то объект пятого уровня получает этот индекс и шестой уровень не импортируется
3. если на пятом уровне индекса нет, а на шестом используются различные перечни и интервалы, то все эти перечни и интервалы я разворачиваю в отдельные объекты и вставляю их в основную таблицу.

ну и в самом конце импорта, естественно очищается таблица с кэшем результатов поиска. В конце для того, чтобы пользователи могли использовать кэш во время импорта, прямой поиск в это время работать не будет.

CREATE PROCEDURE [dbo].[kladr_import] 
AS
BEGIN
declare @Name char(40), 
@Name1 char(40),
@socr char(10),
@Code char(23), 
@code1 char(23),
@Index char(6),
@gninmb char(4),
@uno char(4),
@ocatd char(11)

  DELETE FROM [dbo].[KLADR_FULL]
  INSERT INTO [dbo].[KLADR_FULL]
           ([Region]
           ,[Level]
           ,[name]
           ,[socr]
           ,[code]
           ,[Parent]
           ,[index]
           ,[gninmb]
           ,[uno]
           ,[ocatd]
           ,[status])
select 
Region,
[Level],
name,
socr,
code,
case when [Level]=4 then SUBSTRING(code, 1,8)+ '000000000000000'--15
     when [level]=3 then SUBSTRING(code, 1, 5)+'000000000000000000'--18
     when [level]=2 then SUBSTRING(code, 1, 2)+'000000000000000000000'--21
else '00000000000000000000000' --23
end "Parent",
[index],
gninmb,
uno,
ocatd,
[status]
from
(
SELECT 
SUBSTRING(code, 1, 2) "Region",
case when SUBSTRING(code, 3, 9)=    '000000000' then 1 else
  case when SUBSTRING(code, 6, 6)=  '000000'    then 2 else
    case when SUBSTRING(code, 9, 3)='000'       then 3 
      else 4 
    end
  end
end "Level",
       [name]
      ,[socr]
      ,SUBSTRING([code], 1, 11)+'000000000000'"code"
      ,[index]
      ,[gninmb]
      ,[uno]
      ,[ocatd]
      ,[status]
  FROM [dbo].[tmp_kladr_kladr]
where SUBSTRING(code, 12,2)='00'
and [name]<>''
)q

  INSERT INTO [dbo].[KLADR_FULL]
           ([Region]
           ,[Level]
           ,[name]
           ,[socr]
           ,[code]
           ,[Parent]
           ,[index]
           ,[gninmb]
           ,[uno]
           ,[ocatd]
           ,[status])
SELECT 
SUBSTRING(code, 1, 2) "Region",
5 "Level",
[name]
      ,[socr]
      ,SUBSTRING([code],1,15) + '00000000' "code"
      ,SUBSTRING(code, 1, 11)+  '000000000000' "Parent"
      ,[index]
      ,[gninmb]
      ,[uno]
      ,[ocatd]
      ,0 "Status"
  FROM [dbo].[tmp_kladr_street]
  where [name]<>''
  and SUBSTRING(code, 16, 2)='00'
  
declare cur cursor for
select 
Parent, 
[Index]
from
(select 
Parent, 
[Index],
COUNT(*) "q"
from
(
SELECT [Name]
      ,[Korp]
      ,[Socr]
      ,[code]
      ,SUBSTRING([code], 1, 15)+'00000000' "Parent"
      ,[Index]
      ,[gninmb]
      ,[uno]
      ,[ocatd]
  FROM [dbo].[tmp_kladr_doma] d
  where (SELECT [index] FROM [dbo].[KLADR_FULL] where [code]=substring(d.[code],1,15)+'00000000')=''
  )q
  group by Parent, [Index]
  )w
  where q=1
Order by Parent
open cur
fetch next from cur into @Code, @Index
while @@FETCH_STATUS=0
begin
  UPDATE [dbo].[KLADR_FULL]
      SET [index] = @Index
    WHERE [code]=@Code
  fetch next from cur into @Code, @Index
end
close cur 
deallocate cur

-----------------------
set nocount on
declare @t table
(
Name char(40),
Socr char(10),
Code char(23),
[Index] char(6),
gninmb char(4),
uno char(4),
ocatd char(11)
)

declare @t1 table
(
Name char(40),
Socr char(10),
Code char(23),
[Index] char(6),
gninmb char(4),
uno char(4),
ocatd char(11)
)

declare cur cursor for
SELECT --top 1000 
       [Name]
      ,[Socr]
      ,[code]
      ,[Index]
      ,[gninmb]
      ,[uno]
      ,[ocatd]
  FROM [dbo].[tmp_kladr_doma] d
  where [Name]<>''
  and (SELECT [index] FROM [dbo].[KLADR_FULL] where [code]=substring(d.[code],1,15)+'00000000')=''
  
open cur
fetch next from cur into @Name, @socr, @code, @index, @gninmb, @uno, @ocatd
while @@FETCH_STATUS=0
begin
  while CHARINDEX(',',@Name)<>0
  begin
    insert into @t values (SUBSTRING(@name,1,charindex(',',@Name,1)-1)
    ,@socr, @code, @index, @gninmb, @uno, @ocatd
    )
    set @Name=SUBSTRING(@Name, charindex(',',@name,1)+1, LEN(@name))
  end
  insert into @t values (@Name
    ,@socr, @code, @index, @gninmb, @uno, @ocatd
    )
  fetch next from cur into @Name, @socr, @code, @index, @gninmb, @uno, @ocatd
end

close cur
deallocate cur

declare @start int, @end int, @step int

declare cur cursor for
select * from @t
order by Code

open cur

declare @i int

set @code1=''
fetch next from cur into @Name, @socr, @code, @index, @gninmb, @uno, @ocatd

while @@FETCH_STATUS=0
begin
  if substring(@code,1,19)<>substring(@code1,1,19)
  begin
    set @i=1
    set @code=substring(@code,1,19)+'0001'
    set @code1=@code
  end else begin
    set @i=@i+1
    set @code=SUBSTRING(@code, 1, 19)+case when @i<10 then '000'+ltrim(rtrim(CONVERT(char,@i))) else 
                                        case when @i<100 then '00'+ltrim(rtrim(CONVERT(char,@i))) else 
                                          case when @i<1000 then '0'+ltrim(rtrim(CONVERT(char,@i))) else 
                                            ltrim(rtrim(CONVERT(char,@i)))
                                          end
                                        end
                                      end
  end
  if CHARINDEX('Н',@Name,1)<>0
  begin
    -- диапазон нечетных домов
   if CHARINDEX('Н(',@Name,1)=1
   begin
     set @Name1=SUBSTRING(@name, 3, charindex(')', @name,1)-3)
     begin try
       set @start = CONVERT(int, substring(@Name1, 1, charindex('-', @name1)-1))
       set @end = CONVERT(int,substring(@Name1, charindex('-', @name1)+1, len(@Name1)))
       set @step=2
       --print convert(char, @start)+convert(char, @end)+ @Name
     end try
     begin catch
       if @Name='Н'
       begin
         set @start = 1
         set @end = 999
         set @step=2
       end else begin
         set @start=1
         set @end=0
         print @Name
       end
     end catch
     while @start<=@end
     begin
       insert into @t1 values(CONVERT(char, @Start), @socr, @code, @index, @gninmb, @uno, @ocatd)
       set @start=@start+@step
       set @i=@i+1
       set @code=SUBSTRING(@code, 1, 19)+case when @i<10 then '000'+ltrim(rtrim(CONVERT(char,@i))) else 
                                           case when @i<100 then '00'+ltrim(rtrim(CONVERT(char,@i))) else 
                                             case when @i<1000 then '0'+ltrim(rtrim(CONVERT(char,@i))) else 
                                               ltrim(rtrim(CONVERT(char,@i)))
                                             end
                                           end
                                         end
     end
   end else
     print @name
  end else begin
    if CHARINDEX('Ч',@Name,1)<>0
    begin
     -- диапазон четных домов
     if CHARINDEX('Ч(',@Name,1)=1
     begin
       set @Name1=SUBSTRING(@name, 3, charindex(')', @name,1)-3)
       begin try
         set @start = CONVERT(int, substring(@Name1, 1, charindex('-', @name1)-1))
         set @end = CONVERT(int,substring(@Name1, charindex('-', @name1)+1, len(@Name1)))
         set @step=2
         --print convert(char, @start)+convert(char, @end)+ @Name
       end try
       begin catch
         if @Name='Ч'
         begin
           set @start = 2
           set @end = 998
           set @step=2
         end else begin
           set @start=1
           set @end=0
           print @Name
         end
       end catch
       while @start<=@end
       begin
         insert into @t1 values(CONVERT(char, @Start), @socr, @code, @index, @gninmb, @uno, @ocatd)
         set @start=@start+@step
         set @i=@i+1
         set @code=SUBSTRING(@code, 1, 19)+case when @i<10 then '000'+ltrim(rtrim(CONVERT(char,@i))) else 
                                             case when @i<100 then '00'+ltrim(rtrim(CONVERT(char,@i))) else 
                                               case when @i<1000 then '0'+ltrim(rtrim(CONVERT(char,@i))) else 
                                                 ltrim(rtrim(CONVERT(char,@i)))
                                               end
                                             end
                                           end
       end
     end else
       print @name
    end else begin
      if CHARINDEX('-',@Name,1)<>0
      begin
        -- диапазон
        begin try
          set @start = CONVERT(int, substring(@Name, 1, charindex('-', @name)-1))
          set @end = CONVERT(int,substring(@Name, charindex('-', @name)+1, len(@Name)))
          set @step=1
        end try
        begin catch
          set @start=1
          set @end=0
          print @Name
        end catch
        while @start<=@end
        begin
          insert into @t1 values(CONVERT(char, @Start), @socr, @code, @index, @gninmb, @uno, @ocatd)
          set @start=@start+@step
          set @i=@i+1
          set @code=SUBSTRING(@code, 1, 19)+case when @i<10 then '000'+ltrim(rtrim(CONVERT(char,@i))) else 
                                              case when @i<100 then '00'+ltrim(rtrim(CONVERT(char,@i))) else 
                                                case when @i<1000 then '0'+ltrim(rtrim(CONVERT(char,@i))) else 
                                                  ltrim(rtrim(CONVERT(char,@i)))
                                                end
                                              end
                                            end
        end
      end else begin
        insert into @t1 values(@Name, @socr, @code, @index, @gninmb, @uno, @ocatd)
      end
    end
  end
  fetch next from cur into @Name, @socr, @code, @index, @gninmb, @uno, @ocatd
end

close cur
deallocate cur

set nocount off
INSERT INTO [dbo].[KLADR_FULL]
           ([Region]
           ,[Level]
           ,[name]
           ,[socr]
           ,[code]
           ,[Parent]
           ,[index]
           ,[gninmb]
           ,[uno]
           ,[ocatd]
           ,[status])
select 
SUBSTRING(code,1,2)"Region",
6 "Level",
Name,
Socr,
Code,
SUBSTRING(code, 1, 15)+'00000000'"Parent",
[index],
gninmb,
uno,
ocatd,
0
from @t1
DELETE FROM [dbo].[KLADR_CASH_Result]
DELETE FROM [dbo].[KLADR_CASH_Header]
END

GO

далее по третьему этапу.
сразу были написаны функции преобразования кода кладр в строку адреса:
CREATE FUNCTION [dbo].[KLADR_GetFullName]
(
@code char(23)
)
RETURNS varchar(max)
AS
BEGIN
declare @result varchar(max),--ССРРРГГГПППУУУУДДДДдддд
@RegionCode char(23),
@RaionCode char(23),
@CityCode char(23),
@PunktCode char(23),
@StreetCode char(23),
@HouseCode char(23)

set @RegionCode=SUBSTRING(@code, 1, 2) +'000000000000000000000'--21
set @RaionCode= SUBSTRING(@code, 1, 5) +'000000000000000000'--18
set @CityCode=  SUBSTRING(@code, 1, 8) +'000000000000000 '--15
set @PunktCode= SUBSTRING(@code, 1, 11)+'000000000000'--12
set @StreetCode=SUBSTRING(@code, 1, 15)+'00000000'--8
set @HouseCode= SUBSTRING(@code, 1, 23)

if SUBSTRING(@code,1,2)='00'
begin
  set @result='Ошибка'
end else begin
              set @result= case when substring(@RegionCode, 1, 2)='00'        then '' else isnull(     (select  '1 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@RegionCode)t1 left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@RaionCode,  3, 3)='000'       then '' else isnull(', '+(select  '2 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@RaionCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@CityCode,   6, 3)='000'       then '' else isnull(', '+(select  '3 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@CityCode)t1   left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@PunktCode,  9, 3)='000'       then '' else isnull(', '+(select  '4 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@PunktCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@StreetCode, 12, 4)='0000'     then '' else isnull(', '+(select  '5 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@StreetCode)t1 left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@HouseCode,  16, 8)='00000000' then '' else isnull(', '+(select  '6 - '+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] (nolock) where code=@HouseCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV] (nolock))t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
              --set @result='1 - '+@RegionCode+' 2 - '+@RaionCode+' 3 - '+@CityCode+' 4 - '+@PunktCode+' 5 - '+@StreetCode+' 6 - '+@HouseCode
end
	RETURN @result

END

GO

CREATE FUNCTION [dbo].[KLADR_GetFullName_User]
(
@code char(23)
)
RETURNS varchar(max)
AS
BEGIN
declare @result varchar(max),--ССРРРГГГПППУУУУДДДДдддд
@RegionCode char(23),
@RaionCode char(23),
@CityCode char(23),
@PunktCode char(23),
@StreetCode char(23),
@HouseCode char(23)

set @RegionCode=SUBSTRING(@code, 1, 2) +'000000000000000000000'--21
set @RaionCode= SUBSTRING(@code, 1, 5) +'000000000000000000'--18
set @CityCode=  SUBSTRING(@code, 1, 8) +'000000000000000 '--15
set @PunktCode= SUBSTRING(@code, 1, 11)+'000000000000'--12
set @StreetCode=SUBSTRING(@code, 1, 15)+'00000000'--8
set @HouseCode= SUBSTRING(@code, 1, 23)

if SUBSTRING(@code,1,2)='00'
begin
  set @result='Ошибка'
end else begin
              set @result= case when substring(@RegionCode, 1, 2)='00'        then '' else isnull(     (select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@RegionCode)t1 left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@RaionCode,  3, 3)='000'       then '' else isnull(', '+(select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@RaionCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@CityCode,   6, 3)='000'       then '' else isnull(', '+(select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@CityCode)t1   left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@PunktCode,  9, 3)='000'       then '' else isnull(', '+(select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@PunktCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@StreetCode, 12, 4)='0000'     then '' else isnull(', '+(select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@StreetCode)t1 left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
                          +case when substring(@HouseCode,  16, 8)='00000000' then '' else isnull(', '+(select  ''+case when t2.[ABBREV_POS]=0 then LTRIM(rtrim(t1.[socr]))+' '+LTRIM(rtrim(t1.[name]))when t2.[ABBREV_POS]=1 then LTRIM(rtrim(t1.[name]))+' '+LTRIM(rtrim(t1.[socr])) else LTRIM(rtrim(t1.[name])) end from(SELECT [Level], [name], [socr] FROM [dbo].[KLADR_FULL] where code=@HouseCode)t1  left join (SELECT [ABBREV_LEVEL], [ABBREV_POS], [ABBREV_SHNAME] FROM [dbo].[KLADR_ABBREV])t2 on t1.[Level]=t2.ABBREV_LEVEL and t1.socr=t2.ABBREV_SHNAME),'') end
end
	RETURN @result

END



GO


ну и наконец непосредственно процедура поиска. При поиске используется динамический sql!!! в части безопасности есть некоторый изъян!!! Результаты поиска выдаются только из таблицы кэша! (если в кэше данных нет, то производится поиск с кэшированием и результаты выдаются все равно из кэша). Предусмотрено два режима поиска - обычный и расширенный. В обычном строка поиска должна содержать начала названий объектов адреса в любой последовательности. а при расширенном любые фрагменты любых объектов адреса в любой последовательности:
CREATE PROCEDURE [dbo].[KLADR_GET_ADDRESS_LIST]
@SearchString varchar(50),
@Adv int=0
AS
BEGIN
set nocount on
set nocount on
declare 
  @SearchStr varchar(50),
  @Start datetime, 
  @End datetime,
  @sql varchar(max), 
  @par varchar(max),
  @buf varchar(max)
  declare @ResId table(res int)

declare @params table(parametr varchar(50))

if @SearchString<>''
begin
DELETE FROM [dbo].[KLADR_CASH_Header]
      WHERE id in (
select t1.id from
(
SELECT [id]
  FROM [dbo].[KLADR_CASH_Header]
where [ok]=1
)t1
left join
(
SELECT [Header], COUNT(*) q
  FROM [dbo].[KLADR_CASH_Result]
  group by Header
)t2
on t1.id=t2.Header
where t2.header is null
)

set @SearchStr=@SearchString
if (SELECT COUNT(*) FROM [dbo].[KLADR_CASH_Header] where [ok]=1 and [query]=@SearchString and Adv>=@Adv)=0
begin

set @Start=GETDATE()

INSERT INTO [dbo].[KLADR_CASH_Header]
           ([query]
           ,[ok]
           ,Adv)
output inserted.id into @ResId
     VALUES
           (@SearchString
           ,0
           ,@Adv)

set @SearchString=LTRIM(RTRIM(@SearchString))

set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'e', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'ё', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'и', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'й', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'Е', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'Ё', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'И', '_')
set @SearchString=[dbo].[ReplaseSimbol] ( @SearchString, 'Й', '_')

while CHARINDEX(' ', @SearchString,1)<>0
begin
  set @buf=ltrim(rtrim(SUBSTRING(@SearchString, 1, CHARINDEX(' ', @SearchString,1)-1)))
  if LEN(@buf)>=3
  begin
      insert into @params values(@buf)
  end
  set @SearchString=ltrim(rtrim(SUBSTRING(@Searchstring, CHARINDEX(' ', @SearchString,1)+1, LEN(@SearchString))))
end
if LEN(@SearchString)>=3
begin
    insert into @params values(@SearchString)
end


set @sql='select '+ltrim(rtrim(CONVERT(int, (select top 1 res from @ResId)))) +', 
--top 30 
* from
(
select distinct
isnull(t2.[index], t1.[index])"index",
substring(isnull(t2.code, t1.code),1,19) "code",
[dbo].[KLADR_GetFullName] (isnull(t2.code, t1.code))"FullName",
[dbo].[KLADR_GetFullName_User] (isnull(t2.code, t1.code))"FullName_User"
from
(
SELECT
[index], code
  FROM [dbo].[KLADR_FULL]
where ('
declare cur cursor for
select * from @params
open cur
fetch next from cur into @par
while @@FETCH_STATUS=0
begin
  if @Adv=1
    set @sql=@sql+'[name] like ''%'+@par+'%'''
  else
    set @sql=@sql+'[name] like '''+@par+'%'''
  fetch next from cur into @par
  if @@FETCH_STATUS=0
    set @sql=@sql+' or '
end
close cur
deallocate cur
set @sql=@sql+')
and level<=5
)t1
left join
(
SELECT [code]
      ,[Parent]
      ,[index]
  FROM [dbo].[KLADR_FULL]
)t2
on t1.[index]='''' and t1.code=t2.[Parent]
)q'
if (select COUNT(*) from @params)<>0
  set @sql=@sql+' where '

declare cur cursor for
  select * from @params
open cur
fetch next from cur into @par
while @@FETCH_STATUS=0
begin
  set @sql=@sql+'FullName like ''%'+@par+'%'''
  fetch next from cur into @par
  if @@FETCH_STATUS=0
    set @sql=@sql+' and '
end
close cur
deallocate cur
set nocount off
set @sql=@sql+' order by FullName'
print @sql
INSERT INTO [dbo].[KLADR_CASH_Result]
           ([Header]
           ,[index]
           ,[code]
           ,[Result]
           ,[Result_User])
exec(@sql)
set @End=GETDATE()

UPDATE [dbo].[KLADR_CASH_Header]
   SET [ok] = 1
      ,[Start]=@Start
      ,[end]=@End
 WHERE [id]=(select top 1 res from @ResId)

end
end
SELECT distinct [index]
      ,[code]
      ,[Result]
      ,[Result_User]
  FROM [dbo].[KLADR_CASH_Result]
  where [Header] in (SELECT [id] FROM [dbo].[KLADR_CASH_Header] where [ok]=1 and [query]=@SearchStr)
order by [Result_User]
END

GO


Сообщение было отредактировано: 30 сен 11, 14:33
30 сен 11, 14:32    [11362724]     Ответить | Цитировать Сообщить модератору
 Re: КЛАДР  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Девиченский Василий
select top 1 res from @ResId
Top 1 без order by - фии.

Сообщение было отредактировано: 30 сен 11, 14:38
30 сен 11, 14:38    [11362807]     Ответить | Цитировать Сообщить модератору
 Re: КЛАДР  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Стопиццот циклов и курсоров - тоже, скорее всего, лишнее.

Сообщение было отредактировано: 30 сен 11, 14:49
30 сен 11, 14:39    [11362832]     Ответить | Цитировать Сообщить модератору
 Re: КЛАДР  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 27
Гавриленко Сергей Алексеевич,

там может быть только одна строка и никак иначе.
30 сен 11, 15:33    [11363431]     Ответить | Цитировать Сообщить модератору
 Re: КЛАДР  [new]
Девиченский Василий
Member

Откуда: Москва
Сообщений: 27
Гавриленко Сергей Алексеевич,

Вполне допускаю, что процедуру импорта можно и ускорить. Эта процедура отрабатывает примерно за полторы минуты - несравнимо быстрее, чем заполнение временных таблиц. И кроме того вполне вписывается в рамки работы компании (есть три часа "мертвого" времени - когда на территории России везде ночь, с 22-00 до 1-00 по Москве).

Ну и в процессе создания этого механизма приходилось обрабатывать импорт "по шагам"-уровням. Поэтому и циклы-курсоры.

Опять же, критиковать легко. Несколько сложнее покритиковав предложить более хороший вариант.

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

Когда я столкнулся с этой задачей, мне не удалось найти ни одного решения поиска адреса с использованием одной строки поиска, с произвольным вводом названий объектов.

Если кто-то сможет усовершенствовать предложенный механизм - буду только рад.
30 сен 11, 15:42    [11363515]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить