21.10.2017
Эффективная Навигация М2М
[ Новые сообщения · Участники · Правила форума · Поиск · RSS ]
Страница 1 из 11
Форум (скрипты, вопросы...) » CyberFleet » Скрипты для CyberFleet, CrossPoint » Скрипты загрузки адресной базы из OSM
Скрипты загрузки адресной базы из OSM
logoffДата: Пятница, 28.12.2012, 10:48 | Сообщение # 1
Тамбов
Группа: Администраторы
Сообщений: 649
Репутация: 19
Статус: Offline
Для статьи http://bnc.ucoz.net/publ....-1-0-99

Листинг 1 - загрузка данных с файл osm в базу данных OSM_LOAD

Код
use OSM_LOAD  
go

DECLARE @x xml;
SET @x = (SELECT * FROM OPENROWSET(
BULK 'd:\1\Lipeck.xml',
SINGLE_BLOB) AS x);
CREATE TABLE nodes (
nodeid bigint,
latitude float,
longitude float,
geog4326 geography
);
INSERT INTO nodes

SELECT
OSMnode.value('@id', 'bigint') AS nodeid,
OSMnode.value('@lat', 'float') AS latitude,
OSMnode.value('@lon', 'float') AS longitude,
geography::Point(OSMnode.value('@lat', 'float'), OSMnode.value('@lon', 'float'), 4326) AS geog4326
FROM
@x.nodes('/osm/node') AS OSM(OSMnode);

CREATE TABLE ways (
wayid bigint
);
INSERT INTO ways
SELECT
OSMWay.e.value('(@id)[1]', 'bigint') AS 'WayID'
FROM
@x.nodes('/osm/way') AS OSMWay(e)

CREATE TABLE waytags (
wayid bigint,
tagname varchar(32),
tagvalue varchar(32)
);
INSERT INTO waytags
SELECT
OSMWay.e.value('(@id)[1]', 'bigint') AS 'WayID',
OSMWayTag.e.value('@k', 'nvarchar(32)') AS 'TagName',
OSMWayTag.e.value('@v', 'nvarchar(32)') AS 'TagValue'
FROM
@x.nodes('/osm/way') AS OSMWay(e)
CROSS APPLY
OSMWay.e.nodes('tag') AS OSMWayTag(e)

--SELECT
--w.wayid,
--wtn.TagValue AS wayname,
--wt.TagValue AS highwaytype
--FROM
--ways w
--INNER JOIN waytags wt ON w.wayid = wt.wayid AND wt.TagName = 'Highway'
--LEFT JOIN waytags wtn ON w.wayid = wtn.wayid AND wtn.TagName = 'Name'
--WHERE
--wt.TagValue IN ('motorway', 'motorway_Link', 'trunk', 'trunk_Link', 'primary', 'primary_Link', 'secondary', 'tertiary', 'residential')

CREATE TABLE waynodes (
orderid bigint identity(1,1),
wayid bigint,
nodeid bigint
);
INSERT INTO waynodes (wayid, nodeid)
SELECT
OSMWay.e.value('(@id)[1]', 'bigint') AS 'WayID',
OSMWayNode.e.value('(@ref)[1]', 'bigint') AS 'NodeID'
FROM
@x.nodes('/osm/way') AS OSMWay(e)
CROSS APPLY
OSMWay.e.nodes('nd') AS OSMWayNode(e)

ALTER TABLE ways
ADD geog4326 geography,
string varchar(1500),
street varchar(255) NULL,
    housenumber varchar(50) NULL,
    Lat float null, Long float NULL;
go

-- создание вспомогательных индексов
CREATE CLUSTERED INDEX [ClusteredIndex-20121227-174114] ON [OSM_LOAD].[dbo].
[waynodes](
     [wayid]ASC,
     [nodeid]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, FILLFACTOR = 100) ON  
[PRIMARY]GO

CREATE CLUSTERED INDEX [ClusteredIndex-20121227-174220] ON [OSM_LOAD].[dbo].
[waytags](
     [wayid]ASC,
     [tagname]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, FILLFACTOR = 100) ON  
[PRIMARY]GO

CREATE UNIQUE CLUSTERED INDEX [ClusteredIndex-20121227-175459] ON [dbo].
[nodes](
     [nodeid]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, FILLFACTOR = 100) ON  
[PRIMARY]GO

UPDATE ways
SET string = 'LINESTRING(' + STUFF((
SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
FROM
ways w JOIN waynodes wn ON w.wayid = wn.wayid
JOIN nodes n ON wn.nodeid = n.nodeid
WHERE wn.wayid = ways.wayid
ORDER BY w.wayid, orderid
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(1300)'),1,1,'') +')'
 
logoffДата: Пятница, 28.12.2012, 10:52 | Сообщение # 2
Тамбов
Группа: Администраторы
Сообщений: 649
Репутация: 19
Статус: Offline
Листинг 2 - расчет центра линии
Код
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
 
logoffДата: Пятница, 28.12.2012, 10:54 | Сообщение # 3
Тамбов
Группа: Администраторы
Сообщений: 649
Репутация: 19
Статус: Offline
Листинг 3 - доп обработки (создается вспомогательная функция), заполняется адрес.
Код
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)
 
logoffДата: Пятница, 28.12.2012, 11:06 | Сообщение # 4
Тамбов
Группа: Администраторы
Сообщений: 649
Репутация: 19
Статус: Offline
2013/03/11 исправил листинг с целью убрать дубли по адресам.

Листинг 4 - подготовка скрипта для импорта базы данных в BN_ADDRESS.
Код
- -- ПЕРЕД ЗАПУСКОМ СОЗДАЙТЕ АРХИВНУЮ КОПИЮ базы данных 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


на выходе таблица, со строками скрипта для запуска.
Никакие файлы передавать не требуется - скрипт полностью содержит всю информацию для загрузки
 
logoffДата: Среда, 22.01.2014, 12:02 | Сообщение # 5
Тамбов
Группа: Администраторы
Сообщений: 649
Репутация: 19
Статус: Offline
Исправлены все скрипты - типы данных идентификаторов исправлены с int на bigint
Так же исправлено наименование идекса - добавлено слова Main в конец
 
Форум (скрипты, вопросы...) » CyberFleet » Скрипты для CyberFleet, CrossPoint » Скрипты загрузки адресной базы из OSM
Страница 1 из 11
Поиск:

LogOff © 2017
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС