1

I am trying to generate a Cost Distance table using Spatialite. I have several points in the table random_points for which I would like to compute the Delaunay triangulation and use the segments it produces as arcs. Something like:

SELECT ST_DelaunayTriangulation(ST_Collect(Geometry)) FROM random_points;

And the reuse its output. How can this be achieved?

Alternatively, I have thought about generating the lines connecting each pair of random points (there will be millions of these, the total number of random_points is around 4000) and then to keep those that appear in the Delaunay triangulation. Something of that sort:

WITH dela AS (SELECT ST_DelaunayTriangulation(ST_Collect(Geometry), 1, 0) FROM random_points), 
  g1 AS (SELECT mergename, geometry FROM random_points), 
  g2 AS (SELECT mergename, geometry FROM random_points) 
  SELECT g1.mergename, g2.mergename, AsText(MakeLine(g1.geometry, g2.geometry))  FROM g1, g2 WHERE MakeLine(g1.geometry, g2.geometry) IN dela;

Any idea how to tweak it if the syntax is right?

0

If I understand your question correctly, the core of your problem is that you want to extract the individual line from the collections ST_DelaunayTriangulation. That can be done by ST_GeometryN and a little SQL trick to create a table containing a range of numbers.

CREATE TABLE delaunay_segments AS
WITH
    dela(lines) AS (
        SELECT DelaunayTriangulation(Collect(Geometry), TRUE)
        FROM random_points
    ),
    vals(n) AS (
        SELECT 1
        UNION ALL
        SELECT n+1 FROM vals
        LIMIT (SELECT NumGeometries(lines) FROM dela)
    )
SELECT GeometryN(dela.lines, vals.n) AS Geometry
FROM vals, dela;

SELECT RecoverGeometryColumn('delaunay_segments', 'Geometry', 4326, 'LINESTRING');

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.