1

I am creating a LINESTRING geometry type that is based on two point's geometries. To get the two different points, I am joining on a to and from id field.

I have an sql query like this:

SELECT geometry::STGeomFromText(
CONCAT('LINESTRING(',
(SELECT CONCAT(fstruc.Shape.STX, ' ', fstruc.Shape.STY)),
',', 
(SELECT CONCAT(tstruc.Shape.STX, ' ', tstruc.Shape.STY)),
')')
, 103772) geom, 

FROM dbo.SAN_TELEVISE vid

JOIN dbo.san_struc fstruc
ON fstruc.cid = vid.f_struc_cid

JOIN dbo.san_struc tstruc 
ON tstruc.cid = vid.t_struc_cid

WHERE fstruc.cid IS NOT NULL 
AND tstruc.cid IS NOT NULL
-- THIS DOESN'T WORK: AND geom.STIsValid() = 1

The query works, I am getting a pretty good result, but there are some bad data rows here though, and ArcMap is choking on some line strings that only have one point. So I want to apply a filter to the query to eliminate these bad rows before ArcMap gets them.

Something like geom.STIsValid ( ) = 1 would do it, but I can't get my sql query to run if I add this to the where clause. I get the error: Cannot find either column "geom" or the user-defined function or aggregate "geom.STIsValid", or the name is ambiguous

1 Answer 1

2

Found an alternative solution. It uses .MakeValid() at the end of the geometry type to fix invalid geometries.

SELECT vid.*, geometry::STGeomFromText(
CONCAT('LINESTRING(',
(SELECT CONCAT(fstruc.Shape.STX, ' ', fstruc.Shape.STY)),
',', 
(SELECT CONCAT(tstruc.Shape.STX, ' ', tstruc.Shape.STY)),
')')
, 103772).MakeValid() geom, 
(SELECT cid
FROM dbo.san_pipe 
WHERE vid.f_struc_cid = from_id 
AND vid.t_struc_cid = to_id) found_pipe_id

FROM dbo.SAN_TELEVISE vid
JOIN dbo.san_struc fstruc
ON fstruc.cid = vid.f_struc_cid

JOIN dbo.san_struc tstruc 
ON tstruc.cid = vid.t_struc_cid

WHERE fstruc.cid IS NOT NULL 
AND tstruc.cid IS NOT NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.