search

sketch-city / project-ideas

Running list of all project ideas - pick one and run with it!
http://sketch-city.github.io/project-ideas/
89 stars 7 forks source link

311 incident archive search, slice, & export (csv & polygon) #53

Open randy7771026 opened 8 years ago

randy7771026 commented 8 years ago

Please describe your project, the problem you're solving, and why it's important. Keep it brief! Link to further reading if necessary. I am often told by people that they have called 311 or reported something to 311 but nothing happens. Certain populations will not ever go beyond that call to get badly needed services. If data supported it that data might be presented to the agency that wants to supply services to change the high bar of civic participation often required to assure service or be used to predict other needed attention.

fileunderjeff commented 8 years ago

@randy7771026 interesting! @juyeongkim and I were looking at @d-m-wilson's elegant archive on http://dmwilson.info/Home/Search. Is that a good model for what you're thinking?

randy7771026 commented 8 years ago

A blending of what is there and what I see as promised here but under user control: http://www.houstontx.gov/heatmaps/ and here: http://www.houstontx.gov/311/

frank0051 commented 8 years ago

The City does offer some functionality here:

One a monthly basis there is an open data web service that is updated with 311 data: http://data.ohouston.org/dataset/city-of-houston-311-service-requests/resource/4cd50d05-26cf-44cc-91f8-6855ecd2e4d0

You can get BARC data too: http://data.ohouston.org/dataset/barc-animal-service-requests/resource/5eb9d04a-698b-4815-866a-6aeafb09588a

For those that don't want to use the end-points, there is also the 311 dashboards with mapping in the last dashboard: http://performance.houstontx.gov/311Dashboards

There is another civic innovation project that was implemented around Department of Neighborhood (nuisances) that has a front end and also has web services on the back-end that anyone can tap into (although not fully documented): http://mycity.houstontx.gov/nuisancetracker/ (undocumented end-point here: http://mycity.houstontx.gov/nuisancetracker/GetHData.asmx)

randy7771026 commented 8 years ago

@frank0051 nice start

fileunderjeff commented 7 years ago

@randy7771026 any advances in how you're thinking about this project? I think it's really interesting. There is a lot more than can be done with 311 data. Would be interesting to get that going in a Shiny dashboard of some kind.

randy7771026 commented 7 years ago

My efforts on this front have been more along civic instead of technical lines. COH has a nice dash board except that it does not work. I poke at Leila, Jesse and some Don folks about this on occasion. http://performance.houstontx.gov/311Dashboards Turner has given lip service to an escalation of exceptions but it depends on the public flagging the exceptions but without this site working one an only accomplish one offs on requests that they physically go out and look at then keep up with by keeping the number and following up on the phone.

fileunderjeff commented 7 years ago

@randy7771026 i could rebuild at least some of these dashboards in shiny. having a continuous data feed might be a challenge.

@frank0051 how did you refresh those tableau dashboards? Is there something we can do on the backend to autoupdate any apps/visualizations built using 311 data?

randy7771026 commented 7 years ago

I am guessing there is a place where the data is for http://mycity.maps.arcgis.com/apps/webappviewer/index.html?id=b66f25df99b84620a8d7cdb38ec2223b&level=14 Like the one for recent crime http://cohgis.mycity.opendata.arcgis.com/datasets/8d515a90e80840b3bc7a3ada352b0d15_0?uiTab=table&filterByExtent=false&geometry=-99.134%2C28.201%2C-91.8%2C29.644 if only we knew someone smart with a name like Jason, Frank, Jesse that would tell us or if we had a city with a CDO and mayor committed to getting us our data. Like maybe http://cohgis.mycity.opendata.arcgis.com/datasets/2d51d2af86a54ebb8297288916b41574_0?uiTab=table&geometry=-96.28%2C29.563%2C-94.447%2C29.921

frank0051 commented 7 years ago

@randy7771026 - what do you mean the 311 Dashboards don't work? I need to understand what you mean by "don't work" before I can better provide feedback on this front.

@fileunderjeff in terms of data, Tableau Public cannot be automated, just the nature of the free service. I wrote instructions regarding how to refresh it each month. If we wanted something more frequently, we would have to invest in Tableau Server or use another tool. Given Tableau is very low on the development curve in terms of technical know-how I cannot see another tool being used.

