Thursday, November 1, 2012

PostGIS - Majority Point Value in Polygon

Here I present a simple method for determining the majority point value within each polygon in PostGIS. This method utilizes the postgres Window function to rank order the count of point values within each polygon.  




--Majority Count of Point Values
SELECT
  
i.*

FROM(
SELECT
    
polygons.gid
  
,points.value
  
,COUNT(*) AS point_count
  
,RANK() OVER(PARTITION BY polygons.gid ORDER BY COUNT(*) DESC) AS point_value_rank

FROM polygons, points
WHERE ST_Intersects(polygons.geom, points.geom)
GROUP BY polygons.gid, points.value ) i
WHERE i.point_value_rank = 1;  

Oh, and by the way. My SQL has always look terrible because I usually get lazy halfway through edited the color etc (apologies). But now I have come across SQL "Prettify". Neat.

No comments:

Post a Comment