use BN
go
SET DATEFORMAT dmy
go
declare @filename as varchar(3000)
-- тут имя файла!!!
set @filename='d:\1\zone_cf.kml' -- путь и имя выходного файла - путь должен быть доступен для сервера SQL
declare @file_description as varchar(3000)
set @file_description=' <name>Выгрузка зон</name>
<description><![CDATA[
<img src="http://www.m2m-t.ru/images/iNewsGirl.jpg"
width="300"/>
<br /><table>
<tr><td><a href="http://www.m2m-t.ru/rdc/?map_id=86#anchor_map">Компания:М2М Телематика Тамбов</a></td></tr>
</table>
<br/>
]]></description>
<open>1</open>
<Snippet maxLines="2">Зоны'+N'</Snippet>
'
DECLARE @Text1 Nvarchar(4000),
@FS int,
@OLEResult int,
@FileID int,
@hr int,
@source varchar(30),
@desc varchar (200)
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUTPUT
IF @OLEResult <> 0
BEGIN
PRINT 'Scripting.FileSystemObject'
GOTO Error_Handler
END
execute @OLEResult = sp_OAMethod @FS,'CreateTextFile',@FileID OUTPUT, @filename,1,true
IF @OLEResult <> 0
BEGIN
PRINT 'CreateTextFile'
GOTO Error_Handler
END
-- тег <Style> </Style> задает внешний вид точек и путей - используется для placemark
set @Text1 = N'<?xml version="1.0" encoding="UTF-8"?>
<kml xmlns="http://www.opengis.net/kml/2.2" xmlns:gx="http://www.google.com/kml/ext/2.2"
xmlns:kml="http://www.opengis.net/kml/2.2" xmlns:atom="http://www.w3.org/2005/Atom">
<Document>'+@file_description+'
<Style id="lineStyle">
<LineStyle>
<color>99ffac59</color>
<width>6</width>
</LineStyle>
</Style>
<StyleMap id="msn_track-none">
<Pair>
<key>normal</key>
<styleUrl>#sn_track-none0</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_track-none</styleUrl>
</Pair>
</StyleMap>
<Style id="sh_caution">
<IconStyle>
<scale>0.816667</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/shapes/caution.png</href>
</Icon>
<hotSpot x="0.5" y="0" xunits="fraction" yunits="fraction"/>
</IconStyle>
<ListStyle>
</ListStyle>
</Style>
<StyleMap id="msn_caution">
<Pair>
<key>normal</key>
<styleUrl>#sn_caution</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_caution</styleUrl>
</Pair>
</StyleMap>
<Style id="sn_caution">
<IconStyle>
<scale>0.7</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/shapes/caution.png</href>
</Icon>
<hotSpot x="0.5" y="0" xunits="fraction" yunits="fraction"/>
</IconStyle>
<ListStyle>
</ListStyle>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
set @Text1 = N' </Style> <Style id="sn_blu-blank">
<IconStyle>
<scale>1.1</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/paddle/blu-blank.png</href>
</Icon>
<hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
</IconStyle>
<ListStyle>
<ItemIcon>
<href>http://maps.google.com/mapfiles/kml/paddle/blu-blank-lv.png</href>
</ItemIcon>
</ListStyle>
</Style>
<Style id="sh_blu-blank">
<IconStyle>
<scale>1.3</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/paddle/blu-blank.png</href>
</Icon>
<hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
</IconStyle>
<ListStyle>
<ItemIcon>
<href>http://maps.google.com/mapfiles/kml/paddle/blu-blank-lv.png</href>
</ItemIcon>
</ListStyle>
</Style>
<StyleMap id="msn_blu-blank">
<Pair>
<key>normal</key>
<styleUrl>#sn_blu-blank</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_blu-blank</styleUrl>
</Pair>
</StyleMap>
<StyleMap id="msn_ltblu-stars">
<Pair>
<key>normal</key>
<styleUrl>#sn_ltblu-stars</styleUrl>
</Pair>
<Pair>
<key>highlight</key>
<styleUrl>#sh_ltblu-stars</styleUrl>
</Pair>
</StyleMap>
<Style id="sn_ltblu-stars">
<IconStyle>
<scale>1.1</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/paddle/ltblu-stars.png</href>
</Icon>
<hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
</IconStyle>
<ListStyle>
<ItemIcon>
<href>http://maps.google.com/mapfiles/kml/paddle/ltblu-stars-lv.png</href>
</ItemIcon>
</ListStyle>
</Style>
<Style id="sh_ltblu-stars">
<IconStyle>
<scale>1.3</scale>
<Icon>
<href>http://maps.google.com/mapfiles/kml/paddle/ltblu-stars.png</href>
</Icon>
<hotSpot x="32" y="1" xunits="pixels" yunits="pixels"/>
</IconStyle>
<ListStyle>
<ItemIcon>
<href>http://maps.google.com/mapfiles/kml/paddle/ltblu-stars-lv.png</href>
</ItemIcon>
</ListStyle>
</Style>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
print 'выборка зон'
-- экспортируем данные о зонах
declare cur_zone cursor local for
select z.zoneid, z.name,z.type,z.Width from dbo.FLEET_Zone as Z
--inner join #temp_zone as temp on temp.zonename=z.name -- сцепить с врем табл загруженных зон
-- сцепка сработает как фильтр - будут только зоны, названия которых были в файле
where z.zoneactive=1 -- только не удаленные зоны
--and z.type=3 -- только окружности
order by z.name
open cur_zone
-- откроем папку с зонами
set @Text1 = N' <Folder>
<name>Точки</name>
<visibility>1</visibility>
<description><![CDATA[<table>
<tr><td>Зоны для посещения</td></tr>
<tr><td>Компания: М2М Телематика Тамбов</td></tr>
</table>]]></description><open>0</open>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
declare @zone_id int -- данные о ид зоны из курсора
declare @zone_name Nvarchar(256) -- данные о имени зоны из курсора
declare @ztype int
-- данные о центральной (первой точке) зоны - это центр окружности
declare @zone_long float
declare @zone_lat float
declare @zone_img Nvarchar (500)
declare @M_PI float
set @M_PI= PI();
declare @lat_center float;
declare @lon_center float;
declare @d_rad float;
declare @radius float
declare @i float
declare @radial float;
declare @lat_rad float;
declare @lon_rad_tmp float;
declare @lon_rad float;
declare @a numeric(15,8)
declare @b numeric(15,8)
fetch next from cur_zone into @zone_id,@zone_name,@ztype,@radius
while @@fetch_status=0
begin
--print @zone_name
if @ztype=1 begin -- точка
select top 1 @zone_long=Long,@zone_lat=Lat from dbo.FLEET_ZoneDescript
where ZoneID=@zone_id
order by OrderBy desc;
--print '---'+@zone_name+' ; '+isnull(@zone_img,'')
set @Text1 = N' <Placemark>
<name>'+@zone_name+N'</name>
<description><![CDATA['+isnull(@zone_img,N'')+N'<table>
<tr><td>'+@zone_name+N'</td></tr>
</table>]]></description>
<visibility>0</visibility>
<Snippet maxLines="2">'+@zone_name+N'</Snippet>
<styleUrl>#m_ylw-pushpin</styleUrl>
<Polygon>
<tessellate>1</tessellate>
<outerBoundaryIs>
<LinearRing>
<coordinates>'
--+ltrim(rtrim(str(@zone_long,15,6)))+','+ltrim(rtrim(str(@zone_lat,13,6)))+N'</coordinates></Point></Placemark>';
-- print @text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
declare cur cursor local read_only for
select Long,Lat from dbo.FLEET_ZoneDescript
where ZoneID=@zone_id
order by OrderBy desc;
open cur
fetch next from cur into @zone_long, @zone_lat
while @@fetch_status=0 begin
set @Text1=ltrim(rtrim(str(@zone_long,15,6)))+','+ltrim(rtrim(str(@zone_lat,13,6)))+',0 '
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
fetch next from cur into @zone_long, @zone_lat
end --while @@fetch_status=0
close cur
deallocate cur
set @Text1 = ltrim(rtrim(str(@zone_long,15,6)))+','+ltrim(rtrim(str(@zone_lat,13,6)))+N'0</coordinates>
</LinearRing>
</outerBoundaryIs>
</Polygon>
</Placemark> '
--+ltrim(rtrim(str(@zone_long,15,6)))+','+ltrim(rtrim(str(@zone_lat,13,6)))+N'</coordinates></Point></Placemark>';
-- print @text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
end --if @ztype=1 begin
if @ztype=2 begin -- маршрут
set @Text1 = N' <Placemark>
<name>'+@zone_name+N'</name>
<visibility>0</visibility>
<description><![CDATA['+isnull(@zone_img,N'')+N'<table>
<tr><td>'+@zone_name+N'</td></tr>
</table>]]></description>
<Snippet maxLines="2">'+@zone_name+N'</Snippet>
<styleUrl>#msn_ltblu-stars</styleUrl>
<LineString>
<tessellate>1</tessellate>
<coordinates>'
-- print @text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
declare cur cursor local read_only for
select Long,Lat from dbo.FLEET_ZoneDescript
where ZoneID=@zone_id
order by OrderBy desc;
open cur
fetch next from cur into @zone_long, @zone_lat
while @@fetch_status=0 begin
set @Text1=ltrim(rtrim(str(@zone_long,15,6)))+','+ltrim(rtrim(str(@zone_lat,13,6)))+',0 '
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
fetch next from cur into @zone_long, @zone_lat
end --while @@fetch_status=0
close cur
deallocate cur
set @Text1 = N' </coordinates>
</LineString>
</Placemark>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
end --if @ztype=2 begin
if @ztype=3 begin -- окружность
-- выборка данных о центре окружности.
select top 1 @zone_long=Long,@zone_lat=Lat from dbo.FLEET_ZoneDescript
where ZoneID=@zone_id
order by OrderBy desc;
--print '---'+@zone_name+' ; '+isnull(@zone_img,'')
set @Text1 = N'<Folder>
<name>'+@zone_name+N'</name>
<visibility>0</visibility>
<description><![CDATA['+isnull(@zone_img,N'')+N'<table>
<tr><td>'+@zone_name+N'</td></tr>
</table>]]></description>
<Snippet maxLines="2">'+@zone_name+N'</Snippet>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
set @Text1 = N' <Placemark>
<name>Окружность</name>
<visibility>0</visibility>
<styleUrl>#msn_ltblu-stars</styleUrl>
<Polygon>
<tessellate>1</tessellate>
<outerBoundaryIs>
<LinearRing>
<coordinates>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
select @lat_center = (@zone_lat)*(@M_PI / 180),
@lon_center = (@zone_long)*(@M_PI / 180),
@d_rad = @radius / 6378137;
set @i=0
while @i <= 360 begin
set @radial = @i *(@M_PI / 180);
set @lat_rad = Asin(Sin(@lat_center) * Cos(@d_rad) + Cos(@lat_center) * Sin(@d_rad) * Cos(@radial));
set @lon_rad_tmp = ATN2(Sin(@radial) * Sin(@d_rad) * Cos(@lat_center), Cos(@d_rad) - Sin(@lat_center) * Sin(@lat_rad));
select @a=(@lon_center + @lon_rad_tmp + @M_PI),@b=(2.0000 * @M_PI)
--select @a, @b
set @lon_rad=@a-floor(@a/@b)*@b - @M_PI;
--set @lon_rad = (@a % @b) - @M_PI;
set @Text1 =cast((@lon_rad)* (180 / @M_PI) as nvarchar(10)) + N',' + cast(@lat_rad* (180 / @M_PI) as nvarchar(10)) + N',0 ';
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
set @i=@i+5
end -- while @i <= 360 begin
set @Text1 =N' </coordinates>
</LinearRing>
</outerBoundaryIs>
</Polygon>
</Placemark>';
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
-- поставить точку центра
set @Text1 = N' <Placemark>
<name>'+@zone_name+N'</name>
<visibility>0</visibility>
<description><![CDATA['+isnull(@zone_img,N'')+N'<table>
<tr><td>'+@zone_name+N'</td></tr>
</table>]]></description>
<Snippet maxLines="2">'+@zone_name+N'</Snippet>
<styleUrl>#msn_ltblu-stars</styleUrl>
<Point> <coordinates>'+str(@zone_long,15,8)+','+str(@zone_lat,13,8)+N'</coordinates></Point></Placemark>';
-- print @text1
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
---- закрыть группу
set @Text1 = N' </Folder>'
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
end --if @ztype=3 begin
fetch next from cur_zone into @zone_id,@zone_name,@ztype,@radius
end -- open cur_zone while @@fetch_status=0 begin
set @Text1 = N'</Folder>
</Document>
</kml>' -- закрыли папку с зонами
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', NULL, @Text1
IF @OLEResult <> 0
BEGIN
PRINT 'WriteLine'
GOTO Error_Handler
END
close cur_zone
deallocate cur_zone
print '******закончено'
goto Done
Error_Handler:
PRINT '*** ERROR ***'
EXEC @hr = sp_OAGetErrorInfo null, @source OUT, @desc OUT
SELECT hr = CONVERT (binary(4), @hr), source = @source, description = @desc
Done:
PRINT 'Done'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
--drop table #temp -- удаление временной таблицы
--drop table #temp_zone
