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.

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)

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:

enter image description here

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

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

Answer 2

So, Martin Davis (dr_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 (dr_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 (dr_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.

enter image description here

enter image description here

Original spatial solutions...

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

• dr_jts: Great graphics!
• Cyril Mikhalchenko: @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 :-)...

Answer 3

Now one of the properties of the geofunction ST_GeoAstroide() (https://github.com/CM-FOGS/PostGeoSQL/blob/main/ST_GeoAstroide) is the ability to construct various spirals. Just for this purpose the geo-function view should be represented as follows, for example:

1. To build a classical spiral consisting of 3 spirals, run the following geoSQL code:

SELECT ST_SetSrid(ST_MakeLine(ST_GeoAstroide(geom, 1*i*0.00277777654321, i, 1, 1, 1, 1)),4326) geom FROM (SELECT ST_Point(0, 0) geom) foo 
CROSS JOIN LATERAL generate_series (0,360*3) AS step(i)

The result of the geo-function is shown in screenshot 1.

enter image description here

Screenshot 1

• By changing the + and - signs in the second and third parameters it is possible to change the direction of the spiral unwinding.

2. To build an astroidal spiral, run the following geoSQL code:

SELECT ST_SetSrid(ST_MakeLine(ST_GeoAstroide(geom, 1*i*0.00277777654321, i, 3, 1, 1, 3)),4326) geom FROM (SELECT ST_Point(0, 0) geom) foo 
CROSS JOIN LATERAL generate_series (0,360*3) AS step(i)

The result of the geo-function action is shown in screenshot 2.

enter image description here

Screenshot 2

3. Run the following geoSQL code to build a radial monotonically growing spiral:

SELECT ST_SetSrid(ST_MakeLine(ST_GeoAstroide(geom, 1*i*0.00277777654321, i, 2, 1, 1, 1)),4326) geom FROM (SELECT ST_Point(0, 0) geom) foo 
CROSS JOIN LATERAL generate_series (0,360*3) AS step(i)

The result of the geo-function action is shown in screenshot 3.

enter image description here

Screenshot 3.

And so on...

CM-FOGS...