2

So let's say I'm creating a polygon table taking attributes from multiple polygon tables. For argument's sake let's say one is a country polygon table and one is a mountain table

On my mountain table I have the Alps, and I want to give it the country in which the Alps is covered by the most

create table as select a.id, a.mountain, b.country
from mountaintable a, countrytable b
where st_intersects???

How to make it return the needed result?

1
  • 1
    Best practice is to use a JOIN. The documentation for ST_Intersects, ST_Intersection, and ST_Area, along with the row_number window function should get your question further along toward the best effort maturity expected in questions.
    – Vince
    Jun 25, 2018 at 12:02

1 Answer 1

11

Good candidate for a lateral join.

In a lateral join you can use the attributes from the joining table in determining the join criteria. So we are filtering the country table to select the one with the largest area intersecting with the mountain table:

create table as select a.id, a.mountain, b.country
from mountaintable a
left join lateral
(
select country from
 countrytable
where st_intersects(countrytable.geom, a.geom)
order by st_area(st_intersection(countrytable.geom, a.geom)) DESC NULLS LAST
limit 1
) b on true

Also this is assuming an equal area projection, you may want to cast to geography when checking the area of intersection.

1
  • First time I see both a lateral and also a boolean on a create query. hats off sir
    – Luffydude
    Jun 26, 2018 at 8:13

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.