CODEWITHSUNDEEP
postgresqlgeometry
Log
Log

Reputation: 483

How get a concrete point of a polygon in PostgreSQL?

I have simple table:

create table boxes2(id serial, rect polygon);
insert into boxes2(rect) values(polygon'((0,0),(10,0),(10,10),(0,10))');

And I want to get concrete point of this polygon, I made this

select rect[0] from boxes2 where id = 1;

But there is error: "Type polygon is not array, impossible appoint index of element"

How can I do this?

Version - "PostgreSQL 9.5.4, compiled by Visual C++ build 1800, 32-bit"

Upvotes: 0

Views: 893

Answers (2)

joanolo
joanolo

Reputation: 6328

This is a rather complicated way of getting the 'nth' point of a polygon, based on assuming that PostgreSQL keeps the vertices in the same order they were entered, converting polygons to text, then parsing the text using regular expressions, taking out the part you're interested in, and casting it back to a point type. It's rather complicated:

WITH boxes2(id, rect) AS
(
VALUES
    (1, polygon'((0,0),(10,0),(10,10),(0,10))')
)
SELECT 
    p 
FROM 
    (
    SELECT
        row_number() over () AS rn, p
    FROM
        (
        SELECT
            (
             (regexp_matches(rect::text, 
              '\([^()]+\)', 'g')
             )[1]
            )::point AS p
        FROM
            boxes2
        WHERE
            id = 1
        ) AS pol_to_table_of_points
    ) AS numbered_table_of_points
WHERE
    rn = 2 /* This is if you want to get then 2nd point */ ;

I must admit this looks rather awful, but, AFAIK, there is no published way to get the 'nth' point of a polygon (nor a path). I'm quite sure you can do it using PostGIS, but have not found a way to do it without it.

There is a function to get the number of points (npoints(polygon '((1,1),(0,0))')), but not a specific point. And converting a polygon to an array of points (a cast a_polygon::point[]) doesn't work either.

Caveat:

From a geometric point of view, if A, B, C and D are points (vertices or corners), then the polygons described by (A, B, C, D), (B, C, D, A), (C, D, A, B), (D, A, B, C) and the same (ordered) quadruplets but reversed: (D, C, B, A), ... are absolutely equivalent.

So, you cannot really say which is the "second" vertex of a polygon. At this point, and just having checked a few samples with very few points, it seems that PostgreSQL represents polygons as an ordered list of vertices, keeping the original order. But it actually could change it if this new representation helps it in some way.

For instance, all the polygons in the following query are equivalent:

WITH some_polygons (p1, p2, p3, p4, p5, p6, p7, p8) AS
(
VALUES
    ('(0,0), (1,0), (2,2), (0,3)' :: polygon,
     '(1,0), (2,2), (0,3), (0,0)' :: polygon,
     '(2,2), (0,3), (0,0), (1,0)' :: polygon,
     '(0,3), (0,0), (1,0), (2,2)' :: polygon,

     '(0,3), (2,2), (1,0), (0,0)' :: polygon,
     '(0,0), (0,3), (2,2), (1,0)' :: polygon,
     '(1,0), (0,0), (0,3), (2,2)' :: polygon,
     '(2,2), (1,0), (0,0), (0,3)' :: polygon
    )
)
SELECT
    p1 ~= p2 AS eq2,  -- ~= stands for "same as"
    p1 ~= p3 AS eq3,
    p1 ~= p4 AS eq4,
    p1 ~= p5 AS eq5,
    p1 ~= p6 AS eq6,
    p1 ~= p7 AS eq7,
    p1 ~= p8 AS eq8 
FROM
    some_polygons ;

I think this is the reason why PostgreSQL decided not to let you take the "nth" vertex of a polygon: it is something not theoretically defined.

So, if you want to keep on the safe side, and not risk that a later implementation change breaks your code, as an alternative, you could store an array of points (point[]). An array is guaranteed to keep values in the same order they were supplied. If you need both a polygon and the original list of vertices, I think I would have the two pieces of data (even if this is a not-normalized form). If you only work with rectangles, I'd use the type box; it better represents your needs.

Upvotes: 1

klin
klin

Reputation: 121889

There is a function ST_DumpPoints(geometry geom) in the PostGIS extention to extract points from a geometric object.

If rect values are just boxes then you can convert polygons to boxes:

select (box(rect))[0] right_top, (box(rect))[1] left_bottom
from boxes2 
where id = 1;

 right_top | left_bottom 
-----------+-------------
 (10,10)   | (0,0)
(1 row)

In this case however you can simply store the values as type box.

Upvotes: 2

Related Questions