I have the following trigger on both update and insert for a table called blocks which stores polygons:
AFTER INSERT AS BEGIN
SET NOCOUNT ON
DECLARE int, @ranch_num int, @block_treatment nvarchar(256), @shp geography, @copied_from int, @gdb_from_date datetime2(7)
SELECT * INTO #Temp
FROM inserted
WHILE EXISTS (SELECT * FROM #TEMP) BEGIN
SELECT TOP 1 = OBJECTID, @ranch_num = TRANSACTIONAL_RANCH_NUMBER, @block_treatment = TREATMENT_TYPE, @shp = SHAPE, @copied_from = CAST(COPIED_FROM as int), @gdb_from_date = GDB_FROM_DATE
FROM #Temp
IF @ranch_num IS NOT NULL
BEGIN
IF @block_treatment = 'ORIGINAL'
BEGIN
UPDATE a
SET a.ORIGIN_BLOCK = a.OBJECTID
FROM BLOCKS a
WHERE a.OBJECTID =
END
ELSE
BEGIN
IF (SELECT TOP(1) a.OBJECTID
FROM DEV_ENV.aws.BLOCKS a LEFT JOIN DEV_ENV.aws.TRANSACTIONAL_RANCH_NUMBERS t on a.TRANSACTIONAL_RANCH_NUMBER = t.TRANSACTIONAL_RANCH_NUMBER
WHERE a.SHAPE.STIntersects(
geography::STGeomFromText(
geometry::Point(
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
4326
).STAsText()
, 4326)
)=1 AND a.TREATMENT_TYPE = 'ORIGINAL' AND t.MEDIA = 1 AND a.TRANSACTIONAL_RANCH_NUMBER = @ranch_num
AND a.GDB_TO_DATE = '9999-12-31 23:59:59.0000000' AND a.ORIGIN_BLOCK IS NOT NULL
ORDER BY a.GDB_FROM_DATE desc
) IS NOT NULL
BEGIN
UPDATE BLOCKS
SET
ORIGIN_BLOCK = (
SELECT TOP(1) a.OBJECTID
FROM DEV_ENV.aws.BLOCKS a LEFT JOIN DEV_ENV.aws.TRANSACTIONAL_RANCH_NUMBERS t on a.TRANSACTIONAL_RANCH_NUMBER = t.TRANSACTIONAL_RANCH_NUMBER
WHERE a.SHAPE.STIntersects(
geography::STGeomFromText(
geometry::Point(
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
4326
).STAsText()
, 4326)
)=1 AND a.TREATMENT_TYPE = 'ORIGINAL' AND t.MEDIA = 1 AND a.TRANSACTIONAL_RANCH_NUMBER = @ranch_num
AND a.GDB_TO_DATE = '9999-12-31 23:59:59.0000000' AND a.ORIGIN_BLOCK IS NOT NULL
ORDER BY a.GDB_FROM_DATE desc
)
WHERE OBJECTID =
END
END
END
ELSE
BEGIN
UPDATE a
SET a.ORIGIN_BLOCK = null
FROM BLOCKS a
WHERE a.OBJECTID =
END
DELETE FROM #Temp WHERE OBJECTID =
END
END
Essentially, what I am trying to do is if a block that is being inserted/updated has a non-original treatment type, I need to find the most recent ORIGINAL block that contains the centroid of the block being inserted/updated.
To do this in a trigger, I am first creating a Point geometry to find the centroid, and then converting that geometry to a geography which is used as input for SHAPE.STIntersects. However the inputs to SHAPE.STIntersects is causing an issue when I run geoprocessing scripts that will either call UpdateCursor or InsertCursor on the blocks table. The error I get is:
RuntimeError: workspace already in transaction mode
If I were to change the input of SHAPE.STIntersects:
WHERE a.SHAPE.STIntersects(
geography::STGeomFromText(
geometry::Point(
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STX,
geometry::STGeomFromWKB(@shp.STAsBinary(), 4326 ).STCentroid().STY,
4326
).STAsText()
, 4326)
)=1
to use the EnvelopCenter of the shape instead of a Point geometry of the centroid:
WHERE a.SHAPE.STIntersects(@shp.EnvelopeCenter())=1
I will no longer get the RuntimeError, however it isn't as accurate as using the centroid.
I am not so familiar working with shapes in SQL, any ideas what could be wrong with using the centroid Point geometry?
