Creating or updating geometry polygon column with new point using PostGIS

Question

I have PostgreSQL tables with the following columns:

Table (1)

| Column        | Datatype                |
| ------------- |:-----------------------:|
| group         | TEXT                    |
| geom          | Geometry (Polygon)      |

Table (2)

| Column        | Datatype     |
| ------------- |:------------:|
| group         | TEXT         |
| lat           | NUMERIC      |
| long          | NUMERIC      |

I want to, for every row in table (2), create or update the geometry columns in the first table (1) using the lat and long where the group column names are matching. Apologies if something like this has been answered before but I could not find a good example. I've tried queries like below (I know its wrong) but I can't seem to figure out how to use the PostGIS functions to get the behavior I want...

UPDATE TABLE_1
SET geom = st_setsrid(
    st_makepolygon(
        st_makeline(
            geom, st_makepoint(longitude, latitude)
        )
    ),
4269)
from (
     select
        long as longitude,
        lat as latitude,
        group
      from TABLE_2
) as subquery
where group = TABLE_1.group;

From discussion in comments:

Preferably, the geom column should be a polygon (if there are 4 or more points), if less than 4 it should just be a collection of points. Essentially I am trying to create my own geometries from some latitude and longitude data I have. Also I am using: WGS 84, EPSG 4326

Answer 1

From the comments, and your post, I can create test data with :

DROP TABLE IF EXISTS public.gis_se_table1;
CREATE TABLE public.gis_se_table1 (
id serial PRIMARY KEY,
"group" TEXT
);

SELECT AddGeometryColumn('public', 'gis_se_table1', 'geom', 4269, 'POLYGON', 2);

INSERT INTO public.gis_se_table1 ("group") VALUES ('1'), ('2'), ('3'), ('4');

DROP TABLE IF EXISTS public.gis_se_table2;
CREATE TABLE public.gis_se_table2 (
id serial PRIMARY KEY,
"group" TEXT,
lat NUMERIC,
long NUMERIC
);

INSERT INTO public.gis_se_table2 ("group", lat, long) VALUES
('1', 0, 0),
('2', 1, 1),
('2', 1, 2),
('3', 2, 0),
('3', 2, 1),
('3', 3, 1)
;

And after, do an update only when the table2 have at least 3 points (cnt column) :

UPDATE
  public.gis_se_table1
SET
  geom = update_subquery.geom
FROM
  (SELECT DISTINCT
    t2.group,
    (SELECT COUNT(*) FROM public.gis_se_table2 t WHERE t.group = t2.group GROUP BY t.group) AS cnt,
  --ST_ASTEXT( --uncomment for see WKT
    ST_ConvexHull(
      ST_Collect(
        ARRAY(
          SELECT
            ST_TRANSFORM(
              ST_SETSRID(
                ST_MAKEPOINT(t.long, t.lat),
                4326
              ),
              4269
            )
          FROM
            public.gis_se_table2 t
          WHERE
            t.group = t2.group
        )
      )
    )
  --) --uncomment for see WKT
    AS geom
  FROM
    public.gis_se_table2 t2
  ) update_subquery
WHERE
  public.gis_se_table1.group = update_subquery.group
  AND update_subquery.cnt >= 3
;

Answer 2

There is 2 problems:

1. You should separate the cases depending of the number of points by group (for exemple do 3 request: one for 1 point by group (point), one for 2 points by group (lines) and the last one for polygons)
2. In the case of polygons, you need to order the points to construct it. I'm guessing that you have no id that make sense for ordering, that's probably because you want something like the "area" covered by the points. If that's what you want, you should probably use the function ST_ConvexHull (or maybe ST_ConcaveHull ?).