... split the @h linestring into an ordered list of individual lines (the starting point of each line is the reference point for the line), intersect each individual line with the polygon @g, and split the intersections to individual lines (if more than one). If the starting point of the intersection is closer to the reference point then keep the intersection as is otherwise reverse it (endpoint of intersection becomes the startpoint and vice versa). Finally aggregate the intersections in a geometry collection in order (of reference point/line ordinal, intersection line ordinal)
DECLARE @g geography;
DECLARE @h geography;
SET @g = geography::STGeomFromText('POLYGON((25.84568061650872 49.69890972163677,25.844941083373904 49.69189373653131,25.900405504271674 49.69173426681988,25.900898532630585 49.69667747509544,25.84568061650872 49.69890972163677))', 4326);
SET @h = geography::STGeomFromText('LINESTRING(25.84863871143376 49.68886378946257,25.85578746277756 49.702736200117556,25.874275603076814 49.69013957742882,25.88117784964471 49.696518021079896,25.890791686361744 49.693647826244444)', 4326);
--reverse linestring..test
--SET @h = geography::STGeomFromText('LINESTRING(25.890791686361744 49.693647826244444, 25.88117784964471 49.696518021079896, 25.874275603076814 49.69013957742882, 25.85578746277756 49.702736200117556, 25.84863871143376 49.68886378946257)', 4326);
select
concat(
'GEOMETRYCOLLECTION(',
string_agg(directionline.ToString(), ',') within group (order by pnum, ilnum),
')'
) as geometrycollection_string
--geography::CollectionAggregate(directionline) -- no option for order...
from
(
select
p.pnum,
io.ilnum,
case when
--reverse the interection when starting point is farther to the reference point
io.iline.STStartPoint().STDistance(p.spoint) > io.iline.STEndPoint().STDistance(p.spoint)
then
--...assume intersection is linestring since @h is linestring
cast(
replace(
concat( --lazy...string manipulation
cast('linestring(' as varchar(4000)),
translate(io.iline.STEndPoint().ToString(),'()',' '),',', translate(io.iline.STStartPoint().ToString(),'()',' '), ')'
)
,'point','')
as geography)
--if the starting point of the intesection is closer to the reference point then keep the intersection as is
--this also covers if intersection is point (when @h is tangent to @g)
else io.iline end as directionline
from
(
select
@h.STPointN(n.pnum) as spoint, --starting point of each line/reference
n.pnum, --starting point ordinal
--linestring starting from spoint till the next point
cast(
replace(
concat(
cast('linestring(' as varchar(4000)), --lazy...
translate(@h.STPointN(n.pnum).ToString(),'()',' '),',', translate(@h.STPointN(n.pnum+1).ToString(),'()',' '), ')'
)
,'point','')
as geography) as thelines --each line of the @h linestring
from
(
--points (excluding the last) of the @h linestring
select top (@h.STNumPoints()-1) row_number() over(order by @@spid) as pnum --point number
from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as a(n)
cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as b(n)
) as n
) as p
--get each intersection "object" of each line (in the @h linestring) with @g
cross apply
(
--intersection of each line (of the @h linestring) with the polygon @g
--the intersection could be multiple "objects", get each object
select p.thelines.STIntersection(@g).STGeometryN(n.ilnum) as iline, ilnum
from
(
--split any multi-object intersection into individual objects
select top (p.thelines.STIntersection(@g).STNumGeometries()) row_number() over(order by @@spid) as ilnum
from (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as a(n)
cross join (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) as b(n)
) as n
) as io
) as src
--order by pnum, ilnum
