I have two PostGIS tables with geometries. One with number attributes and one with letter attributes. Each table has two features (rows).

enter image description here

I would like to apply a operation on both stables so that I create the following result:

enter image description here

The resulting table has 7 rows and contains all attributes from the input tables. Some features have both the letter attribute and number attributes.

I struggle to obtain this result using PostGIS / SQL. So far I've only been able to create the features that have both letter and number attributes.


Is there a way to include the remaining features in the same query? I could also create multiple tables and perform a join but this is a non-parsimonious solution. I've also tried ST_Union to no avail.

In ArcMap and Geopandas this is often referred to as "Union" without dissolving however as far as I can tell ST_Union in PostGIS does something different. http://geopandas.org/set_operations.html https://desktop.arcgis.com/en/arcmap/10.3/tools/analysis-toolbox/how-union-analysis-works.htm

  • I am not sure I'm understanding the question completely but have you tried this: SELECT table1.letter, table2.number, ST_Intersection(table1.geom,table2.geom) FROM table1 FULL OUTER JOIN table2 ON ST_Intersects(table1.geom,table2.geom) – pandorabob Nov 12 '18 at 17:20
  • ST_Collect revenant.ca/www/postgis/workshop/advanced.html – Mapperz Nov 12 '18 at 17:20
  • I get an error when trying @pandorabob's answer; ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions SQL state: 0A000 – RutgerH Nov 12 '18 at 17:55
  • @Mapperz where in the command would you use ST_collect? – RutgerH Nov 12 '18 at 17:56
  • The final table's geometry's of each feature(row) is not the original large rectangle but one of the 7 smaller geometries. – RutgerH Nov 12 '18 at 18:06

You need the union of three sets:

1) the intersection of the two tables - has two values, one from each table.

2) The ST_DIFFERENCE Of the first table and the intersection set above - one value from 1st

3) The ST_DIFFERENCE of the second table and the intersection set above -one value from 2nd

I would turn the first set into its own table, then select the pairs of values from the two tables that show up in that table of intersections, and make a table of the ST_SYMDIFFERENCE of the two polygons. I believe that would give you groups 2 and 3 in a single pass. However, that only works if any given member of table 1 is intersected only by one member of table 2, and vice-versa. If so:

  ( select table1.value as table1_val,
           table2.value as table2_val,
           ST_INTERSECTION(table1.geom, table2.geom) as geom
    from table1, table2
    where table1.geom && table2.geom
      and ST_INTERSECTS(table1.geom, table2.geom)

   ( select IP.table1_val, IP.table2_val, 
            ST_SYMDIFFERENCE(table2.geom,table1.geom) as geom
           table1, table2
     where IP.table1_val = table1.value
       and IP.table2_val = table2.value

Then make a union of the intersections and the non-intersections. I'm sure there's one really big SQL statement that would do that with no temporary tables, but I treat SQL databases like a calculator.

That code also assumes that the "Table1 value" and Table2 value are also primary, unique indexes. If not, you'll need to pull those in.

If the two tables have members where there are multiple intersections - if Table 2 has two or more polygons intersecting with one polygon from table 1 - then THIS WILL NOT WORK RIGHT. You'd get two entries - one would be the Table 1 polygon minus the intersection with the first thing from Table2, another would be the same Table 1 polygon minus the other intersection.

That's avoidable with a bulkier bit of code: you have to make all the members of the INTERSECTION_POLY table that contain the same value from Table 2 into a "multipolygon" that is the union of all of them, and find the ST_SYMDIFFERENCE From that multipolygon.

We can go there, but I don't want to get into it if you don't need it.

| improve this answer | |

I came across a similar task once. I had to create overlapping parts of circles around thousands of trees in forest. Maybe the solution is not so straight forward (just take the inspiration), but it did the job:

WITH w_numbers AS (
    -- create two neighboring squares
    SELECT 1 AS id, 1 AS number, ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))') AS geom
    SELECT 2 AS id, 2 AS number, ST_GeomFromText('POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))') AS geom
w_letters AS (
    -- shift them to the down right so they overlap
    SELECT 1 AS id, 'A' AS letter, (SELECT ST_Translate(geom,0.5,-0.5) FROM w_numbers WHERE id = 1) AS geom
    SELECT 2 AS id, 'B' AS letter, (SELECT ST_Translate(geom,0.5,-0.5) FROM w_numbers WHERE id = 2) AS geom
), w_all AS (
    -- make one table from them
    SELECT id, number, NULL::char AS letter, geom
    FROM w_numbers
    SELECT id, NULL::int AS number, letter, geom
    FROM w_letters
), w_polygons AS (
    -- make all the possible boundaries with lines
    -- and make polygons with st_polygonize
        ST_Dump(ST_Polygonize(geom)) AS dump
            -- aggregate version of st_union
            ST_Multi(ST_Union(ST_ExteriorRing(geom))) as geom
        ) AS t1
-- join the original information about numbers and letters
-- spatially corresponding
    (dump).path[1] AS id,
    concat(t2.number,'_',t3.letter)::varchar AS char_id,
    (dump).geom AS dump_geom
    w_polygons AS t1
    w_numbers AS t2
    ST_Intersects(ST_Centroid((dump).geom), t2.geom)
    w_letters AS t3
    ST_Intersects(ST_Centroid((dump).geom), t3.geom);

Hope, it helps.

| improve this answer | |

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.