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]