I have a schema with multiple tables, all of which have a particular field, let's call it foo. They all have geometry wkb_geometry and unique key ogc_fid.

I need to create two new tables that would have intersections and unions of all features 'foo' equal to 'bar'.

I can do it for two tables specified explicitly:

-- intersection
create table intersection_results as
  select T1.foo, st_intersection(T1.wkb_geometry, T2.wkb_geometry) as geom
    from T1, T2
     where st_intersects(T1.wkb_geometry, T2.wkb_geometry) and T1.foo = 'Bar' ;

-- union
create table union_results as
  SELECT st_union(wkb_geometry) 
    FROM (
      SELECT wkb_geometry FROM T1 where foo = 'bar'
        UNION SELECT wkb_geometry FROM T2 where foo = 'bar'
          ) as moo ;

But what I am struggling with is to create a joined results table across all the tables in my schema.

  • Intersection and union are very different tasks. The Tour requires one question per Question.
    – Vince
    Commented Feb 17, 2018 at 23:55
  • As I understand that you would work on many tables at the same time. I prefer to use (join)to join the required tables and go further with your tasks
    – Moh
    Commented Feb 18, 2018 at 0:54
  • @User2009 If you have a chance to write it, how would you join the tables into one? That's essentially what I did, but I had to reimport the data with ogr appending new records to the existing table, thus not using the Postgres where I should have..
    – mikitk
    Commented Feb 18, 2018 at 20:17
  • Sorry, but I didn't understand you totally. If you already join them according to ogc.fid, why you need to add new records?
    – Moh
    Commented Feb 18, 2018 at 20:40
  • No, I didn't join them, just did it in a different (suboptimal) way. How would you join them?
    – mikitk
    Commented Feb 18, 2018 at 20:43

1 Answer 1


To intersect many tables and put the result in one table use the following: (you should modify it as your data and as you like)

CREATE TABLE new_intersect
(select a.kb_geometry as A, b.kb_geometry as B from T1 a, T2 b where ST_Intersects
(a.wkb_geometry, b.wkb_geometry) and T1.foo = 'Bar'))
(select a.kb_geometry, b.kb_geometry from T3 a, T4 b where ST_Intersects
(a.wkb_geometry, b.wkb_geometry) and T3.foo = 'Bar');

And if you would to join them as we discussed, you can do the following by Left Join:

Select *
ON T1.id=T2.id
ON T1.id=T3.id
ON T1.id=T4.id;   //and so on for other tables

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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