在SQL Server中绘制两个行字符串之间的垂直线

Question

我在SQL Server中有2行字符串。

declare @s geometry  = geometry::STGeomFromText
(
'LINESTRING('
+     convert(varchar(max),35.688889,128) +' '+ convert(varchar(max),51.314722,128) -- 
+', '+convert(varchar(max),32.750833,128) +' '+ convert(varchar(max),51.862778,128)+')',0 -- 
);
declare @d geometry  = geometry::STGeomFromText
(
'LINESTRING('
+     convert(varchar(max),34.761111,128) +' '+ convert(varchar(max),52.144444,128)
+', '+convert(varchar(max),33.56,128) +' '+ convert(varchar(max),53.4475,128)+')',0
);

我如何在它们之间画一条线从中间的@s与90-degree angle到@d?

我有这样的代码:

DECLARE @result geometry = @s.STEnvelope().STCentroid().ShortestLineTo(@d).ToString();

但是这个代码只是找到不垂直的最短线!

Answer 1

画一条延伸的垂直线(到@s)，从@s的中点开始，尽可能与@d相交(延伸线覆盖两条直线的所有点的minX, maxX的平面@s&@d[或minY, maxY，如果@s是水平的])

declare @s geometry = geometry::STGeomFromText ( 'LINESTRING(' + convert(varchar(max),35.688889,128) +' '+ convert(varchar(max),51.314722,128) +', '+convert(varchar(max),32.750833,128) +' '+ convert(varchar(max),51.862778,128)+')',0)
declare @d geometry = geometry::STGeomFromText ( 'LINESTRING(' + convert(varchar(max),34.761111,128) +' '+ convert(varchar(max),52.144444,128) +', '+convert(varchar(max),33.56,128) +' '+ convert(varchar(max),53.4475,128)+')',0 )
--horizontal @s
--select @s = geometry::STGeomFromText ( 'LINESTRING(' + convert(varchar(max),35.688889,128) +' '+ convert(varchar(max),51.314722,128) +', '+convert(varchar(max),32.750833,128) +' '+ convert(varchar(max),51.314722,128)+')',0)

declare @extperpendicular geometry;
select @extperpendicular = geometry::STGeomFromText 
(
concat('LINESTRING(',
--start point of extended perpendicular to @s
convert(varchar(100), case when pm is null then smidX else minX end, 2), ' ', convert(varchar(100), case when pm is null then minY else pm*minX + pc end, 2), 
',', 
--end point of extended perpendicular to @s
convert(varchar(100), case when pm is null then smidX else maxX end, 2), ' ', convert(varchar(100), case when pm is null then maxY else pm*maxX + pc end, 2), 
')')
, 0)
from
(
select
p.*, 
--middle point of @s is on perpendicular to @s --> smidY=pm*smidX+pc
smidY-(pm*smidX) as pc,
gbx.*,
gby.*
from
(
select *, -(sX2-sX1)/nullif(sY2-sY1, 0) as pm --slope of perpendicular to @s
from
(
select  
--mid point of @s
@s.STEnvelope().STCentroid().STX as smidX, @s.STEnvelope().STCentroid().STY as smidY,
--start point of @s
@s.STStartPoint().STX as sX1, @s.STStartPoint().STY as sY1, 
--end point of @s
@s.STEndPoint().STX as sX2, @s.STEndPoint().STY as sY2
) as s
) as p
--boundaries of plane
--X
cross apply
(
select min(h.x) as minX, max(h.x) as maxX
from (values(@s.STStartPoint().STX),(@s.STEndPoint().STX),(@d.STStartPoint().STX),(@d.STEndPoint().STX)) as h(x)
) as gbx 
--Y. when @s is horizontal
cross apply
(
select min(v.y) as minY, max(v.y) as maxY
from (values(@s.STStartPoint().STY),(@s.STEndPoint().STY),(@d.STStartPoint().STY),(@d.STEndPoint().STY)) as v(y)
) as gby 
) as allpoints;
select * from (values(@s),(@d),(@extperpendicular)) as thelines(line);
--line between @s & @d
select @extperpendicular.STIntersection(@s).ToString() as startpoint, @extperpendicular.STIntersection(@d).ToString() as endpoint;