1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| USE tempdb;
GO
CREATE OR ALTER PROCEDURE F_POLYSMART
@MAXPNT SMALLINT,
@MAXX FLOAT,
@MAXY FLOAT
AS
SET NOCOUNT ON;
DECLARE @I SMALLINT = 1, @GEO GEOMETRY;
IF @MAXPNT < 3
BEGIN
RAISERROR ('Un polygone doit être définit par un minimum de trois points', 16, 1);
RETURN;
END;
CREATE TABLE #PNT (ID SMALLINT IDENTITY PRIMARY KEY, POINT VARCHAR(128), DONE BIT DEFAULT 0, NUM SMALLINT, D FLOAT)
WHILE @I <= @MAXPNT
BEGIN
INSERT INTO #PNT (POINT) SELECT + CAST(@MAXX * (CHECKSUM(NEWID()) / 2147483647.0) AS varchar(max)) + ' ' +
CAST(@MAXX * (CHECKSUM(NEWID()) / 2147483647.0) AS varchar(max)) ;
SET @I = @I + 1;
END;
WITH
T_PLG AS
(SELECT CAST('POLYGON((' + POINT AS varchar(max)) AS GEO, ID, CAST(@MAXPNT AS bigint) AS N
FROM #PNT
WHERE ID = 1
UNION ALL
SELECT GEO + ', ' + POINT, #PNT.ID, N - 1
FROM T_PLG
JOIN #PNT
ON T_PLG.ID + 1 = #PNT.ID)
SELECT @GEO = CAST(GEO + ', ' + (SELECT POINT FROM #PNT WHERE ID = 1) + '))' AS geometry).MakeValid()
FROM T_PLG
WHERE N = 1;
SELECT @GEO AS GEO
SELECT @GEO.STConvexHull() AS GEOHULL
--> liaison avec les points les plus proches :
DECLARE @GEOTEXT VARCHAR(max), @ID SMALLINT;
SELECT @GEOTEXT = 'POLYGON((' + POINT, @I = 1 FROM #PNT WHERE ID = 1;
UPDATE #PNT SET DONE = 1, NUM = 1 WHERE ID = 1;
WHILE EXISTS(SELECT * FROM #PNT WHERE DONE = 0)
BEGIN
UPDATE T
SET D = CAST('POINT(' + T0.POINT +')' AS GEOMETRY).STDistance(CAST('POINT(' + T.POINT +')' AS GEOMETRY))
FROM #PNT AS T
CROSS JOIN #PNT AS T0
WHERE T.DONE = 0
AND T0.NUM = @I;
SELECT TOP 1 @ID = ID FROM #PNT WHERE D = (SELECT MIN(D) FROM #PNT WHERE DONE = 0);
SELECT @GEOTEXT = @GEOTEXT + ', ' + POINT FROM #PNT WHERE ID = @ID;
UPDATE #PNT SET DONE = 1, NUM = @I + 1 WHERE ID = @ID;
SET @I = @I + 1;
END;
SELECT CAST(@GEOTEXT + ', ' + POINT + '))' AS geometry)
FROM #PNT
WHERE ID = 1;
GO |
Partager