@randy7771026 and @fileunderjeff : I spent a significant amount of time before leaving trying to get 311 data open in an open format on the ArcGIS Open Data portal since that's really the only way we have to provide data in a continuous format unless we dump files to CSV. The 311 ArcGIS web map that you link to only has 2 weeks of data. But the Public 311 Data table (http://cohgis.mycity.opendata.arcgis.com/datasets/2d51d2af86a54ebb8297288916b41574_0?uiTab=table&geometry=-96.28%2C29.563%2C-94.447%2C29.921) is probably the best one - you just need to combine it with the BARC data. It's not daily though, it's monthly. It is linked on the Open Data Portal though with metadata http://data.houstontx.gov/dataset/city-of-houston-311-service-requests

The end point you would be looking for is here: Public 311: http://mycity.houstontx.gov/cohgis/rest/services/IT/BARC311_OpenData_wm/MapServer/0 (you can get to it using the link above) but I it looks like it does not contain any data after May 2016. Getting the GIS team to automate this jobs was only some mystery and they would always break and it wasn't clear anyone was actually monitoring them.

The BARC data can be combined with it: http://data.houstontx.gov/dataset/barc-animal-service-requests (just follow the link to the API and then click the source). It looks like this one isn't being updated either - it looks like it did update to sometime through November and then broke. This one we were actually updating daily i believe.

One of the big issues is just a technical issue regarding how large the data set it. There is 350K-400K records each year or something like that + BARC records in a separate system + a crap ton of dimensional column data. And, there isn't a good way to truncate the data that pleases everyone. There also isn't a good reporting environment for us to pull data out in any sort of near real-time fashion so we would have to hit production. The little open/closed map that shows 7 days or two weeks worth of data hits the production 311 system and its a huge drag. We started hitting the daily back-up and that seems to work better. The yearly flat files on http://data.houstontx.gov/dataset/city-of-houston-311-service-requests are supposed to be refreshed daily so perhaps that might be a place to start - but those pipe delimited files are huge. I also don't know if the older year ones are being updated (even though there could be changes in older year cases). I did try to download the 2016 one (it does look like a 2017 file needs to be created) but it was so slow that I couldn't really get it downloaded past 16 MBs.

frank0051 commented 7 years ago

The other thing I would put out there is I don't really know if there is any intention or ability to support the Open Data Administrative Policy based on the history of the last couple of years and the lack of resources available due to budget constraints and lack of technical knowledge in web 2.0 technologies. Given this, I'm not sure how effective the filing of tickets will be.

If there are certain things that the community feels are vital for release that continually break, it might be more appropriate to start a letter writing campaign to the Mayor's Community Team and to Council or to get the press involved. Strong and vocal community support has really been a challenge for the Open Data Program outside of the Hackathon if I were to be honest. This Mayor seems very attune to citizen complaints so if there is a large enough community interest (i.e. several people), he does appear to get involved personally and that does seem to change behavior of staff (at least in the short term).

Just my two cents anyways.

randy7771026 commented 7 years ago

@frank0051 I think you have outlined most of the technical and administrative problems. You guys may be getting me shot here but that is part of being a full on advocate I guess. My little square of my neighborhood when I moved back here was in the process of the huge new sewer system being put in but the safety meetings put on by GNMD and Avenue CDC identified loose dogs and dumping as greater concerns than drugs or gunfire. The 311, DON and HPD Neighborhood Protection Division as single units seem responsive as of course do Jesse and our other guy who is always behind Sly but because I am old I cannot remember his name right now, he is great and was one of our lightning talkers with Morris and Martin, sometimes some names just get lost for a bit. Anyway though each of these units have very active and responsible / responsive individuals there is not a path within the public facing dashboards to follow from the filing of a complaint to a report by an inspector that there is a verifiable problem assigned to a department and that then the property owner is educated and should really not need to be educated again but rather just notified should it happen again and the third time ticketed and if needed followed up by HPD which should also be verifiable by the public in the dashboard. That and the breakdown of systems either not being updated or being down and filing a ticket not being effective.

randy7771026 commented 7 years ago

@frank0051 and @fileunderjeff This is the part that could could get me shot. My little square and the departments mentioned above have those very responsible and responsive people but also run the spectrum right up to gang bosses. A good sampling of city employees are very happy to be active and draw a paycheck with being effective and indeed that secures their jobs and their pensions. In the CMs office Leila has replaced an employee I thought for a while was being effective until she lost it and lost that position. I had wanted the CM or some of the CM staff to just come out and walk a few short blocks with me in which they could view a microcosm of what I see repeated all over the under served areas of our city. Back to the beginning. Jack Valinski is very good at attending neighborhood meetings, listening then going out and filing reports. He once directed me to the 311 map and a place to put in his name and see all the reports filed by him. The problem is when I do that I see about a thousand and no reports of any follow up. 311 when a report from me is made of a neighbor putting out heavy trash at the wrong time says the two month cycle of pick ups is their solution. Leila had Mayra Hypolite came out and CB and I rode along as she filed reports. She was to send me an Excel spreadsheet of those early the next week. I have been following up with her and Leila as well as poking Jack for two months now because if I can get Jack to point out to me again where it was I put in a search for his name and instead put in Mayra's I would have that report which the Excel report would be a poor substitute for. Monday is very busy so maybe Tuesday I will follow up on that again.

