Generating Shape Bound Random Points in SQL Server

Problem

Random number generation is vital in computer science, supporting fields like optimization, simulation, robotics, and gaming. The quality, speed, and sometimes security of the generator can directly affect an algorithm’s correctness, performance, and competitiveness. In Python, random number generation is well-supported and widely used. In this article, we will look how to we can use SQL to do this.

Solution

Following are several methods that can be used to generate random numbers.

Permuted Congruential Generator (PCG) Function

The PCG takes a simple and fast random Congruential base generator and combine with Permuted logic. The Permuted logic is used to the scramble the numbers. A permutation math trick is used to make the numbers look more random or evenly spread out. It is hard to guess the next number compared to other generators, but it is still not secure enough for serious cryptography.

Algorithms are used to produce a stream of numbers to match statistical expectations and I choose to work with PCG64.

Let´s create a function to generate the PCG 64 bits that will generate random number between @low and @high values. The @int1 and @int2 are random generated numbers that needs to be supplied outside the function.

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20250813
-- Description: Permuted congruential generator
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnRndGenPCG64] 
                (@int1 bigint
                ,@int2 bigint
                ,@low float
                ,@high float)
RETURNS float
AS
BEGIN
    DECLARE @low32  bigint = (@int1 + 2147483648);
    DECLARE @high32 bigint = (@int2 + 2147483648);
 
    DECLARE @u float = CONVERT(float, @high32 * 4294967296 + @low32) / 18446744073709551616.0;
 
    DECLARE @result float = @low + (@high - @low) * @u;
 
    RETURN @result;
END
GO

Box-Muller Transformation Function

The Box-Muller Transformation function generates pairs of independent normally standard distributed random numbers. You can find more information at WIKIPEDIA – Box-Muller transform.

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20250813
-- Description: Box-Muller transformation
-- =============================================
CREATE OR ALTER FUNCTION [dbo].[ufnRndGenBoxMuller] 
                (@u1 float
                ,@u2 float
                ,@mean float
                ,@std float)
RETURNS float
AS
BEGIN
 
    DECLARE @z0 float = SQRT(-2 * LOG(@U1)) * COS(2 * PI() * @U2);
 
    DECLARE @result float =  @mean + @std * @z0;
 
    RETURN @result;
END
GO

Spiral Shape

Spiral is a curve which emanates from a point, moving farther away as it revolves around the point, where formula, yformula, and formula

The variable a is the initial starting radius, and the variable b is the radius grow rate, so the spiral starts at the origin when r = 0 and grows linearly with theta, and a larger b makes the spiral expand faster. When b = 0 the spiral degenerates to a circle.

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20250815
-- Description: Spiral shaped-bounded random 
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspRandGenShapeSpiral] 
                (@points  int
                ,@a float
                ,@b float
                ,@l float
                ,@cx float
                ,@cy float
                ,@fx bit
                ,@fy bit
                ,@j float
                ,@r float)
WITH EXECUTE AS CALLER 
AS
BEGIN;
 
    /*
 
        @a        initial radius
        @b        radius increment per angle
        @l        number of full loops
        @cx        x axis offset
        @cy        y axis offset
        @fx        x flip direction where 1 means left
        @fy        y flip direction where 1 means vertical inversion
        @j        random radius jitter
 
        @r        spatial point ratio
 
    */
 
    DECLARE      @Values
        TABLE   (id int 
                ,r float
                ,theta float
                ,x float
                ,y float);
 
    DECLARE @i int = 1;
 
    WHILE @i <= @points BEGIN
        DECLARE @theta float = @l * 2 * PI() * (CONVERT(float, @i) / (@points - 1));
 
        INSERT INTO @Values
                        (id
                        ,r
                        ,theta)
            SELECT         @i
                        ,@a + @b * @theta + 
                         (([dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),0,1) - 0.5) * @j) AS r
                        ,@theta AS theta;
 
        SET @i += 1;
    END
 
    UPDATE         @Values
        SET         x = CASE WHEN @fx = 1 
                          THEN -1 
                          ELSE 1 
                     END * (@a + @b * theta + r) * COS(theta) + @cx
                ,y = CASE WHEN @fy = 1 
                          THEN -1 
                          ELSE 1 
                     END * (@a + @b * theta + r) * SIN(theta) + @cy;
 
    SELECT         id
                ,x
                ,y
                ,([geometry]::Point([x],[y],(0))).STBuffer(@r)
        FROM     @Values
 
    RETURN;
