set nocount on
   declare @id bigint, @string varchar(4000)
   declare cur cursor local for
   SELECT w.[wayid],string from [OSM_LOAD].[dbo]. [ways]as w
   inner join (select  [wayid]from [OSM_LOAD].[dbo]. [waynodes]group by  [wayid]having count(*) >=2) as WN on wn.[wayid]=w.
   [wayid]inner join (select  [wayid]from [OSM_LOAD].[dbo]. [waytags]where [tagname]='addr:housenumber' group by [wayid]) as WT on wt.[wayid]=w.
    
   [wayid]open cur
   declare @gm geometry, @g geography

   while 1=1 begin
    fetch next from cur into @id,@string
    if @@FETCH_STATUS!=0 break
    --print @id
     
    if (select count(*) from [OSM_LOAD].[dbo]. [waynodes]where [wayid]=@id)>=3  
       begin  
     
          --print @string

          set @gm=geometry::STGeomFromText(@string,4326).MakeValid ()
          set @gm=@gm.STUnion(@gm.STStartPoint())
          if @gm.STIsValid() =1  
          begin
             --print  cast(@gm as varchar(max))

             set @g=geography::STGeomFromText(@gm.ToString(),4326)
             --print  cast(@g as varchar(max))
             update [OSM_LOAD].dbo.ways
             set geog4326=@g where  [wayid]= @id
          end
          else select gm.ToString(),'not valid'
       end
    --print cast(geometry::STGeomFromText(@string,4326).MakeValid () as varchar(max))
    --print  cast(geography::STGeomFromText(geometry::STGeomFromText(@string,4326).MakeValid ().STAsText(),4326) as varchar(max))
   end
   close cur
   deallocate cur

     
    go
-- добавляем центры домов в качестве точек    
    update  [OSM_LOAD].[dbo].
[ways]set Lat=[geog4326].EnvelopeCenter().Lat,
    Long=[geog4326].EnvelopeCenter().Long
    where [geog4326] is not null