randy7771026 commented 7 years ago

@frank0051 and @fileunderjeff many of those reports are being followed up on but I only know that from walking the neighborhood, seeing a big yellow sign and flagging down and talking to an inspector when I saw him down the street.

frank0051 commented 7 years ago

@randy7771026 : so let me understand, it's not that the 311 Dashboards do not work, but rather they are: a) not meeting your purpose (i.e. being able to see the status of an individual ticket); and, b) are otherwise not causing escalation when a ticket breaches its escalation (which is not the purpose of the dashboards).

Do it would seem to be your issue is more so with the monitoring a follow-up process, specifically those related to heavy trash and DON, no?

Part of the problem is there isn't any real system integration. Nuisance Tracker tried to do this (http://mycity.houstontx.gov/nuisancetracker/) but it's very slow because it's on a shitty server and because there are SO MANY open DON cases. The data refreshes weekly though so you should find it useful; best I can tell the data is still refreshing. If you search for property address you should be able to get some visibility. It also has another issue that when there are multiple violations at the same geo-coordinates you cannot get the little pop-up window to appear (https://github.com/frank0051/DON_Blighted_Properties_Hackathon/issues/14). I tried to get some IT folks to work on it, but unfortunately they were less familiar with modal windows and the querying process than I was and didn't have the bandwidth to support.

Another item you might want to look at is something Jim, Eduardo, myself, and the GIS team was able to do before I left: add the comments from the DON IPS system 311 tickets themselves as comments. You might find this map more to your liking: http://www.houstontx.gov/fighthoustonblight/blightmap.html

We did the best we could with limited resource, bandwidth, and technical know-how. I cannot speak to how much of this is still being support as I'm no longer with the City.

frank0051 commented 7 years ago

Oh, and @randy7771026 and @fileunderjeff , I'll tell you a secret, the entire source code of Nuisance Tracker is on GitHub (unless some changes were made after I left, which I don't think so): https://github.com/frank0051/DON_Blighted_Properties_Hackathon/

And, the live API that the site uses (which means it's updated weekly I believe) - as slow as it may be - is fully available (albeit undocumented) - for the public to use: http://mycity.houstontx.gov/nuisancetracker/GetHData.asmx

randy7771026 commented 7 years ago

@frank0051 You are mostly correct. Does not work means it does not work for me. Sometimes because the cities systems are down or not updated but mostly in that to get any meaningful functionality you have to know these things that are not documented or just get lucky when poking around.

randy7771026 commented 7 years ago

@frank0051 and @fileunderjeff Lots of good stuff provided by @frank0051 Hope this discussion goes somewhere and as you know I am willing to do more than just write letters.

Steven David

frank0051 commented 7 years ago

@randy7771026 and @fileunderjeff - I got a message that Public 311 data and Public BARC data have been refreshed and should be updating daily (hopefully) on the ArcGIS end-point - per https://github.com/sketch-city/project-ideas/issues/53#issuecomment-271065335

randy7771026 commented 7 years ago

Thank you Frank. Mayra will be visiting with Cheryl and I for a couple of hours during the second week in February to show us how she uses the Houston 311 platform to cache while out in the field then post when back in the office without it picking up the office as the report address. So far I think there are actually four different tools being used by COH folks and they may not all be gettting into the same place but again they may.

frank0051 commented 7 years ago

@randy7771026 - that sounds about right we multiple tools. Re out in the field: are we sure we're not talking about the Infor Nieghborhood Services system in DON?

randy7771026 commented 7 years ago

@frank0051 We will see what we shall see but I specifically asked her about the Houston 311 SeeClickFix app and since she is planning on teaching Cheryl and I how to use it my guess is the internal tool would not be available to us.

randy7771026 commented 7 years ago

Saw Mayra last night at Castillo and she say definitely the public facing side of SeeClickFix.

frank0051 commented 7 years ago

Well, the SeeClickFix tickets do go into the 311 data; there is a Channel column in the open data that tells where it came from.