2

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.

SELECT
    table1.letter,
    table2.number,
    ST_Intersection(table1.geom,table2.geom)
FROM 
    table1,
    table2
WHERE
    ST_Intersects(table1.geom,table2.geom)

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
2

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:

CREATE TABLE INTERSECTION_POLY as
  ( 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)
  );



CREATE TABLE EXCLUSION_POLY as
   ( select IP.table1_val, IP.table2_val, 
            ST_SYMDIFFERENCE(table2.geom,table1.geom) as geom
     from  INTERSECTION_POLY as IP,
           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.

0

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
    UNION ALL
    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
    UNION ALL
    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 (
----------------
-- SOLUTION HERE
----------------
    -- make one table from them
    SELECT id, number, NULL::char AS letter, geom
    FROM w_numbers
    UNION ALL
    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
    SELECT
        ST_Dump(ST_Polygonize(geom)) AS dump
    FROM
        (SELECT
            -- aggregate version of st_union
            ST_Multi(ST_Union(ST_ExteriorRing(geom))) as geom
        FROM
            w_all
        ) AS t1
)
-- join the original information about numbers and letters
-- spatially corresponding
SELECT
    (dump).path[1] AS id,
    concat(t2.number,'_',t3.letter)::varchar AS char_id,
    (dump).geom AS dump_geom
FROM
    w_polygons AS t1
LEFT JOIN
    w_numbers AS t2
ON
    ST_Intersects(ST_Centroid((dump).geom), t2.geom)
LEFT JOIN
    w_letters AS t3
ON
    ST_Intersects(ST_Centroid((dump).geom), t3.geom);

Hope, it helps.

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.