Update array geometry field using array lat & long field in PostGIS

Question

I have two array columns in Postgres named lat and long. Both have 1-5 values in their array field. Like the following

Now I need to create geometry from each value based on the array index of lat & long field and update an array geometry column. I have created the geometry from the fields by using the following query

Query:

select
st_SetSrid(st_MakePoint(long[1] :: double precision, lat[1] :: double precision), 4326),
st_SetSrid(st_MakePoint(long[2] :: double precision, lat[2] :: double precision), 4326),
st_SetSrid(st_MakePoint(long[3] :: double precision, lat[3] :: double precision), 4326),
st_SetSrid(st_MakePoint(long[4] :: double precision, lat[4] :: double precision), 4326),
st_SetSrid(st_MakePoint(long[5] :: double precision, lat[5] :: double precision), 4326)
from loc_table;

Output:

It creates separate columns but I need to update an array geometry column. I have tried using that select query first after so many failed attempts as following

Query:

select 
string_to_array(
concat(
st_SetSrid(st_MakePoint(long[1] :: double precision, lat[1] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[2] :: double precision, lat[2] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[3] :: double precision, lat[3] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[4] :: double precision, lat[4] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[5] :: double precision, lat[5] :: double precision), 4326)), ',' ) as geom 
from loc_table;

Output:

Then I tried with the update query with following

Query:

update loc_table
set geom_array  = string_to_array(
concat(
st_SetSrid(st_MakePoint(long[1] :: double precision, lat[1] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[2] :: double precision, lat[2] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[3] :: double precision, lat[3] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[4] :: double precision, lat[4] :: double precision), 4326), ',' ,
st_SetSrid(st_MakePoint(long[5] :: double precision, lat[5] :: double precision), 4326)), ',' );

Output:

Note that, the geom_array field/column is the array geometry type column and I know that the query made the geometry into the string. But that’s the best I can able to do. And I have a large amount of data like 5-10 million. So, I have to use an efficient way to do so. Can anyone please help me out with this?

I am using PostGIS 2.5 and Postgres 11.5.

Answer 1

PostgreSQL's UNNEST comes with a multi-array table function signature that extracts given arrays into sets of rows with respect to their ordinality.

Inside an UPDATE statement this is rather straight forward to implement:

UPDATE loc_table
    SET geom_array = (
          SELECT ARRAY_AGG(ST_SetSRID(ST_MakePoint(x::FLOAT8, y::FLOAT8), 4326))
          FROM   UNNEST(long, lat) AS arr(x, y)
    )
;

---

However:

Keep in mind that the core paradigm of RDBS are normalized relations, and the core feature for query performance is indexation, which, generally speaking, decreases in effectiveness with growing complexity.

In fact, you will not be able to index these geometries while they are stored in arrays, other than the actual bounding box around all contained geometries; run e.g.:

ALTER TABLE loc_table
  ADD COLUMN geom_array_bbox GEOMETRY(GEOMETRY, 4326)  --needs to be generic GEOMETRY rather than POLYGON for single point arrays
;

CREATE INDEX ON loc_table USING GIST (geom_array_bbox);


UPDATE geom_array_test
    SET (geom_array_bbox, geom_array) = (
          SELECT ST_SetSRID(ST_Extent(pt), 4326)::GEOMETRY,
                 ARRAY_AGG(pt)
          FROM   UNNEST(lon, lat) AS arr(lon, lat),
                 LATERAL ST_SetSRID(ST_MakePoint(lon, lat), 4326) AS pt  --save writing out the same point generation twice, for both SELECT columns
    )
;

to store actual geometries to work with, or, if storage space is at a premium (or there simply is no need to work with bbox geometries), simply index the array union:

CREATE INDEX ON loc_table USING GIST ( ST_Union(geom_array) );

Note here that any attempt to utilize the index needs you to explicitly call ST_Union(geom_array) in functions like ST_Intersects!

As a matter of fact, it may be a lot more convenient to store the geom_array as a MULTIPOINT in the first place:

ALTER TABLE loc_table
  ADD COLUMN geom GEOMETRY(MULTIPOINT, 4326)
;

CREATE INDEX ON loc_table USING GIST (geom);


UPDATE loc_table
  SET geom = (
        SELECT ST_Multi(ST_Union(ST_SetSRID(ST_MakePoint(x::FLOAT8, y::FLOAT8), 4326)))
        FROM   UNNEST(long, lat) AS arr(x, y)
    )
;

---

If you intent to being able to filter for any of these grouped geometries individually, e.g. by spatial relation with another table, you may want to consider to normalize that multi value column into a related table, i.e. UNNEST the points into rows, with an id reference to the base table; an index may be more efficient, and the core RDB paradigms are honored.