Convert Points to Polygon using PostGIS from a table with multiple points for one attribute

Question

I want to create a polygon table using PostGIS. Each row intest_table has points x and y. Table 'test_table' has the location information of points which is the column origin.

    I tried this 
        SELECT   ST_MakePolygon(ST_MakeLine (432099.197021 , 6736122.29126 , 432099.197021 , 6747306.948242 , 427835.719238 , 6747306.948242 , 427835.719238 , 6736122.29126, 23031));
        FROM   test_table
        where origin = '126af84e-0a9b-407d-8036-1ffc316106dd'

XMAX,YMIN 
XMAX,YMAX 
XMIN,YMAX 
XMIN,YMIN 

No luck I was wondering if someone can explain it better to me and is it possible to add a bounding box to the geometry? for all my attributes with points

Answer 1

You can make a line either from array of points or from WKT representation, and if you want to convert it to polygon the last point of the line should be the same as the first one (so that the polygon is closed). If I understand correctly you'd like to build bounding boxes for point clouds that share the same value of origin. This might be done like that:

with 
bounds as (
    select
     origin
    ,min(x) as xmin
    ,min(y) as ymin
    ,max(x) as xmax
    ,max(y) as ymax
    from test_table
    group by 1
)
select 
 origin
,st_makepolygon(st_makeline(array[
     st_makepoint(xmin,ymin)
    ,st_makepoint(xmax,ymin)
    ,st_makepoint(xmax,ymax)
    ,st_makepoint(xmin,ymax)
    ,st_makepoint(xmin,ymin)
]))
from bounds