USE [OSM_LOAD]
GO
/****** Object:  UserDefinedFunction [dbo].[address_from_tags2]    Script Date: 27.12.2012 15:41:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Бондарь Михаил
-- Create date:  
-- Description:   Возвращает название улицы и номер дома
-- =============================================
Create FUNCTION [dbo].[address_from_tags2]
(   @wayid bigint
)
RETURNS varchar(255)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @addrstring varchar(255), @street varchar(255), @hnumber varchar(255)
    select @street= (select  [tagvalue]from [OSM_LOAD].[dbo]. [waytags]where [wayid]=@wayid and [tagname]='addr:street')
    select @hnumber= (select  [tagvalue]from [OSM_LOAD].[dbo]. [waytags]where [wayid]=@wayid and [tagname]='addr:housenumber')

    if @street is null or @hnumber is null begin return @addrstring end;

    set @street=
    CASE  
    when PATINDEX ('%улица%',@street)>0 then LTRIM(rtrim(replace (@street,'улица','')))+' ул.'
    when PATINDEX ('%площадь%',@street)>0 then LTRIM(rtrim(replace (@street,'площадь','')))+' пл.'
    when PATINDEX ('%переулок%',@street)>0 then LTRIM(rtrim(replace (@street,'переулок','')))+' пер.'
    when PATINDEX ('%проспект%',@street)>0 then LTRIM(rtrim(replace (@street,'проспект','')))+' прос.'
    when PATINDEX ('%проезд%',@street)>0 then LTRIM(rtrim(replace (@street,'проезд','')))+' пр.'
    when PATINDEX ('%шоссе%',@street)>0 then LTRIM(rtrim(replace (@street,'шоссе','')))+' ш.'
     
    end;
    -- Add the T-SQL statements to compute the return value here
    set @addrstring=@street+'>>>'+@hnumber
    -- Return the result of the function
    RETURN @addrstring

END

go

-- ищем номера домов и улицы, запоминаем
   update [OSM_LOAD].[dbo].
   [ways]set [street]=left(osm_load.dbo.[address_from_tags2] ([wayid]),patindex('%>>>%',osm_load.dbo.[address_from_tags2] ([wayid]))-1),
   [housenumber]=right(osm_load.dbo.[address_from_tags2] ([wayid]),len(osm_load.dbo.[address_from_tags2] ([wayid]))-patindex('%>>>%',osm_load.dbo.[address_from_tags2] ([wayid]))-2)

- -- ПЕРЕД ЗАПУСКОМ СОЗДАЙТЕ АРХИВНУЮ КОПИЮ базы данных BN_ADDRESS!!!
   
  create table #t_string (string varchar(1024))

   declare @ZoneLonP1 float
       ,@ZoneLatP1 float
       ,@ZoneLonP2 float
       ,@ZoneLatP2 float
      ,@TabName varchar(255)

select @TabName='City_Lipeck001' -- название таблицы города!!! Править ТУТ!!!

select @ZoneLonP1=min (long), @ZoneLonP2 = max(long), @ZoneLatP2= min (lat), @ZoneLatP1=max(lat)  
from [OSM_LOAD].[dbo].  
[ways]where  [street]is not null and lat is not null

--select @ZoneLonP1       ,@ZoneLatP1       ,@ZoneLonP2       ,@ZoneLatP2
insert into #t_string  
values ('set dateformat YMD')

insert into #t_string  
values ('insert into [BN_ADDRESS].[dbo]. [Cities]([Name],[CityName],[Comment],[ZoneLonP1],[ZoneLatP1],[ZoneLonP2],[ZoneLatP2],[LastUpdate],[TableName],[Active])')
insert into #t_string  
values ('values (''Липецк-OSM'',''Липецк'',''Список адресов г. Липецк'', -- строка описания города')
insert into #t_string  
values (str(@ZoneLonP1,15,6)+','+
str(@ZoneLatP1,15,6)+','+
str(@ZoneLonP2,15,6)+','+
str(@ZoneLatP2,15,6)+ ','''+convert(nvarchar(30),getdate(),120)
+''','''+@TabName+''',1)')

   -- создаем таблицу и процедуру
  declare @Procedure nvarchar(1024)
set @Procedure='use [BN_ADDRESS]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].['+@TabName+'](
     [IDRec] [int]IDENTITY(1,1) NOT NULL,
     [TimeReg] [datetime]NOT NULL,
     [FullAddress][nvarchar](100) NULL,
     [HomeNum][nvarchar](25) NULL,
     [StreetName][nvarchar](50) NULL,
     [AdminRegion][nvarchar](50) NULL,
     [Region][nvarchar](20) NULL,
     [lat] [float]NULL,
     [lon] [float]NULL
) ON [PRIMARY]'

insert into #t_string  
values (@Procedure)
--EXEC (@Procedure)
print 'создание ограничения таблицы'

set @Procedure='ALTER TABLE [BN_ADDRESS].[dbo].['+@TabName+'] ADD  CONSTRAINT [DF_'+@TabName+'_TimeReg]  DEFAULT (getdate()) FOR  
[TimeReg]GO'
insert into #t_string  
values (@Procedure)

--EXEC (@Procedure)

--EXEC (@Procedure)
print 'создание индекса'
set @Procedure='USE [BN_ADDRESS]
/****** Object:  Index [IX_'+@TabName+'Main]  ******/
CREATE CLUSTERED INDEX [IX_'+@TabName+'Main] ON [dbo].['+@TabName+']
(    [lat]ASC,
     [lon]ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
insert into #t_string  
values (@Procedure)
--EXEC (@Procedure)

-- теперь заполняем таблицу данными
set @Procedure='
INSERT INTO [BN_ADDRESS].[dbo].['+@TabName+']
            (
            [FullAddress],
            [HomeNum],
            [StreetName],
            [AdminRegion],
            [Region],
            [lat],[lon])
     select street+'',''+housenumber,housenumber,street,null,''Липецк'',lat,Long
    from [OSM_LOAD].[dbo].
    [ways]where street is not null and lat is not null'
--insert into #t_string  
--values (@Procedure)
insert into #t_string  
values ('')

insert into #t_string
SELECT  
'INSERT INTO [BN_ADDRESS].[dbo].['+@TabName+'] ( [FullAddress], [HomeNum], [StreetName], [AdminRegion], [Region], [lat],[lon])  
values ('''+street+', '+housenumber+''','''+housenumber+''','''+street+''',null,''Липецк'','+str(lat,15,6)+','+str(Long,15,6)+')'
  FROM [OSM_LOAD].[dbo].
  [ways]where lat is not null and street is not null and wayid in (select max(wayid) from  [OSM_LOAD].[dbo].
  [ways]group by  street,housenumber)

--EXEC (@Procedure)
select * from #t_string
drop table #t_string