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...

SET geom = st_setsrid(
            geom, st_makepoint(longitude, latitude)
from (
        long as longitude,
        lat as latitude,
      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

  • Welcome to GIS SE ! Can you explain more what you want to achieve with TABLE1.geom ? I see you make a point with lat and lon (I assume the coordinates are in WGS 84, EPSG 4326) and you tried to make a polygon, but how, I don't understand, one coordinate pair can't make a polygon, maybe with a buffer function. Jan 20 '20 at 21:20
  • @J.Monticolo Preferably, this column should be a polygon (if there are 3 or more points), if only 2 then it should be a line, and lastly a point (if there is only one set of coordinates). Essentially I am trying to create my own shapefiles from some latitude and longitude data I have. Yes, to WGS 84, EPSG 4326 also. Thanks!
    – Megan
    Jan 20 '20 at 21:25
  • Three points don't make a polygon, four do (it needs to close). While PostGIS can permit multiple geometry classes in a single table, most GIS clients will treat this as an error. Shapefiles certainly can't support multiple geometry classes (it is explicitly forbidden by the specification), but I suspect you may be using "shapefile" to refer to a PostgreSQL table with with a geometry column, which is also incorrect.
    – Vince
    Jan 20 '20 at 21:54
  • @Vince Noted. Is there a way then to create a polygon when there are 4+ points represented in the geom column, if not to just store them as a collection of points?
    – Megan
    Jan 20 '20 at 21:56
  • You want to group points in your table 2 to make a triangle (3 points and close on the first point) or a line (2 points) ? But if there is 4 points, how to determine the order ? Jan 21 '20 at 8:09

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,

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) :

  geom = update_subquery.geom
    (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_MAKEPOINT(t.long, t.lat),
            public.gis_se_table2 t
            t.group = t2.group
  --) --uncomment for see WKT
    AS geom
    public.gis_se_table2 t2
  ) update_subquery
  public.gis_se_table1.group = update_subquery.group
  AND update_subquery.cnt >= 3

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 ?).
  • Ah, ok thanks I'll try this!
    – Megan
    Jan 21 '20 at 15:01
  • Actually, you could use only ST_ConvexHull and it will result as a point when there is only one point, and a line when there is 2 points. But you should probably make 3 tables for these 3 cases, because that's not the same type of geometry so it will be difficult to manipulate them later if you don't separate them. Jan 21 '20 at 15:39
  • You can group it into one query, but don't affect the Polygon geometry type to the Table1. The problem after is that you can't display data on QGIS (for example) without to create views for filter geom types. Jan 21 '20 at 15:40
  • @robinloche @J.Monticolo Thank you. I will attempt to only create geometries when there a 4 or more points so as to avoid this issue. Apologies I am new to postgis, what would the query look like to using ST_ConvexHull to create a polygon only when a group has 4 or more sets of points?
    – Megan
    Jan 21 '20 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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