Skip navigation
All People > tpcolson > Finding Sasquatch with ArcGIS Pro > 2015 > January > 24

In Real-time Geodatabase Replication? Part 1 you were introduced to SQL Real-Time Replication. One requirement of SQL Merge Replication is that the table being replicated must have a rowguidcol. This is easy with a cursor:

 

DECLARE 
@sql VARCHAR(500), 
@tableName VARCHAR(128)
DECLARE gandgCursor CURSOR 
FOR 
SELECT 
table_name
FROM 
SDE_column_registry
WHERE 
table_name like 'CR%' 
ORDER BY 
table_name ASC 
OPEN gandgCursor 
FETCH NEXT FROM gandgCursor  
INTO @tableName 
WHILE ( @@FETCH_STATUS = 0 ) 
BEGIN 
SET @sql = ' 
ALTER TABLE [dbo].['+ @tableName + '] 
ALTER COLUMN GlobalID ADD ROWGUIDCOL ;
' 
 PRINT 'Executing Statement - '+ @sql 
 EXECUTE ( @sql ) 
 FETCH NEXT FROM gandgCursor  
 INTO  @tableName 
 END 
 CLOSE gandgCursor 
 DEALLOCATE gandgCursor

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]