4

I am trying to get all the lines between the points of a polygon, by writing PostGreSQL functions. I have written the two following functions that do the job fine :

  • the first function to get all the points from the polygon
 CREATE OR REPLACE FUNCTION PointsFromPolygon(polygon geometry) RETURNS SETOF geometry AS $$ DECLARE point geometry; BEGIN FOR point IN SELECT DISTINCT points.geom FROM ( SELECT (ST_DumpPoints(polygon)).* ) AS points LOOP RETURN NEXT point; END LOOP; END; $$ LANGUAGE plpgsql ;
  • the second function to create all segments between these points.
 CREATE OR REPLACE FUNCTION AllSegmentsFromPoints(polygon geometry) RETURNS SETOF geometry AS $$ DECLARE point1 geometry; point2 geometry; i integer; BEGIN i:=1; FOR point1 IN SELECT * FROM PointsFromPolygon(polygon) LOOP FOR point2 IN SELECT * FROM PointsFromPolygon(polygon) OFFSET i LOOP RETURN NEXT ST_MakeLine(point1,point2); END LOOP; i:= i+1; END LOOP; END; $$ LANGUAGE plpgsql;

Is there a better way to perform this ? Perhaps by doing a cartesian product between the points ?

Improve this question

2 Answers 2

Reset to default
5

I think I found my answer :

 WITH poly_geom1 AS (SELECT way FROM planet_osm_polygon WHERE id=1), poly_geom2 AS (SELECT way FROM planet_osm_polygon WHERE id=1), points1 AS (SELECT (ST_DumpPoints(poly_geom1.way)).* FROM poly_geom1), points2 AS (SELECT (ST_DumpPoints(poly_geom2.way)).* FROM poly_geom2) SELECT DISTINCT ST_MakeLine(points1.geom, points2.geom) FROM points1,points2 WHERE points1.path <> points2.path;

Execution time for a 40 points polygon goes down from 135ms to 115ms.

Update

Thank to simplexio, the following query does exactly the same and is shorter :

WITH poly_geom AS (SELECT way FROM planet_osm_polygon WHERE id=1), points1 AS (SELECT (ST_DumpPoints(poly_geom.way)).* FROM poly_geom), points2 AS (SELECT (ST_DumpPoints(poly_geom.way)).* FROM poly_geom) SELECT DISTINCT ST_MakeLine(points1.geom, points2.geom) FROM points1,points2 WHERE points1.path <> points2.path;
Improve this answer
2
  • 1
    can you drop poly_geom2 and use only poly_geom1 in points2 selection ? Just intrested. Commented Apr 19, 2013 at 11:44
  • 1
    Yes you are right, I can use only poly_geom1. The execution time is exactly the same but the query shorter Commented Apr 19, 2013 at 14:04
1

Another solution, that avoids duplicate lines:

WITH poly(id, geom)AS (VALUES ( 1, 'POLYGON ((2 7, 5 9, 9 7, 8 3, 5 2, 2 3, 3 5, 2 7))'::geometry ) ) ,ring AS (SELECT ST_ExteriorRing(geom) geom FROM poly) ,pts AS (SELECT i, ST_PointN(geom, i) AS geom FROM ring JOIN LATERAL generate_series(2, ST_NumPoints(ring.geom)) AS s(i) ON true) SELECT ST_MakeLine(p1.geom, p2.geom) AS geom FROM pts p1, pts p2 WHERE p1.i > p2.i;
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.