From table with points to table with polygons

Question

I have a table POINTS like the following:

POINTS
--------------------
ID_POINT     INT PK
COORD        POINT
POSITION     INT
ID_POLYGON   INT

With the following content

ID_POINT  COORD           POSITION     ID_POLYGON
---------------------------------------------------
1         POINT(1, 1)     1            1
2         POINT(2, 1)     2            1   
3         POINT(2, 2)     3            1
4         POINT(0, 0)     1            2
5         POINT(1, 0)     2            2
6         POINT(1, 1)     3            2
7         POINT(0, 1)     4            2

I need to extract with a query if possible the following resultset

ID_POLYGON           BORDERS
----------------------------------------------------
1                    POLYGON((1 1, 2 1, 2 2))
2                    POLYGON((0 0, 1 0, 1 1, 0 1))

Somebody has solved a similar problem?

Note: the order of points is defined by the column position.

Answer 1

I think what you're looking for here is GROUP_CONCAT, i.e. something like

SELECT ID_POLYGON, GROUP_CONCAT(COORD ORDER BY POSITION ASC)
FROM POINTS
GROUP BY ID_POLYGON

For more on GROUP_CONCAT see the docs.