1

I have a grid and for every cell I want to set the column "landuse" according to the largest intersecting polygon.

SELECT 
  grid.PKUID, 
  landuse.landuse,
  MAX(AREA(INTERSECTION(grid.Geometry,landuse.geom)))
FROM grid, landuse
WHERE Overlaps(grid.Geometry,landuse.geom) OR WITHIN(grid.Geometry,landuse.geom) OR  INTERSECTS(grid.Geometry,landuse.geom) 
GROUP BY grid.PKUID

Without the max() the table would look like this

PKUID | landuse | AREA(INTERSECTION(grid.Geometry,landuse.geom))
168     mais      0.14
168     wheat     0.04   
...

and this is what i get with the above query:

PKUID | landuse | MAX(AREA(INTERSECTION(grid.Geometry,landuse.geom)))
168     wheat     0.14
...

so the maximum value is selected but it doesn't tale the corresponding landuse. I PostgreSQL i would need to write the group part like this

GROUP BY grid.PKUID, landuse.landuse

but when I do this in my Spatialite query the aggregate function doesn't work and the result looks like i wouldn't have used it.

Any suggestions how to solve this?

1

Whenever you use an aggregate function with a GROUP BY clause and multiple results are available for the aggregate function, the last of the values is the one that's returned. That's why you're getting "wheat" for the pk_uid 168. What you can do to get the actual Max() value for the correct landuse is use a sorted subquery, and do the GROUP BY in the outer query like this:

SELECT t.pkuid, t.landuse, t.LU_Area
FROM (SELECT 
  g.PKUID AS pkuid, l.landuse AS landuse,
  AREA(INTERSECTION(g.Geometry,l.geom)) AS LU_Area
  FROM grid AS g JOIN landuse AS l 
  ON INTERSECTS(g.Geometry,l.Geometry)
  ORDER BY pkuid, LU_Area ASC
  ) AS t
GROUP BY t.pkuid;

BTW: I didn't understand why you need both Overlaps(), Within() AND Intersects() ??

  • Thanks, that worked well. The three different methods were just left over from trying them out, but intersects() alone is enough. – meles Dec 2 '13 at 11:40

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.