Choose "strongest" intersected area

Question

I have a materialized view which is the result of a spatial joint using st_intersect of two polygons layers. Table1 and table2, features of table1 can be itnersected for few polygons of table2, thsi is how i create the mview:

SELECT g.field1,
    att.ogc_fid,
    st_intersection(g.geom, att.geom) AS intersect_geom,
    st_area(g.geom) AS geom_area,
    st_area(st_intersection(g.geom, att.geom)) AS intersect_area
   FROM table1 g
     JOIN table2 att ON g.geom && att.geom;


field1 | ogc_fid | intersect_geom| geom_area | intersect_area
aa12345    1              123123       123131    1313123414
aa12345    3               1              1       1
bb12345    2             4124141       13141      14415151
bb12345    1           1243141414     1231313     13131323

From this mview i want to pick just the strongest intersected area and join to a description coming from table2.. I have tried the code below:

select a.*, b.desc
from table1 a
left join lateral
(
select desc
 table2
where table2.ogc_fid= table1.ogc_fid
order by (intersect_area/geom_area) DESC NULLS LAST
limit 1
) b

field1 | ogc_fid | intersect_geom| geom_area | intersect_area | desc
aa12345    1              123123       123131    1313123414      desc for 1
bb12345    2             4124141       13141      14415151       desc for 2

but results here are not the expected ones. I went through other threads but im stuck when trying to get just one result (the strongest), and create a table with those strongest intersection so for one feature in table one i have the most strongest intersected.

I think you need to join on st_intersects, then use a "select distinct on" query to return only one record per feature. Best to add table structure and a couple of records to your question. https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by — mlinth, Nov 18 '21 at 10:59
I have updated the question, st_intersect is already used for creating the spatial join which create the materialized view. The purpose is associate this strongest intersection to one description field from a source table joining it by the common field ogc_fid — gcj, Nov 18 '21 at 11:07
@jr.gcj to be sure: is it what you mean? `SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.ogc_fid = t2.ogc_fid ORDER BY t1.geom && t2.geom LIMIT 1;` or `SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.ogc_fid = t2.ogc_fid ORDER BY ST_Intersection(t1.geom,t2.geom) LIMIT 1;` — Jim Jones, Nov 18 '21 at 11:11
@JimJones i have updated the question adding how i generate the mview, form there i want to pick one record for each a.field1 (the strongest) and joinn it to the description field, b.desc. Generating a new output. — gcj, Nov 18 '21 at 11:30
@jr.gcj I see. Can you also post some sample data and the exact expected result? It would make things faster :-) — Jim Jones, Nov 18 '21 at 11:36
@JimJones i have update with a sample of how it looks mview results and what i am looking to get as result. Thanks! — gcj, Nov 18 '21 at 13:02

score 2 · Accepted Answer · answered Nov 18 '21 at 13:13

If I understood you right, you have done the hard bit already. You just need to pick the one record per field from the view and join with table2... So try this:

SELECT DISTINCT ON (field1) field1, m.ogc_fid, b.desc FROM
mview AS m
INNER JOIN table2 AS b ON b.ogc_fid = m.ogc_fid
ORDER BY field1, (intersect_area/geom_area) DESC

Choose "strongest" intersected area

1 Answers1