How to create a classic sand snail using PostgreSQL/PostGIS?

Question

I would like to be able to create a classic sand snail shape using a query or SQL function, similar that shown in the picture below but with smoother curves.

The direction of unwinding/twisting of the spiral is not important.

The input data for its creation may be:

1. Circle center,
2. Circle radius,
3. Number of spirals.

I know at least 2 ways to solve this problem.

The 1st way is azimuthal (see picture), the 2nd way - with a displacement of segments on the diameter of the circle by a specified step, followed by a transformation of segments using the function ST_CurveToLine().

For now, an experimental preliminary solution of method 1 might look like the one presented in the query, but it needs refinement.

WITH
    tbla AS (SELECT (ST_Dump(ST_MakeValid((SELECT ST_MakePolygon(ST_ExteriorRing(ST_Buffer(center, 0.0001)),
      ARRAY[ST_ExteriorRing(ST_Buffer(center, 0.03))]))))).geom FROM ST_SetSrid(ST_MakePoint(33.0, 33.0), 4326) AS center),
    tblb AS (WITH btbl AS (SELECT ST_ExteriorRing(geom) geom FROM tbla)
              SELECT i, ST_LineInterpolatePoint(geom, (i-1.0)/8) geom FROM btbl JOIN generate_series (1, 8) AS step(i) ON true),
    tblc AS (SELECT DISTINCT i ray, ST_MakeLine(a.geom, ST_Centroid(b.geom)) AS geom FROM tblb a CROSS JOIN tbla b),
    tbld AS (SELECT ray, (ST_Dump(ST_Intersection(a.geom, b.geom))).geom AS geom FROM tblc a JOIN tbla b ON ST_Intersects(a.geom, b.geom)),
    tble AS (SELECT i ray, ST_LineInterpolatePoint(geom, (i-1.0)/12) geom FROM tbld JOIN generate_series (1, 12) AS step(i) ON true)
          (SELECT ST_MakeLine(geom ORDER BY ray) AS geom FROM tble);

How can I make smoother sand spirals?

The function could be named ST_ClassicSandSnail() or otherwise.

Translated with www.DeepL.com/Translator (free version)

Answer 1

Here's some simple SQL for a spiral:

WITH spiralStep AS (
   SELECT i, 
          80 AS circleSegs,   -- Parameter: quantization of arc
          1000 AS centerX,    -- Parameter: center X
          1000 as centerY,    -- Parameter: center Y
          100.0 as radius     -- Parameter: radius
  FROM generate_series(0, 5 * 80) t(i) -- Parameter: # rings = 5
)
SELECT ST_MakeLine( 
    ST_Point(centerX + (radius / circleSegs) * i * cos(i * (2 * pi() / circleSegs)),
             centerY + (radius / circleSegs) * i * sin(i * (2 * pi() / circleSegs)))
ORDER BY i) AS geom
FROM spiralStep;

The output is a LineString that looks like this:

Note: the image is generated using pg-svg; see demo script.

Answer 2

So, Martin Davis (D-r JTS), proposed in my opinion a perfect mathematical-geometric solution to the problem.

As a result, which proposed in the answer solution allows to create many series of sinusoidal curved lines and opens the way to the "Kingdom of curved lines"!

I think Archimedes of Syracuse would have been very pleased with this solution, as it extends the limits of creating not only spirals in particular, but sinusoidal curves, in general!!!

As a result,

1. For spatial SQL users, I publish 4 basic behaviors for spatial functions developed from the Martin Davis (D-r JTS) geo-tool!

CREATE OR REPLACE FUNCTION ST_ArchimedianSpiralСlockwiseEast(
centerX double precision,
centerY double precision,
radius float,
circleSegs integer,
rings integer)
RETURNS GEOMETRY AS
$BODY$
WITH 
    spiralStep AS (SELECT i FROM generate_series(0, rings * circleSegs) t(i))
    (SELECT ST_MakeLine(ST_SetSrid(ST_MakePoint(centerX + (radius/circleSegs) * i * cos(i * (2 * pi()/circleSegs)),
              centerY + (radius/circleSegs) * i * -sin(i * (2 * pi()/circleSegs))),4326)) AS geom FROM spiralStep);
$BODY$
LANGUAGE SQL;

RUN

SELECT ST_ArchimedianSpiralСlockwiseEast(15.326975287, 37.007075212, 0.003, 75, 3) geom

CREATE OR REPLACE FUNCTION ST_ArchimedianSpiralСlockwiseWest(
centerX double precision,
centerY double precision,
radius float,
circleSegs integer,
rings integer)
RETURNS GEOMETRY AS
$BODY$
WITH 
    spiralStep AS (SELECT i FROM generate_series(0, rings * circleSegs) t(i))
    (SELECT ST_MakeLine(ST_SetSrid(ST_MakePoint(centerX + (radius/circleSegs) * i * -cos(i * (2 * pi()/circleSegs)),
              centerY + (radius/circleSegs) * i * sin(i * (2 * pi()/circleSegs))),4326)) AS geom FROM spiralStep);
$BODY$
LANGUAGE SQL;

RUN

SELECT ST_ArchimedianSpiralСlockwiseWest(15.326975287, 37.007075212, 0.003, 75, 3) geom

CREATE OR REPLACE FUNCTION ST_ArchimedianSpiralCounterClockwiseEast(
centerX double precision,
centerY double precision,
radius float,
circleSegs integer,
rings integer)
RETURNS GEOMETRY AS
$BODY$
WITH 
    spiralStep AS (SELECT i FROM generate_series(0, rings * circleSegs) t(i))
    (SELECT ST_MakeLine(ST_SetSrid(ST_MakePoint(centerX + (radius/circleSegs) * i * cos(i * (2 * pi()/circleSegs)),
              centerY + (radius/circleSegs) * i * sin(i * (2 * pi()/circleSegs))),4326)) AS geom FROM spiralStep);
$BODY$
LANGUAGE SQL;

RUN

SELECT ST_ArchimedianSpiralCounterClockwiseEast(15.326975287, 37.007075212, 0.003, 75, 3) geom

CREATE OR REPLACE FUNCTION ST_ArchimedianSpiralCounterClockwiseWest(
centerX double precision,
centerY double precision,
radius float,
circleSegs integer,
rings integer)
RETURNS GEOMETRY AS
$BODY$
WITH 
    spiralStep AS (SELECT i FROM generate_series(0, rings * circleSegs) t(i))
    (SELECT ST_MakeLine(ST_SetSrid(ST_MakePoint(centerX + (radius/circleSegs) * i * -cos(i * (2 * pi()/circleSegs)),
              centerY + (radius/circleSegs) * i * -sin(i * (2 * pi()/circleSegs))),4326)) AS geom FROM spiralStep);
$BODY$
LANGUAGE SQL;

RUN

SELECT ST_ArchimedianSpiralCounterClockwiseWest(15.326975287, 37.007075212, 0.003, 75, 3) geom

2. For professional developers, the SQL code given in the answer Martin Davis (D-r JTS) is more than sufficient :-)!

3. For professional mappers, I publish simple geo-images made based on given SQL spatial functions in PostgreSQL/PostGIS software environment named: "Archimedian Spiral" and stylized in QGIS software environment in two views: a) day and b) night.

Original spatial solutions...

Translated with www.DeepL.com/Translator (free version)