1

I have two tables. One for school districts, one for schools with the total number of each student in the school.

I created a float field in the districts table for student population. I want to populate this field with the sum of students in each school within each district.

Essentially, I need to select by location the schools in each district and assign the summary of their student population to the district student population field.

I feel like this is a simple task with ArcMap, but I can't afford their license.

3

I'm assuming that your school district table has polygon geometries and that your schools table has point geometries. You'd join the tables and output like so:

SELECT a.id AS district_id, sum(b.students) AS total_students FROM school_district a INNER JOIN schools b ON ST_Intersects(a.geom, b.geom) GROUP BY a.id;

Just to clarify some of that, a and b are aliases for the longer table names, and I'm giving clear names district_id and total_students to the output columns (which isn't necessary, but thought I'd share how to do that should you want to name your own output columns). The query joins features spatially by where they intersect ON ST_Intersects(a.geom, b.geom) and then sums the number of students by the GROUP BY part of the query, in your case each school district. But check out @GISKid's comment to what is a very good resource for postgis joins.

  • From the title I think his schools are polygons too, but the statement should works as well ... – Jochen Schwarze May 5 '17 at 7:15
  • Good point (and great reading comprehension on my part...). If schools are polygons then they should reasonably be converted to points to avoid any potential double counting if there happens to be any overlap along district boundaries. It's probably not likely however. – scabecks May 5 '17 at 7:40
  • Thank you! The schools layer was polygon parcels — used to set buffers for drug-free enforcement zones. But it still worked fine. Helped save me a lot of frustration. – Mateo May 5 '17 at 16:20

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.