maplion
10/14/2015 - 8:35 PM
This procedure finds all the points in a given Polygon and disperses them in a pattern similar to the esri tool arcpy.DisperseMarkers_Manage
This procedure finds all the points in a given Polygon and disperses them in a pattern similar to the esri tool arcpy.DisperseMarkers_Management with "SQUARES" option.
/*
This procedure finds all the points in a given Polygon and disperses them in a pattern similar to the
esri tool arcpy.DisperseMarkers_Management with "SQUARES" option.
Author: @MapLion for @Simplot
*/
CREATE Procedure Disperse_Points
@Shape GEOMETRY,
@Points_Table_Name nvarchar(255),
@Points_Schema_Name nvarchar(100) = '',
@Points_Database_Name nvarchar(100) = '',
@Dispersion_Type nvarchar(15) = 'SQUARES'
as
BEGIN
SET NOCOUNT ON;
/* Drop existing Temporary Objects */
IF OBJECT_ID('tempdb..#Points_Table') IS NOT NULL DROP TABLE #Points_Table
IF OBJECT_ID('tempdb..#Shape_Table') IS NOT NULL DROP TABLE #Shape_Table
/* Set @Points_Table_Name */
IF @Points_Database_Name <> ''
SET @Points_Database_Name = CONCAT(@Points_Database_Name, '.')
IF @Points_Schema_Name <> ''
SET @Points_Schema_Name = CONCAT(@Points_Schema_Name, '.')
SET @Points_Table_Name = CONCAT(@Points_Database_Name, @Points_Schema_Name, @Points_Table_Name)
--select @Points_Table_Name
/* Declare Dynamic SQL Variables */
DECLARE @SQL nvarchar(MAX)
DECLARE @DEBUG INT = 0 -- SET to 1 to print out all Dynamic SQL Statements or wrap specific statements with SET @DEBUG = 1 and SET @DEBUG = 0
/* POINT DISPERSION */
-----------------------------------------------------------------------------------------
/* Get a list of all points within Shape along with their X and Y Coordinates */
Create Table #Points_Table (
OBJECTID INT
, Shape GEOMETRY
, X_Coord FLOAT
, Y_Coord FLOAT
)
/* This table is created to hold a single geometry @Shape for the purpose of being able to use it in dynamic SQL */
Create Table #Shape_Table (
Shape GEOMETRY
)
/* Populate @Shape into #Shape_Table */
insert into #Shape_Table
select @Shape
/* Populate Temporary Points Table */
SET @SQL =
'insert into #Points_Table
select
OBJECTID
, Shape
, Shape.STX X_Coord
, Shape.STY Y_Coord
from ' + @Points_Table_Name + ' p
where (select TOP 1 Shape from #Shape_Table).STContains(p.Shape) = 1'
IF @DEBUG = 1 PRINT '1.' + CHAR(13) + @SQL + CHAR(13)
EXECUTE sp_executesql @SQL WITH RESULT SETS NONE
/* Get Centroid X and Y */
DECLARE @CENTROID_X FLOAT
DECLARE @CENTROID_Y FLOAT
select @CENTROID_X = @Shape.STCentroid().STX
select @CENTROID_Y = @Shape.STCentroid().STY
/* Get Count of Points */
DECLARE @POINT_COUNT INT
select @POINT_COUNT = count(*) from #Points_Table
/* Set Base Square Value */
DECLARE @BASE_SQUARE_VALUE FLOAT = 0.0001325 /* This is an arbitrary measure based on evaluations of esri's DisperseMarkers_Management results */
/* Reset Points to Centroid */
update #Points_Table
set X_Coord = @CENTROID_X, Y_Coord = @CENTROID_Y
/* Change Coordinate Values */
BEGIN TRY
BEGIN TRAN
DECLARE @OBJECTID INT
DECLARE @MOVED_POINT GEOMETRY
DECLARE @COUNT INT = 0
/* SQUARES DISPERSION */
--------------------------------------------------
IF @Dispersion_Type = 'SQUARES'
BEGIN
IF @POINT_COUNT <= 4
BEGIN
select @OBJECTID = min(OBJECTID) from #Points_Table
WHILE @OBJECTID IS NOT NULL
BEGIN
SET @COUNT = @COUNT + 1
IF @COUNT = 1
BEGIN
update #Points_Table
set X_Coord = X_Coord - @BASE_SQUARE_VALUE, Y_Coord = Y_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 2
BEGIN
update #Points_Table
set X_Coord = X_Coord + @BASE_SQUARE_VALUE, Y_Coord = Y_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 3
BEGIN
update #Points_Table
set X_Coord = X_Coord + @BASE_SQUARE_VALUE, Y_Coord = Y_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 4
BEGIN
update #Points_Table
set X_Coord = X_Coord - @BASE_SQUARE_VALUE, Y_Coord = Y_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
/* Convert New X Y to a shape */
select @MOVED_POINT = geometry::Point(X_Coord, Y_Coord, 4326)
from #Points_Table
where OBJECTID = @OBJECTID
/* Update table with new shape */
update #Points_Table
set Shape = @MOVED_POINT
where OBJECTID = @OBJECTID
/* Grab next OBJECTID */
select @OBJECTID = min(OBJECTID) from #Points_Table where OBJECTID > @OBJECTID
END /* END WHILE @OBJECTID IS NOT NULL */
END /* END IF @POINT_COUNT <= 4 */
ELSE /* More than 4 labels */
BEGIN
select @OBJECTID = min(OBJECTID) from #Points_Table
WHILE @OBJECTID IS NOT NULL
BEGIN
IF (@COUNT % 8) = 0
BEGIN
SET @BASE_SQUARE_VALUE = @BASE_SQUARE_VALUE * 2
SET @COUNT = 0
END
SET @COUNT = @COUNT + 1
IF @COUNT = 1
BEGIN
update #Points_Table
set X_Coord = X_Coord - @BASE_SQUARE_VALUE, Y_Coord = Y_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 2
BEGIN
update #Points_Table
set Y_Coord = Y_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 3
BEGIN
update #Points_Table
set X_Coord = X_Coord + @BASE_SQUARE_VALUE, Y_Coord = Y_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 4
BEGIN
update #Points_Table
set X_Coord = X_Coord + @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 5
BEGIN
update #Points_Table
set X_Coord = X_Coord + @BASE_SQUARE_VALUE, Y_Coord = Y_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 6
BEGIN
update #Points_Table
set Y_Coord = Y_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 7
BEGIN
update #Points_Table
set X_Coord = X_Coord - @BASE_SQUARE_VALUE, Y_Coord = Y_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
ELSE IF @COUNT = 8
BEGIN
update #Points_Table
set X_Coord = X_Coord - @BASE_SQUARE_VALUE
where OBJECTID = @OBJECTID
END
/* Convert New X Y to a shape */
select @MOVED_POINT = geometry::Point(X_Coord, Y_Coord, 4326)
from #Points_Table
where OBJECTID = @OBJECTID
/* Update table with new shape */
update #Points_Table
set Shape = @MOVED_POINT
where OBJECTID = @OBJECTID
/* Grab next OBJECTID */
select @OBJECTID = min(OBJECTID) from #Points_Table where OBJECTID > @OBJECTID
END /* END WHILE @OBJECTID IS NOT NULL */
END /* End ELSE */
END /* END IF @Dispersion_Type = 'SQUARES' */
--------------------------------------------------
/* END SQUARES DISPERSION */
/* Boundary Check */
IF (select COUNT(*) CNT from (select * from #Points_Table where @Shape.STContains(Shape) = 0) a) > 0
BEGIN
;THROW 50005, 'Markers are outside of the range of the Polygon; there are too many points for the current implementation with the given shape.', 1;
END
--select * from #Points_Table
--select geometry::Point(X_Coord, Y_Coord, 4326) NewShape from #Points_Table
/* Update Original Points Table with dispersed points */
SET @SQL =
'update i
set i.Shape = p.Shape
from #Points_Table p
inner join ' + @Points_Table_Name + ' i
on i.OBJECTID = p.OBJECTID'
IF @DEBUG = 1 PRINT '1.' + CHAR(13) + @SQL + CHAR(13)
EXECUTE sp_executesql @SQL WITH RESULT SETS NONE
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRAN;
DECLARE @ERROR_SEVERITY INT = ERROR_SEVERITY()
DECLARE @ERROR_STATE INT = ERROR_STATE()
DECLARE @ERROR_MESSAGE_CONCAT NVARCHAR(MAX) = CONCAT(
char(13),'Error Procedure: ', ERROR_PROCEDURE(),
char(13),'Error Number: ',ERROR_NUMBER(),
char(13),'Error Line: ', ERROR_LINE(),
char(13),'Error Message: ', ERROR_MESSAGE())
RAISERROR(@ERROR_MESSAGE_CONCAT, @ERROR_SEVERITY, @ERROR_STATE)
END CATCH;
-----------------------------------------------------------------------------------------
/* END POINT DISPERSION */
END