tpcolson

Lines, Lines, Every Where Lines

Blog Post created by tpcolson Champion on Jan 24, 2015

Messin' up the scenery, breakin' my mind. Do this, don't do that, can't you read the LINE? In Hey Neighbor? What's Your Value? , Rasters, Pixels and Code Oh My!, and Using SQL to Auto-Populate XY Values I had introduced some SQL trickery to automate some data entry. Probably should have mentioned those methods mostly just work on points....

 

For our GIS data standard, we also want to collect the same administrative values and automation for line types such as roads, trails, and other linear features. Problem is, there's a lot of vertices (nodes, coordinate pairs) on a line, which one to use for the SQL trickery?

 

There are some existing SQL Geography methods for manipulating well-known-text from a Geography line instance, but none of them give me a midpoint value, and they certainly don't lend themselves well to being part of a trigger.

 

In order to easily get a line midpoint value from Geography, you'll need the SQL Server Spatial Tools - Home  installed, which is as easy as registering a DLL through SQL on your database instance- works on 2008 and 2012.

 

A line midpoint can be queried as

 

SELECT dbo.LocateAlongGeog(SHAPE, SHAPE.STLength()/2).Lat FROM [dbo].[SOMELINE]
SELECT dbo.LocateAlongGeog(SHAPE, SHAPE.STLength()/2).Long FROM [dbo].[SOMELINE]

 

Wrapping that up in a trigger to populate the LAT, LON, and Elevation fields we get:

 

CREATE TRIGGER    [dbo].[SOMELINE_GEO]
ON    [dbo].[SOMELINE]
AFTER INSERT, UPDATE NOT FOR REPLICATION
AS   
BEGIN    
SET NOCOUNT ON;  
 UPDATE p SET  
LON = CASE WHEN p.SHAPE IS NULL THEN p.STStartPoint.LON ELSE p.SHAPE.Long END,  
LAT = CASE WHEN p.SHAPE IS NULL THEN p.STStartPoint.LAT ELSE p.SHAPE.Lat END,
ELEVATION = (SELECT
pdata.getValueByLoc(1,p.SHAPE.Long,p.SHAPE.Lat)  FROM [dbo].[DEM10MP])
  FROM  
    dbo.SOMELINE AS p 
  INNER JOIN  
    inserted AS i 
    ON i.GlobalID = p.GlobalID 
END
GO

 

Polygons don't require the "special function" and simply can be queried for midpoint values with:

 

SELECT SHAPE.EnvelopeCenter().Lat
FROM [SOMEPOLY
SELECT SHAPE.EnvelopeCenter().Long
FROM [SOMEPOLY]

Outcomes