1

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

  • 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. – J. Monticolo Jan 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 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 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 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 ? – J. Monticolo Jan 21 at 8:09
2

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
;
|improve this answer|||||
1

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 ?).
|improve this answer|||||
  • Ah, ok thanks I'll try this! – Megan Jan 21 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. – robin loche Jan 21 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. – J. Monticolo Jan 21 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 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.