1

I have many tables with geometry attribute which is saved in Postgis database. These tables are basically from more than one shapefiles which is stored in Postgis using geotools.

Now I want a common table to handle all the geom attributes in the table, as shown in the below example

For eg:

enter image description here

Is there a way to do this using PostGIS? Initially the Table geom_all with geom column contains NULL value. I tried updating the values using JOINS with the below query example.

 UPDATE Geom_all g SET geom = (SELECT geom FROM Table-1 t WHERE g.id = t.id);

But this overrides with every new value i update in the geom column and also the sub query should return only 1 row, which in my case is not. I am not sure whether I can achieve this task using JOINS, it would be helpful to know if there is any other way to solve this.

NOTE: All the geom column contains geometry_type as MULTIPOLYGON and ST_SRID as 4326.

  • 1
    Can you union all the rows together from tables 1, 2 etc. ? You migt want a new column to give all the rows a unique ID – Dave C Nov 22 '18 at 13:02
  • Yes Dave I tried doing a ST_union (geom), but this removes the ID info. How do i handle this? however I need the geom specific ID info to map other attributes based on the ID later. – GeoFresher Nov 22 '18 at 13:14
  • Answer below was what I was refering to. ST_Union is trying to 'join' the geometries, where you just want to union the rows. – Dave C Nov 22 '18 at 14:30
1

If I understand correctly, you want to merge the rows from multiple tables. You don't need to alter the geometry

Create table geom_all as
Select ID, geom from table1
UNION
select ID, geom from table2
UNION 
select ID, geom from table 3;

or if the table already exists:

Insert into geom_all (select ID, geom from table1);
  • I didnt expect it to be so straight forward, thank you @JGH :) – GeoFresher Nov 22 '18 at 14:18

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.