Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS

Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS

Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS

Hi everyone, I wonder if someone out there would be willing to offer me some advice on how to write this SQL query.

I have two tables (previously shape files) which I have imported into PostGIS: One point file (occ_master) and 1 polygon file (geol_1m). The table with point geom represents mineral occurrences. The table with polygon geom represents geology, which can be subcategorized by field lithological_unit, as displayed in the image. I need to find a way to determine how many mineral occurrences (points) occur in each lith_name. This query will allow me to figure out which geological unit has the most mineral occurrences. The end result should be a list of all of the different lithological units, with a count of how many mineral occurrences occur in that precise lithology unit.

I know it will involve a ST_Contains and GROUP BY query but it is my first day on PostGIS and so I am rather inexperienced. If anybody has an idea on how best to formulate this query, I would be so grateful!

https://preview.redd.it/b0f0vply51j61.jpg?width=580&format=pjpg&auto=webp&s=a032c1c41294a6c9e902526c47b27abb2a4481b2

submitted by /u/AccomplishedWinter93
[link] [comments]


Go to Source of this post
Author Of this post: /u/AccomplishedWinter93
Title Of post: Calculating how many points are in each polygon, grouping that polygon by a field in PostGIS
Author Link: {authorlink}