search

sfcpc / housing-dashboard

4 stars 0 forks source link

Group PTS permits by (mapblocklot, permit_type, filed_date) #95

Closed rajpara11 closed 4 years ago

rajpara11 commented 4 years ago

NOTE: This issue is just a stream of consciousness dump as I work through this issue in the data, actual work that has to be done as part of this has been filed as separate issues.

Alton brought up in our 2/13 meeting with Planning that he uses this method to group permits from PTS that are related to the same 'project'.

This means he assumes there is only 1 project per unique mapblocklot+filed_date combo. Ideally we should just mirror what he is doing (maybe) and do the same thing. I'll run some queries on PTS and see if I see any issues with this approach.

rajpara11 commented 4 years ago

Ok I was able to reproduce what Alton was doing with public data. What I had to do was

  1. Associate mapblocklot data to PTS by joining against Parcels data set
  2. Group all permits where filed_date, mapblocklot and proposed_use is the same (I filtered on permit types 1-3 here to make it quicker)
  3. Remove dupe permit_numbers (dupe permit_numbers always have the same number of units so we can jsut choose one. These are apparently revision permits in internal PTS maybe?)

Doing this method I was able to get the project Alton showed us in the 2/13 meeting with block lot 3456002 that when you sum up has 251 units. Unfortunately I can't find this project in other data sources easily so not sure how to verify that this actually gives us a correct number. I'll mess with newer projects and see if I can verify data another way

rajpara11 commented 4 years ago

Alton's updated email says:

1.  Associate mapblocklot data to PTS by joining against Parcels data set (translates block lot to map block lot)
2.  Group all permits where filed_date, mapblocklot and proposed_use is the same
3.  Filter permits that are permit type 1, 2 or 3 with net_units > 0
4.  Only take the first when there are duplicate permit_numbers (first of series of building permit numbers)
5.  Remove permits with status ‘withdrawn’ or ‘cancelled’