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

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,
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)

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
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.

• +10, I really like your solution. I haven't observed this solution before :-)...or am I wrong? Nov 10, 2021 at 18:03
• The solution is really just some basic math, so no doubt it's been done before :) Nov 10, 2021 at 18:04
• Everything brilliant is simple! Nov 10, 2021 at 18:06
• I realized that ST_MakeLine has an aggregate version, so the ARRAY_AGG can be removed. Fixed SQL. Nov 10, 2021 at 18:41
• All unnecessary things disappear with time, only the main things remain... Nov 10, 2021 at 19:02

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,
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,
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,
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,
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
``````
1. For professional developers, the SQL code given in the answer Martin Davis (D-r JTS) is more than sufficient :-)!

2. 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)

• Great graphics! Apr 20, 2022 at 21:50
• @dr_jts, Thanks, so I didn't waste my time creating it for nothing :-) ... so a simple SQL-code was not invented by you for nothing :-)... Apr 21, 2022 at 13:03