2

All,

We're using parcel data (polygon) to count student locations (point) based on parcel type. We're doing this using SQL and the ST_Intersects function.

It works great, except the City and County of Denver stores parcels with condominiums on them as multiple overlapping polygons, that is, each condo at a location gets its own parcel polygon - if there are 20 buildings on a parcel, there are now 20 coincident parcel features stacked on top of one another.

This is a problem because each parcel would count 1 student at that location once for every condominium parcel. So while there is only 1 student at that 'spatial location', if there were 20 condos at that location, that would be counted as 20 students.

Using a SQL 'distinct' does not seem to work...

How can we either:

a) Generalize the polygon data such that all overlapping polygons become a single polygon layer

b) Use some other SQL wizardry to eliminate the overlapping polygons from the query?

Thanks!!!

-m

  • Does the parcel data have a subtype or a field to delineate the condos from non-condominium property? – evv_gis Mar 14 '14 at 15:45
  • 2
    If you have the student addresses, and you are just wanting to get numbers based on parcel types, and not specifically to map the results, you could join the parcel information to the student information based on their address, rather than joining the student locations to the parcels. – TDavis Mar 14 '14 at 15:45
  • @evv_gis yes the Condominiums have an attribute in the Parcel Type field. – DPSSpatial Mar 14 '14 at 15:51
  • @TDavis interesting... that might work. Let me try that and report back... That would also allow us to do the next step, which is aggregate those counts based on our population forecast area... thank you!!! – DPSSpatial Mar 14 '14 at 15:52
  • 1
    it sounds like the overlapping parcels must have different addresses.if the address is the same, but with different apartment/unit numbers, maybe you can create a new address field that doesn't include a unit number, and filter/summarize based on that. or, if the address is identical already in the overlapping parcels, you could just summarize the results, and that way you would have one records per address, as well as a count value per parcel, in case you wanted to include that info later on – TDavis May 6 '14 at 3:23
2

You can use DISTINCT ON to remove duplicate records in the student-polygon join, so that you only get one parcel type per student. If a student is within multiple parcel types, you'll get a random type back unless you impose some kind of rule with an ORDER BY clause.

SELECT DISTINCT ON (student_id) student_id, parcel_type
FROM students
INNER JOIN parcels ON ST_Intersects(students.geom, parcels.geom)

You can use the above as a subquery to get a count of students by parcel type:

SELECT parcel_type, COUNT(student_id) FROM (
    SELECT DISTINCT ON (student_id) student_id, parcel_type
    FROM students
    INNER JOIN parcels ON ST_Intersects(students.geom, parcels.geom)
) subquery
GROUP BY parcel_type
|improve this answer|||||
  • I had to get this into PostGIS to get a better handle on the problem, but your solution worked best - thank you and very sorry for the delay, but the problem just re-surfaced and I've finally got it working!!! – DPSSpatial May 22 '15 at 20:46
1

You can't just say DISTINCT doesn't work without showing how you're trying to use it. It has to work if you use it like this:

SELECT COUNT(DISTINCT student_id), parcel_type
FROM students
INNER JOIN parcels ON ST_Intersects(students.geom, parcels.geom)
GROUP BY parcel_type

If it doesn't use then the problem is in student_id not being unique.

|improve this answer|||||
  • One hindrance for me is the size of these datasets... let me create a subset and test the method you've suggested. – DPSSpatial Apr 23 '14 at 15:58
  • 2
    Note that this query will not run - you can't mix aggregates and straight columns without using GROUP BY. If you add a GROUP BY, you'll still double-count students if they intersect parcels of more than one type. – dbaston Apr 23 '14 at 21:35
  • @dbaston I think I'm seeing that as I test this further... – DPSSpatial Apr 23 '14 at 21:40
  • @dbaston You're right, I pasted your query for edit and didn't edit it the way I wanted it to. – Jakub Kania Apr 23 '14 at 23:40

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.