Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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;
share|improve this answer
1  
can you drop poly_geom2 and use only poly_geom1 in points2 selection ? Just intrested. –  simplexio Apr 19 '13 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 –  FredB Apr 19 '13 at 14:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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