Implement data model

[patcon]

Not strictly necessary to start with this, but something is be interested in sorting out. Would likely require insider knowledge and might possibly be something to collaborate with other cities on...

I'm mobile right now, but I might sketch of some notes and resources here later

[dougestey]

Great idea - just this morning one of our devs commented on the fact that we need start & end dates for councillors (for historical possibilities) that I hadn't even thought of.

Is there a nifty way to do this other than listing it out?

[patcon]

Not that I know of. The Sails ORM builds the database from the models, and then we could use sqlfairy on a dump? https://nsaunders.wordpress.com/2009/01/11/easy-visualisation-of-database-schemas-using-sqlfairy/

Obviously easier to sketch em out in person, but not sure how best to share that over the long term. But perhaps it might make sense to do that sketching with someone who knows city hall well, then worry about digitizing it after? You interested @emilyjfan, or know who might be?

[dougestey]

I'm into the sqlfairy idea. For now, I have a pretty clear vision of how the three main models we have - councillors, wards and motions - all relate. I'll draft a visual up using the method above.

[patcon]

We can brainstorm here for now: https://pad.riseup.net/p/city-hall-schema

And here's what emily's group settled on, to star with: https://github.com/CivicHaxx/ok_councillr/blob/master/db/schema.rb

[emilyjfan]

Hey guys! Happy to help. Here's a more visual representation of the database associations that we used for OKCouncillr: http://okcouncillr.cloudapp.net/ok_councillr_associations.pdf.

[patcon]

Yay!

[patcon]

fwiw, we probably shouldn't feel the need to nail this down before getting to work -- I don't want to be responsible for accusations of premature optimization! We can always migrate

[dougestey]

I'm actually pretty happy with the database associations drawn up above by @emilyjfan. Will use this as a basis for now.

[patcon]

https://represent.opennorth.ca/api/

I think this was what Michael Lenczner (that old-hat ppen data guy I mentioned in earlier convo) was referring me to when he said open north had already created a data model for electoral bodies. Might be worth trying to use what they've settled on, as they have tons of experience.

cc: @jpmckinney

[jpmckinney]

Actually, it's http://www.popoloproject.com/ :)

[dougestey]

This is really well detailed and helpful. Thanks guys.

[patcon]

:heart_eyes:

[jpmckinney]

We're currently working on improving the documentation with more examples and better focus on what implementers need. I'd be interested in any feedback we can incorporate in the re-design!

[patcon]

We also came across this: http://opencivicdata.org/ https://github.com/opencivicdata/scrapers-ca/tree/master/ca_on_toronto

Doug mentioned in IRC that it "follows the popolo standard to a T". Any thoughts on its relation to the others resources? Is one better or more future-oriented?

EDIT: haha ok, clearly @jpmckinney is familiar: https://github.com/opencivicdata/scrapers-ca/blame/1abfd80b1cd5df0b255f4c25a6c7fb5db07e1abf/ca_on_toronto/bills.py

Interested on your thoughts on using that, as doug seemed to be leaning toward it. Also curious about your thoughts on upcoming agenda items. Thinking if they're mutable, then it might confuse things to try to load them in... (feel free to link me to existing discussion if this is all passé :)

[jpmckinney]

Yes, Open North is a partner to OpenCivicData, and we control its scrapers-ca repository.

Which other resources do you mean specifically?

You can use the OpenCivicData API (documentation) from e.g. http://scrapers.herokuapp.com/bills/ to get Toronto's agenda items and votes.

I've shoehorned Toronto's Agenda Items into Bills, but it seems to work well enough. Re: mutable agenda items, I think you'll have to cross that bridge when you get to it!

[patcon]

Thanks JP :) By resources, I just meant it seemed both opencivicdata and the represent API could theoretically be used for city-level individuals. Is the idea that opencivicdata is city-level, and Represent API is intended to remainonly prov/fed? (I can understand if those in the know considered them too different to reconcile easily.)

And it sounds like you're down for one of us submitting a PR to get upcoming agenda items scraped into bill objects. Any thoughts off-hand on how to distinguish them from non-upcoming? 'type' array or 'action' hash seem like prospects on the bill object. (Promise this is the final round of questions!)

[jpmckinney]

We scrape with OpenCivicData and import into Represent. Our scrapers are running at http://scrapers.herokuapp.com/ Represent is older than OpenCivicData, and we haven't transitioned its API, but we have an open issue for that https://github.com/opennorth/represent-canada/issues/84 Represent has all the data from our OCD scrapers (fed/prov/muni), plus electoral boundaries and postcodes data which haven't been imported into our OCD instance.

I think you should add an "introduction" action with a date to the bill object.

[patcon]

:+1:

[dougestey]

@jpmckinney, thanks for the great work you guys are doing. It's really starting us off on the right foot here.

Had a few minutes this morning, and now legislative terms and bills/motions have been implemented and hooked up to the db generated by the ca_on_toronto scraper: https://github.com/designcofounders/toronto-city-hall-api/commit/8247ddf78c40b057796f6a337b7164f480f2cd5f