An alternative, without the STCrosses function, filtering using STIntersects.

😎

DECLARE @GEOLINE TABLE

(

GEOLINE_ID INT IDENTITY(1,1) NOT NULL

,GEOLINE_LINE GEOMETRY NOT NULL

);

INSERT INTO @GEOLINE (GEOLINE_LINE)

VALUES

(geometry::STGeomFromText('LINESTRING(0 1, 6 0)', 0))

,(geometry::STGeomFromText('LINESTRING(0 2, 5 0)', 0))

,(geometry::STGeomFromText('LINESTRING(0 3, 4 0)', 0))

,(geometry::STGeomFromText('LINESTRING(0 4, 3 0)', 0))

,(geometry::STGeomFromText('LINESTRING(0 5, 2 0)', 0))

,(geometry::STGeomFromText('LINESTRING(0 6, 1 0)', 0));

SELECT

G1.GEOLINE_ID AS START_LINE

,G2.GEOLINE_ID AS COMP_LINE

,G1.GEOLINE_LINE.STIntersection(G2.GEOLINE_LINE).STBuffer(0.1) AS LINE_INTERSECTION

FROM @GEOLINE G1

CROSS APPLY @GEOLINE G2

WHERE G1.GEOLINE_ID > G2.GEOLINE_ID

AND G1.GEOLINE_LINE.STIntersects(G2.GEOLINE_LINE) = 1

UNION ALL

SELECT

G1.GEOLINE_ID

,0

,G1.GEOLINE_LINE AS LINE_INTERSECTION

FROM @GEOLINE G1;