Find length of geometry line after it intersects with geometry polygon using STIntersect

Question

SQL SERVER 2012

I need to find the length of the geometry of a line after I use STIntersect to intersect lines against polygon geometry.
For instance, I have a line that is 7731 meters long but then I STIntersect it against a set of polygons, I need to find what the length of the line is in each of the polygons.

Output table would look like

ReferenceID    PolygonID   LineID   Length
12324          3234        24661    1635
12325          3233        24652     663
12326          3236        24653     256
12327          3365        24634     165

Currently using this but not able to return geometry nor the length

insert [VMS_OBS_LINES_INTERSECT] (CA_Reference_Key, STAT_AREA,WATERS,GNMFSAREA, Grid_ID, Length)select l.CA_Reference_Key,g.stat_area,g.waters, g.GNMFSAREA , g.Grid_ID, g.shape.STIntersection(l.shape).STLength()
FROM GRID_AREA_SQL g, VMS_OBS_COMBINE_LINES_AI l
WHERE g.shape.STIntersects(l.shape) = 1;

Does this answer your question? – MarkD Dec 31 '12 at 06:56 — MarkD, Dec 31 '12 at 06:56

score 4 · Answer 1 · answered Dec 31 '12 at 18:28

declare @g geometry, @l geometry;

select @g = geometry::STGeomFromText('POLYGON ((0 0, 10 0, 10 10, 0 10, 0 0))', 0), --10x10 box
       @l = geometry::STGeomFromText('LINESTRING( -10 -10 , 20 20 )', 0); --a line that will go through the corners of the box

select @g.STIntersection(@l).STLength();

Find length of geometry line after it intersects with geometry polygon using STIntersect

1 Answers1