How to filter the max value and write to row?

Question

Postgres 9.3.5, PostGIS 2.1.4.

I have tow tables (polygons and points) in a database.

I want to find out how many points are in each polygon. There will be 0 points per polygon or more than 200000. The little hick up is the following.

My point table looks the following:

x    y    lan
10  11    en
10  11    fr
10  11    en
10  11    es
10  11    en
- #just for demonstration/clarification purposes
13  14    fr
13  14    fr
13  14    es
-
15  16    ar
15  16    ar
15  16    ps

I do not simply want to count the number of points per polygon. I want to know what is the most often occuring lan in each polygon. So, assuming each - indicates that the points are falling into a new polygon, my results would look the following:

Polygon table:

polygon    Count   lan
1          3       en
2          2       fr
3          2       ar

This is what I got so far.

SELECT count(*), count.language AS language, hexagons.gid AS hexagonsWhere 
  FROM hexagonslan AS hexagons, 
       points_count AS france 
 WHERE ST_Within(count.geom, hexagons.geom) 
 GROUP BY language, hexagonsWhere 
 ORDER BY hexagons DESC;

It gives me the following:

Polygon    Count     language
1          3         en
1          1         fr
1          1         es
2          2         fr
2          1         es
3          2         ar
3          1         ps

Two things remain unclear.

1. How to get only the max value?
2. How will cases be treated where there are by any chance the max values identical?

Answer 1

Answer to 1.

To get the most common language and its count per Polygon, you could use a simple DISTINCT ON query:

SELECT DISTINCT ON (h.gid)
       h.gid AS polygon, count(c.geom) AS ct, c.language
FROM   hexagonslan h
LEFT   JOIN points_count c ON ST_Within(c.geom, h.geom)
GROUP  BY h.gid, c.language
ORDER  BY h.gid, count(c.geom) DESC, c.language;  -- language name is tiebreaker

• Select first row in each GROUP BY group?

But for the data distribution you describe (up to 200.000 points per polygon), this should be substantially faster (hoping to make better use of an index on c.geom):

SELECT h.gid AS polygon, c.ct, c.language
FROM   hexagonslan h
LEFT   JOIN LATERAL (
   SELECT c.language, count(*) AS ct
   FROM   points_count c
   WHERE  ST_Within(c.geom, h.geom) 
   GROUP  BY 1
   ORDER  BY 2 DESC, 1  -- again, language name is tiebreaker
   LIMIT  1
   ) c ON true
ORDER  BY 1;

• Optimize GROUP BY query to retrieve latest record per user

LEFT JOIN LATERAL .. ON true preserves polygons not containing any points.

• Call a set-returning function with an array argument multiple times

In cases where there are by any chance the max values identical, the alphabetically first language is picked in the example, by way of the added ORDER BY item. If you want all languages that happen to share the maximum count, you have to do more:

Answer to 2.

SELECT h.gid AS polygon, c.ct, c.language
FROM   hexagonslan h
LEFT   JOIN LATERAL (
   SELECT c.language, count(*) AS ct
        , rank() OVER (ORDER BY count(*) DESC) AS rnk
   FROM   points_count c
   WHERE  ST_Within(c.geom, h.geom) 
   GROUP  BY 1
   ) c ON c.rnk = 1
ORDER  BY 1, 3  -- language only as additional sort critieria

Using the window function rank() here, (not row_number()!). We can get the count or points and the ranking of the count in a single SELECT. Consider the sequence of events:

• Best way to get result count before LIMIT was applied