END
GO

Example – Play with the numbers below to see its effects. To visualize the points, it is necessary to adjust the geometry feature @r which is the radius of each point to be plotted at the Spatial results tab.

-- MSSQLTips (TSQL)
 
DECLARE  @points int = 1000
        ,@a float = 0.7
        ,@b float = 0.2
        ,@l float = 2
        ,@cx float = 0
        ,@cy float = 0
        ,@fx bit = 0
        ,@fy bit = 0
        ,@j float = .5
        ,@r float = 0.08;
 
EXECUTE [dbo].[uspRandGenShapeSpiral] @points,@a,@b,@l,@cx,@cy,@fx,@fy,@j,@r;
GO

Resulting in:

Spiral

Polygonal Shape

First, we need to create a table to hold the polygon vertices. It is very important that the start vertices must be the same as the end vertices to be considered a closed polygonal. See more details at MICROSOFT – Polygon.

CREATE TABLE [dbo].[Polygon](
    [vertice] [int] IDENTITY(1,1) NOT NULL,
    [x] [float] NULL,
    [y] [float] NULL,
 CONSTRAINT [PK_Polygon] PRIMARY KEY CLUSTERED 
(
    [vertice] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

This is the store procedure to generate the random values inside the polygon.

-- =============================================
-- Author:      SCP - MSSQLTips
-- Create date: 20250818
-- Description: Polygon shaped-bounded random
-- =============================================
CREATE OR ALTER PROCEDURE [dbo].[uspRandGenShapePolygon] 
                (@Points int
                ,@r float)
WITH EXECUTE AS CALLER 
AS
BEGIN;
    SET NOCOUNT ON;
 
    IF    (SELECT COUNT(*) FROM [dbo].[Polygon]) < 3 AND
        (SELECT CASE 
            WHEN (SELECT TOP 1 x FROM [dbo].[Polygon] ORDER BY [vertice] ASC) = 
                 (SELECT TOP 1 x FROM [dbo].[Polygon] ORDER BY [vertice] DESC) AND
                 (SELECT TOP 1 y FROM [dbo].[Polygon] ORDER BY [vertice] ASC) = 
                 (SELECT TOP 1 y FROM [dbo].[Polygon] ORDER BY [vertice] DESC)
            THEN 1
            ELSE 0
         END AS IsClosed) = 0 BEGIN
        PRINT ('Polygon must be closed!')
        RETURN;
    END;
 
    BEGIN TRY
 
        DECLARE      @Values 
            TABLE   (id int 
                    ,x float
                    ,y float);
 
        DECLARE         @poly geometry
                    ,@x float
                    ,@xMax float
                    ,@yMax float
                    ,@y float
                    ,@xMin float
                    ,@yMin float
                    ,@w nvarchar(MAX)
                    ,@p int = 1;
 
        SELECT @w = 'POLYGON((' + STRING_AGG(CAST(x AS nvarchar(10)) + ' ' + CAST(y AS nvarchar(10)), ', ') + '))'
            FROM [dbo].[Polygon];
 
        SET @poly = geometry::STGeomFromText(@w, 0);
 
        SELECT       @xMax = MAX(x)
                    ,@xMin = MIN(x)
                    ,@yMax = MAX(y)
                    ,@yMin = MIN(y)
            FROM     [dbo].[Polygon];
 
        WHILE @p <= @Points BEGIN
            SET @x = [dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@xMin,@xMax);
            SET @y = [dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@yMin,@yMax);
 
            IF @poly.STContains((geometry::Point(@x, @y, 0))) = 1 BEGIN
                INSERT INTO  @Values
                    VALUES  (@p
                            ,@x
                            ,@y);
 
                SET @p += 1;
            END
        END
 
        SELECT         'Polygon' AS Id
                    ,NULL AS x
                    ,NULL AS y
                    ,@poly.STBoundary() AS geom
 
            UNION ALL
 
        SELECT         CONVERT(nvarchar(10),id)
                    ,x
                    ,y
                    ,([geometry]::Point([x],[y],(0))).STBuffer(@r)
            FROM     @Values;
 
        RETURN;
    END TRY
    BEGIN CATCH
        PRINT    'Error: ' + CONVERT(varchar(50), ERROR_NUMBER()) +
                ', Severity: ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
                ', State: ' + CONVERT(varchar(5), ERROR_STATE()) + 
                ', Procedure: ' + ISNULL(ERROR_PROCEDURE(), '-') + 
                ', Line: ' + CONVERT(varchar(5), ERROR_LINE());
        PRINT ERROR_MESSAGE();
    END CATCH;
END
GO

TRIANGLE

-- MSSQLTips (TSQL)
 
TRUNCATE TABLE [dbo].[Polygon];
 
INSERT INTO  [dbo].[Polygon]
    VALUES  (1,1),(4,3),(8,0),(1,1);
 
EXEC [dbo].[uspRandGenShapePolygon] 1000,0.03;
GO

Resulting in

Triangle

RECTANGLE / SQUARE

-- MSSQLTips (TSQL)
 
TRUNCATE TABLE [dbo].[Polygon];
 
INSERT INTO  [dbo].[Polygon]
    VALUES  (0,0),(0,3),(3,3),(3,0),(0,0);
 
EXEC [dbo].[uspRandGenShapePolygon] 1000,0.03;
GO

Resulting in:

Square

POLYGON

-- MSSQLTips (TSQL)
 
TRUNCATE TABLE [dbo].[Polygon];
 
INSERT INTO  [dbo].[Polygon]
    VALUES  (0,1),(2,0.5),(3,1),(2,2.5),(0.5,2),(0,1);
 
EXEC [dbo].[uspRandGenShapePolygon] 1000,0.03;
GO

Resulting in:

Polygon

Combining Two or More Polygons

To change the location of a polygon you can change its offset and scale.

 DECLARE @Values 
    TABLE    ([Id] nvarchar(20)
            ,[x] float
            ,[y] float 
            ,[geom] geometry
            ,[src] smallint);
 
TRUNCATE TABLE [dbo].[Polygon];
INSERT INTO  [dbo].[Polygon]
    VALUES  (0,1),(2,0.5),(3,1),(2,2.5),(0.5,2),(0,1);
 
INSERT INTO @Values
            (Id,x,y,geom)
    EXEC [dbo].[uspRandGenShapePolygon] 1000,0.03;
 
-- changing the offset to the left
UPDATE @Values
    SET         src = 1
            ,x = -2 + x
            ,y = -2 + y
    WHERE     src IS NULL;
 
TRUNCATE TABLE [dbo].[Polygon];
INSERT INTO  [dbo].[Polygon]
    VALUES  (1,1),(4,3),(8,0),(1,1);
 
INSERT INTO @Values
            (Id,x,y,geom)
    EXEC [dbo].[uspRandGenShapePolygon] 1000,0.03;
 
DELETE FROM @Values WHERE x IS NULL;
 
UPDATE @Values
    SET         geom = ([geometry]::Point([x],[y],(0))).STBuffer(0.05);
 
SELECT * FROM @Values;
GO

Resulting in:

Combined

Happy Face Shape

-- MSSQLTips (TSQL)
 
SET NOCOUNT ON;
 
DECLARE      @face int = 500
            ,@eye int = 50
            ,@eye_radius float = 0.08
            ,@smile int = 200
            ,@smile_radius float = 0.6
            ,@smile_start float = PI() / 6      -- 30 degrees
            ,@smile_end float = 5 * PI() / 6    --150 degrees
            ,@theta float
            ,@r float
            ,@mean float = 0.0
            ,@std float = 0.02
            ,@low float = 0
            ,@high float = 2 * PI()
            ,@i int = 1;
        
DECLARE      @Values
    TABLE   (id int IDENTITY
            ,r float
            ,theta float
            ,src smallint
            ,x float
            ,y float);
 
-- face =========================================
 
WHILE @i <= @face BEGIN
    INSERT INTO  @Values
                (r
                ,theta
                ,src)
        VALUES  (1 + [dbo].[ufnRndGenBoxMuller] (RAND(CHECKSUM(NEWID())),RAND(CHECKSUM(NEWID())),@mean,@std)
                ,[dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@low,@high)
                ,0);
 
    SET @i += 1;
END
 
 
UPDATE       @Values    
    SET      x = r * COS(theta)
            ,y = r * SIN(theta)
    WHERE    src = 0;
 
-- left eye =====================================
 
SET @i = 1;
 
WHILE @i <= @eye BEGIN
    INSERT INTO @Values
                (r
                ,theta
                ,src)
        VALUES  ([dbo].[ufnRndGenBoxMuller] (RAND(CHECKSUM(NEWID())),RAND(CHECKSUM(NEWID())),@mean,@std / 4)
                ,[dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@low,@high)
                ,1);
 
    SET @i += 1;
END
 
UPDATE       @Values    
    SET      x = r - 0.35 + @eye_radius * COS(theta)
            ,y = r + 0.35 + @eye_radius * SIN(theta)
    WHERE    src = 1;
 
-- right eye ====================================
 
SET @i = 1;
 
WHILE @i <= @eye BEGIN
    INSERT INTO @Values
                (r
                ,theta
                ,src)
        VALUES  ([dbo].[ufnRndGenBoxMuller] (RAND(CHECKSUM(NEWID())),RAND(CHECKSUM(NEWID())),@mean,@std / 4)
                ,[dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@low,@high)
                ,2);
 
    SET @i += 1;
END
 
UPDATE       @Values    
    SET      x = r + 0.35 + @eye_radius * COS(theta)
            ,y = r + 0.35 + @eye_radius * SIN(theta)
    WHERE    src = 2;
 
-- smile ========================================
 
SET @i = 1;
 
WHILE @i <= @smile BEGIN
    INSERT INTO @Values
                (r
                ,theta
                ,src)
        VALUES  ([dbo].[ufnRndGenBoxMuller] (RAND(CHECKSUM(NEWID())),RAND(CHECKSUM(NEWID())),@mean,@std)
                ,[dbo].[ufnRndGenPCG64] (CHECKSUM(NEWID()),CHECKSUM(NEWID()),@smile_start,@smile_end)
                --,@smile_start + (CAST(@i - 1 AS FLOAT) / (@smile - 1)) * (@smile_end - @smile_start) 
                ,3);
 
    SET @i += 1;
END
 
UPDATE       @Values    
    SET      x = -(r + @smile_radius * COS(theta))
            ,y = -(r + 0.15 + 0.3 * SQUARE(SIN(theta)))
    WHERE    src = 3;
 
SELECT   x
        ,y
        ,([geometry]::Point([x],[y],(0))).STBuffer(0.03) AS geom
    FROM @Values;
GO

Resulting in:

Happy face

For a sad face just remove the negative signal of x and y for src = 3.

Observe the fact that working with the values r and theta you can obtain the format that you wish when applying to x and y values.

Next Steps

​

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *