5

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)

0
10

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

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

enter image description here

enter image description here

Original spatial solutions...

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.