spatial database - Updating polygon attribute based on mode value from cluster of points using PostGIS?

I'm updating a PostGIS table of polygons denoting property boundaries with some values that form part of a code for sites scattered across the properties. Neither dataset is 100% accurate so I can't rely on a direct attribute transfer based on overlap; instead I need to find a way to take the most common value from the points and apply them to the polygon. Attaching an image to clarify:

That image also shows an example where a polygon needs to be defined for EY-183 but that's another matter.

Any ideas on how to do this, either in PostGIS?

Answer

The mode aggregate function isn't available in PostgreSQL (<9.4) by default but you can easily add it. Once you have that, it's a simple matter of choosing the mode, grouped by polygon.

SELECT mode(a.name), b.gid 
FROM pt_table a INNER JOIN poly_table b
ON ST_Contains(b.geom, a.geom)

GROUP BY b.gid;

Of course this isn't a particularly robust way of identifying polygons, but it's a start.