use bn_patp -- имя база данных
go
declare @mname varchar(64)
set @mname='А 17' -- имя маршрута для очистки
declare @mlist_id int
set @mlist_id=(select ml.id from dbo.PATP_MarshrutList as ML
inner join dbo.PATP_Marshruts as M on M.id=ml.Marshrut_id      
where m.name=@mname and m.active=1)
print @mlist_id

declare @todel table (mid int, pid int,mlist_id int)

declare @inwhile int
set @inwhile=1
while @inwhile=1 begin
       --print 'цикл'
if (not object_id('tempdb..#temp0084356') IS NULL)
drop table #temp0084356;
       with points as (
       select mlist_id, point_id, ord,long,lat,[len] from dbo.PATP_MarshrutData as MD
       inner join dbo.PATP_Points as p on p.id=md.point_id)

       select --top 1000      
       m.name, md.id as mid,p.id as pid, md.mlist_id,md.ord,p.caption,
       dbo.PATP_GetDirection (pos.long,pos.lat,p.long,p.lat) as dir1,
       dbo.PATP_GetDirection (p.long,p.lat,pos2.long,pos2.lat) as dir2,
       p.len+pos2.len as lensum,
       dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat)as leng,      
       abs((p.len+pos2.len)-dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat)) as razn,
       p.long as long,p.lat as lat,
       pos.long as plong,pos.lat as plat,
       pos2.long as nlong,pos2.lat as nlat
       , case when (pos.lat<>pos2.lat and pos.long<>pos2.long) then [dbo].[PATP_GetLengthToLine] (p.long,p.lat,pos.lat,pos.long,pos2.lat,pos2.long)
        else -1 end  as lentopoint
        ,smd.id
       into #temp0084356
       from dbo.PATP_MarshrutData as MD
       inner join dbo.PATP_Points as p on p.id=md.point_id
       inner join points as pos on pos.mlist_id=md.mlist_id and pos.ord=md.ord-1
       inner join points as pos2 on pos2.mlist_id=md.mlist_id and pos2.ord=md.ord+1
       inner join dbo.PATP_MarshrutList as ml on ml.id=md.mlist_id
       inner join dbo.PATP_Marshruts as M on m.id=ml.Marshrut_id
       left join PATP_SubMarshrutData as SMD on SMD.mData_id=md.id
       where      

       md.mlist_id=@mlist_id and -- фильтр по маршруту

       (abs(dbo.PATP_GetDirection (pos.long,pos.lat,p.long,p.lat)-dbo.PATP_GetDirection (p.long,p.lat,pos2.long,pos2.lat))<=1 ) -- направления различаются на 1 или менее градусов
       and p.pointtype_id<>2 and p.iscontrolpoint<>1 and p.isend<>1-- не остановка, не контрольная, не конечная
       and abs((p.len+pos2.len)-dbo.PATP_GetLength2Points (pos.long,pos.lat,pos2.long,pos2.lat))<=1 -- разница расстояний отрезков ([1-2]+[2-3]) и [1-3] меньше 4 метров
       and      
       case when (pos.lat<>pos2.lat and pos.long<>pos2.long) then [dbo].[PATP_GetLengthToLine] (p.long,p.lat,pos.lat,pos.long,pos2.lat,pos2.long)
        else -1 end <=0.5 -- высота треуголника меньше 1 метров
       and (p.caption is null or rtrim(ltrim(p.caption))='') -- нет описания у точки
       and smd.id is null -- нет связи с подмаршрутом
       if exists (select  top 1 * from #temp0084356 order by name,ord) begin
        --select  * from #temp0084356 order by name,ord;

        --select m.name, m.active, m.c as 'point in m',t.c 'point to del', (0.0000+t.c)/m.c*100 as '%' from      
        --(select m.name name, count(*)as c,m.active as active from dbo.PATP_MarshrutData as MD
        --inner join dbo.PATP_MarshrutList as ML on ML.id=MD.mList_id
        --inner join dbo.PATP_Marshruts as M on m.id=ML.Marshrut_id
        --group by m.name,m.active
        --) as m
        --left join (select  name, count(*) as c from #temp0084356 group by name) as t on t.name=m.name
        --where m.active=1
        --order by '%' desc

        delete from @todel
        declare cur cursor local read_only for
         select mid,pid,mlist_id,ord from #temp0084356
         order by mlist_id,ord
        declare @mid int, @pid int, @curmlistid int,@ord int
        declare @lastmid int, @lastpid int, @lastcurmlistid int, @lastord int
        set @lastord=-1
        set @lastcurmlistid=-1
        open cur
        fetch next from cur into @mid, @pid, @curmlistid, @ord
        while @@fetch_status=0 begin
         if @curmlistid<>@lastcurmlistid set @lastord=-1

         if @lastord+1<>@ord begin
          insert into @todel
          values (@mid,@pid,@curmlistid);
         end
         set @lastord=@ord
         set @lastcurmlistid=@curmlistid      
         fetch next from cur into @mid, @pid, @curmlistid, @ord

        end
        close cur

        deallocate cur
        --select * from @todel

        ---- удаление найденных точке в маршруте(убрать комментарий для удаления)
        delete from md
        from dbo.PATP_MarshrutData as MD
        inner join @todel as temp on MD.id=temp.mid
        where MD.mlist_id=@mlist_id;

        -- удаление записей о в справочнике точек
        delete from p
        from dbo.PATP_Points as p
        inner join @todel as temp on p.id=temp.pid
        where temp.mlist_id=@mlist_id;

        -- обновление сведений о расстояниях для точек
        with points2 as (
        select md.mlist_id, md.point_id, md.ord,p.long,p.lat,p.[len] from dbo.PATP_MarshrutData as MD
        inner join dbo.PATP_Points as p on p.id=md.point_id)
        update p
        set p.len=dbo.PATP_GetLength2Points (pos.long,pos.lat,p.long,p.lat)
         from dbo.PATP_MarshrutData as MD
        inner join dbo.PATP_Points as p on p.id=md.point_id
        inner join points2 as pos on pos.mlist_id=md.mlist_id and pos.ord=md.ord-1
        where abs(p.[len]-dbo.PATP_GetLength2Points (pos.long,pos.lat,p.long,p.lat))>0.01
        and MD.mlist_id=@mlist_id;
       end
       else      
        set @inwhile=0
            
end;

drop table #temp0084356