1

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 Feb 17 '18 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 Feb 18 '18 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 Feb 18 '18 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 Feb 18 '18 at 20:40
  • No, I didn't join them, just did it in a different (suboptimal) way. How would you join them? – mikitk Feb 18 '18 at 20:43
0

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
AS
(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'))
UNION
(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 *
FROM T1
LEFT JOIN T2
ON T1.id=T2.id
LEFT JOIN T3
ON T1.id=T3.id
LEFT JOIN T4
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, privacy policy and cookie policy

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