Create a classic sand snail using PostgreSQL/PostGIS

postgispostgresqlsqltrue-curves

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.

enter image description here

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)

Best Answer

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:

enter image description here

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

Related Question