PostGIS: Perform a Spatial Join only joining the intersecting polygon with the maximum value

Question

I'd like to perform a spatial join using PostGIS. I've got table_A containing 80.000 entries and table_B containing roughly 1.500.000 entries. The entries in table_B have a column "number".

My goal is to only join the entries from table_B with the maximum value which intersect polygons from table_A.

For example: In the following image I'd like to only join "polygon 3" from table_B to the blue polygon of table_A. Polygon 1 and Polygon 2 should not be joined because they have lower numbers.

I could also use ArcMap (Standard license) but it crashes due to the size of the shapefile (~ 1.8GB).

Answer 1

You could use the MAX aggregate function on a subquery that does the spatial query.

E.g.

SELECT aid, bid, MAX(noOfThings) FROM (
  SELECT a.id AS aid, b.id AS bid, b.noOfThings AS noOfThings
  FROM table_A AS a, table_B AS b
  WHERE ST_Within(b.polygon, a.polygon)
)
GROUP BY (aid, bid);

(untested)

I've not called the column number, because it's a reserved keyword http://www.postgresql.org/docs/current/static/sql-keywords-appendix.html

You cannot perform the join immediately since you won't know which is the largest until you've completed the initial spatial join (hence the subquery). There might be a few ways to optimise you're query in some scenarios, but more knowledge of the underlying data is required.