Choose "strongest" intersected area-CodePudding

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.

CodePudding user response：

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