1

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. enter image description here

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

2

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.

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.