Liberation and modernization of government legacy data using Django

Well my name is Roberto for those who don't don't know me and for those who do know me I'm Steve Roberto so I'm gonna be This is the last talk. I know you're tired. I know you wanna go bar hopping. I'm gonna be doing my best to keep you awake. So I work for critical because it's a cryptographic uh customer cryptographic creation company we also do software development and we love Django myself and the company We've been backers of many high-profile Django projects like the Schema Migration Project, like High performance Django by by Peter Baumgartner and we are goal sponsors of the Django Rest framework Kickstarter. I've been doing software development for 25 29 years My first claim of fame came by uh helping uh reverse engineer the Nintendo.

So if you ever got fired by uh playing cr uh a sloppy made uh pixelated games in your cell phone, that's on you, not on me In the Django world, I'm known for a few projects, and one of the most uh recent ones who have been uh had a lot of uh visibility is Myang EDMS. It's a document management system done entirely on Django. So how I got into into this mess. In 2013 I was appointed director of software development for the government of Puerto Rico and I had to oversee the creation and use of software in the government. And one of the Uh the projects I was handed was that uh the governor of Puerto Rico had just signed an executive order uh uh ordering all government agencies to start sharing data electronically.

But we had no infrastructure to do that. And this is the problem. This is the scenario I was given. We have uh we had at that time 142 government agencies each of them creating and accumulating data in completely incompatible formats and with no way to share the data Some of the most uh forward looking agencies did try uh to fix the problem on themselves, but because there was no policy, no oversight The end solution was the same. Everybody just kept wasting money doing in uh uh completely uninter non-interoperable interfaces and export of the data. So pretty much this was my reaction.

What is going on here? So I realized we did not understood the problem. So the first thing we did was just make a checklist. What do we need to make this happen? Okay, we need an export and an universally uh compatible export tool where we can take any data, government data, and export it into uh new format like JSON and XML and regardless of what the original file format was. And then we realized that does not exist in the universe. So basically we just had to create it ourselves. We just uh we're uh a new experimental uh software development department for the government. So Let's honor our name. Let's start development developing. And this is why we came up. We we came up with uh Libre.

It's a uh actually a f uh uh a backronym uh to uh create a an engine to free up government data. Libre in in English means free. So it was also kind of a a political a political statement. This is the ego eye view basically what what the platform actually managed to do. We can take completely heterogeneous uh data source regardless of format and uh the place where the data is being originated and just by doing a simple the description of how the data is uh incorporate uh uh a structure we can import government data uh we can do versioning on the government data and we can start hosting also open government data from the same product

because infrastructure is also another big problem in the government. You can have uh you have very few government agencies that have good infrastructure, most of them will collapse as soon as they get uh a hundred concurrent users. Uh we also had to create a a unified query language because our users are now more technical. The this the public is more technical so Some people do want to see just an infographic, but most of our users now just want access to the data themselves to do statistic analysis analysis, mathematical analysis. So we had to come up with a with an unified way so that our our new clientele, uh the developer clientele, could could filter, could select what they wanted. And we had to support as many output formats as it

as we could, not just the original file format. We have to support JSON for for JavaScript development. You have we have to support XML So Django Fresh Framework was very crucial in this part. So now we can have completely outdated government data and it can be used in a lot of different scenarios. So this is now where uh how Libre fits in the whole ecosystem we can have now all government agencies producing data how they know how to produce it and we can now drop in this tool and they don't have to change anything internally how to do things how they how they skip producing this data and yet the data can now be shared, can be used by other government agencies or the general public. And we can start turn stuff very ugly stuff like this. I hate spreadsheet files

they have No kind of validation, they didn't tell you anything, and we can start turning them into beautiful stuff like this that a software developer can use without having to worry about importing the file We can turn completely all this stuff like this. This is a shape file. It is a very bad number and a name for a file format because it's not a single file, it's a distribution of files You can blame SS E S R I for that. And we can turn them into this. We the the tool also had to support geospatial capabilities, which is another big topic in the government. The government has a lot of geospatial uh data that is being uh produced. But it's being produced sometime in outdated formats. In Puerto Rico we we had most of our our maps, our state plane projection in NAT 27

format is a format that was uh standardized in 1927 and we started to move to NAT uh uh NAT uh 83 uh still was not interoperable because it's a is uh it was a a state-centric format and with this tool we can help convert all those data completely transparently into WGS for the 84th, which is a a geocentric, it's a world-centric projection. Now Puerto Rican data can be plotted in in stuff that is designed to work around the world. So basically we are modernizing uh government data. For developers, for example, now they can take a legacy check file from the government and using the Libre platform they they can uh we can render a map and a developer can just capture the map.

uh in in an iframe and you can actually now um incorporate geospatial capabilities in your software without uh having to write code just by capturing a render map from a query you just issued to the platform And you can just start doing stuff like this from data that was originally came from outdated Chay files that were just uh accumulating bit rot in in a government server and and from uh Excel spreadsheets So this is the kind of reaction we started getting from the developers. But the administrators started hating it because they already have this amount of work. DevOps are very stressful people. They they they have the weight of the infrastructure on their shoulders and having them create descriptor

files for the files that they were going to import into the platform was very coming another um obstacle into the platform So that's where uh the Django administration uh tool came into the rescue and on top of that Django suit allowed us to to create this new in uh web interface where the person without no knowledge of how to create a Jamma file. Can describe the file format that they are going to export. So now DevOps, which are sometimes the only technical person in a government agency staff, now he can without having to know uh be a data scientist as a for a software developer, now he can use this tool to start uh exporting its government agency data And uh

we started getting this reaction and this is oh software development is so easy I want to become a developer myself. So that that's how successful the the the conversion to Janglabmin was So the they came from we started that the platform got very popular in Puerto Rico. So we had a company whose name started with M means very small and soft at the end And they said, no, we already fixed this problem that you are reinventing the wheel. We have tools that create web services from all our databases. Now, which ones of you work with web services? Which one of you like working with web services? Nobody, see. So

the problem with web services is if you don't have uh sufficient documentation, have you any anyone of you have tried to reverse engineer a complex type from a web service without documentation It's not possible. So you're very dependent on documentation and web services have become a way to promote vendor lock in. Another problem is standardization. That's why we jumped from WSDL 1. 1 to 2. 0. And there's a 1. 2 and one 1. 3 draft that never made it into the into the the public because they were completely interoperable And the tools that are creating WSDL files, the website description language files, sometimes create uh description files which are not even interoperable between one vendor or the other. So it's it's like trying to to assemble an IKEA

uh table with instructions and bad things tends to happen. And so web services were just outside the door. So the tool had to be a REST-centric uh tool. If they if if people didn't like it, we had to do because there's a beautiful thing about REST. REST and JSON are self-documenting. Even if you don't have documentation, you see this and you know it's a dictionary, it's a key value pair. even as cryptic as the key value is, you still have a rough idea what this is and how to operate it. And because we are using Django Rest framework from the same solution now we can re-export using using Django Rest framework renders

to a different format. And I love this. This is why our company is a goal sponsor of Yango Red Framework. The browsable API allows developers to start playing with the data, to start exploring the data. get used to the data even if there's no documentation for it. So what about a unified query language to be able to access all these completely different data sets? This is the same reaction we got from the company whose name starts with M. They said, oh, we already solved that. There's something called SQL that's used for accessing data where you want to create the recreate the wheel. Because stuff like this, this is the name, this is a web uh the source code from an actual website, uh government website. I'm not gonna say the name.

Shut up government And they're actually concatenating and creating an SQL statement in JavaScript, trusting user input and not doing any kind of sanitation or checks. So I talked to the developer that did this from that company and and I I I w I was gonna ask him if he knew about SQL injections and sanitation. But I said no, I'm gonna ask him even a more interesting question. Do you know who Bobby Table is? And he said no. So that was my answer. So SQL was was out of the question too. Because SQL is not a standard, it's a structured query language. Whoever told you that SQL stands for standard

query language was playing a really cruel joke on you It is not. This is an actual question on Stack Overflow I create I did while creating the platform because I wanted to know how to limit the amount of results in in a in a result set for that query. And it turns out it's not even that simple thing, it's not even standardized across databases. So we ended up creating our own language. It's called Eliquel, the Liqual Query Language. Now another problem with data exporting tools is that you need a software, a server, and a client. So still you get that element of bender lock in. So what we did is We created a RESTful query language. Basically the URL is the query that will give you filtering and selection and slicing

for the data Here we have an example. This is a shape file i polygons from the municipal municipalities of Puerto Rico and if you c the URL is kind of small, but if you see it, I'm asking I'm having to uh pursue two predicates. I'm selling it give me only the shape the polygons whose properties in the name municipio is municipality contains the fragment Gua ignoring case So I get guayinabo aguas buenas caguas. And instead of getting just uh data, I'm telling it give me that, render it into a leaflet now. This this is a very nice feature of Django REST framework The renders can also give you maps or charts or or or tables.

They don't necessarily have to be numbers or or uh serialized data. This is another example of a simple query. This is the crime points of the Department of Police, and we are filtering just for the crimes of Psi 4, aggravated aggression. This is the kind of thing now we can filter, we can start analyzing just by rewriting a simple URL. Because we were using uh Django and Leaflet. We're starting incorporating Django's uh templating system into leaflet uh pop-up and markup language. And now from Django we can start customizing, creating customized uh customized map, and we created a map builder. And we can start Doing stuff like this, I can take a chafed file from one government agency

and start doing stuff like this This is the whole universos of crimes in Porto Rico being filtered by the result Querying from a polygon of a municipality from the Puerto Rico Planning Board. So basically, this is a join between two data sets completely different in two completely different government agencies. And this is a municipality-centric query. And this is a URL that produces that. There's no code, it's just one URL. Looks complicated, but you will see in a moment is actually just Four elements and even with the first two you can produce them up, the last two's are just uh um cosmetic um markup The first thing is I'm telling the engine

what is the the data I want to work with. This is a this is the crime data and I'm telling it Filter all those crimes where the geometry of the crime, in this case a point, falls within the geometry and the minus bracket is a subquery marker. Where the the encompassing geometry is the result set from a simple query to the planning board asking just for the polygon of the municipality called Arecibo. And the JSON path is actually slicing the properties of the of the of the geospatial feature and just giving me the data points. the uh the the the uh map points and then passing that then to the geometry and doing a filtering so this

this this is basically a typecasting during runtime from the URL This is then uh this is telling the engine to render a map, not giving me the data points, and to be able to to see The outline because the map wouldn't produce anything, I'm passing also context to the renderer. Please pay me the outline so I know what I'm filtering what Because uh knowledge of the of the language, there was a little bit of barrier, so we created also a query builder for the tool. And this is where you can start Experimenting, filtering data, you have a preview on the bottom, and you can ask uh you can do stuff like producing the results set as a dictionary list

So it is it it is already processed to be able to be plotted into a chart. You don't have to do any post-processing, for example, in JavaScript, and you can take that as it is outputted into stuff like D3. js and already start plotting charts. Excuse me. And after you have the data you you you want, all you have to do is copy paste the query string. And we can start using doing stuff like this. This is an egocentric, a cell-centric result set of the same. crime map or the same crime data. I'm asking the engine show me all the crimes in a radius from where I'm standing from. And the query is even simpler. I have the same police crime data, but I'm filtering instead of filtering for the result set of a polygon, I'm filtering just for a point.

And because point doesn't don't have area, I'm doing a buffer which in this case, in this projection and zoom level, is just 0. 1 arcs, which correlates to roughly ten miles. So I'm basically telling the the engine, give me all the crime that have happened where a ten mile radius from where I'm standing to see if I'm in a danger of being mobbed or assaulted or killed. And it's a good place for example pseudo a fi uh a a party because the only thing that has happened is just aggravated aggression, just a fist fight. So if I'm gonna park my car I know that it's a good place because Car theft has not happened there in the time frame that this data set was created. And we can do also this. This is called feature analysis. I can see how a crime behaves in regards to a

geographical feature. In this case, this is the PR twenty two, Puerto Rico's biggest highway. And it has been criticized that there's not enough um police uh I don't know how to say this in English, but uh the routing, the the preventive uh uh patrolling. So we did this simple analysis and we can automatically know that at the south to the south of the highway there's basically no crime happening at the uh in the time set which is two years that is what that was collected So there is something happening at the north of the highway that's causing a bigger uh crime rate. I cannot tell you what it is, but now I can give you the observation to do the right questions. And this is the start of the scientific method. So I give you the observation, now you explain why this is happening.

Before this, we had no idea this was even happening. The query to do this is basically the same, but instead of a circle or a polygon, we are creating a polygon runtime just for data points squared. So this is more in-depth now how the tool works. This is now more uh nitty-gritty details. Because I cannot filter or give users the data every time they requested it. It is a very heavy operation. We took a page from DNS. This is a uh write ones. a warm write once read many times so all the processing was moved into the import phase and this is the import phase the first thing we did is do a scheduler because I cannot trust the government agencies to give me the data. I basically have to go and get it forcefully.

That's how great they are. And the next step is to do an origins layer. So that's tell the engine how to get to the data. Once the engine has the data, there's a data data driver's uh layer which is which tells it how to understand, how to process the data. Is it an XL file or REST API? It came from a chain file Then we serialize the data to store it to be able to store it in a database because it's binary data and and for this specific implementation we chose uh base64 encoded Pickle files so that they can be uh stored in the database. Okay. Base 64 pickle file. This this is your Q to squirm to get nauseated. So So it's not glamorous, but for this particular implementation we wanted just to get the data out.

So no MongoDB no fancy infrastructure, just Code one order of magnitude of your worst case scenario. See you never thought you were gonna get a business lesson from a Django con diva stepper center And this is now the the read part. This is where we process the request for the data. Just cookie cutter stuff, Django Restrain Word does most of it. We make sure that the user that is accessing the data has access to the data. Employees or maybe it's a public completely public data. Then we pass it to our own custom engine where the the query is uh split into its part uh uh filtering, grouping, aggregation, and segmentation. The data is then deserialized from the database and rendered in whatever format the user is asking it.

And then we just pipe that to the response uh to this response object that Django Rest framework uh supports. Now that, and that's it, this is a bootmark of the presentation, that's where the project died. After 12 years I became tire of the hate because if if you're a software development in a place where nobody's technical you get a lot of hate. If you work in the government you get even more hate. You get hate explicitly, implicitly and secretly Your boss hates you secretly because you are showing that he's not prepared for the job you are. Your coworkers are hate you implicitly because you are these software developers, you are this wizard of technology, and yet you've refused to to fix the coffee machine. And the public in general, you are just a government employer

employee they're gonna hate you anyway. So after after twelve years of being a lightning rod of hate, I decided to move forwards and Now the company we I work for are very open for community projects and we are actually hosting our own copy of Libre and hosting public government agency in Africa in our infrastructure. So basically we are doing the government's job And with this this data hosted, now we can start uh doing uh really cool stuff like this, like for example, creating dashboards uh using completely uh completely uh disparate data. For example, this is the the section of the datasets of the Department of Energy agency has really not much interesting data, like how much clients they have.

how much energy they've sold, you can see it in a table. But when you plot it into stuff like this, you start seeing patterns, you start seeing correlation, you start seeing behaviors that should not be happening. Like for example in this chart uh you see that the amount of industrial clients the the power company uh for ex uh and I have to excuse me because this is from right to left at this time we had we hadn't even implemented ordering in the engine That's fixed now. So the power company has lost two-thirds two-thirds order industri of their industrial clients and yet their revenue for the concept of industrial in uh uh income never decreased. That's not supposed to happen. So we saw stuff so we started so

stuff like this. This is for example the dashboard of the health department. Puerto Rico is a is a tropical island. We have a lot of mosquito-borne-based disease. Sadly, some people do die, but this diseases are preventable. It's just about making sure that that people get the help they need at the right time. So there's a lot of money allocated into awareness. If you see now, if you when we plotted this and we added the data of asthma, the problem of asthma in the island completely overshadows the problem of mosquito-borne diseases. And when you look at the amount of budget that's being allocated for asthma research and asthma awareness, it's just a fraction of us. mosquito borne diseases awareness programs are getting.

And when you plot stuff like diabetes, it completely crushes the problem of of asthma, even though both are chronic um Diseases as my uh diabetes is a real pro a a really big problem in the island. And when you put hypertension too, something very interesting happened. The behavior of hypertension in the island almost directly correlate the behavior of diabetes in the island So a statistical will bark at this and say correlation doesn't imply causation, but you cannot deny that there is something happening there The government of Puerto Rico also wanted that we have a very big problem in the uh the town halls. The people are leaving the town centers. because of technology, you know they have Netflix and stuff like this.

And the government the the the central government wanted to uh start giving free Wi-Fi in public spaces And they were starting over it to allocate a few million dollars till we got this map up. This is the map of all the municipalities which were by their own initiative giving free Wi-Fi in the town centers. So they were fixing the problem. in the first place and fixing the problems and getting people all already into the public spaces. So just this map save a few million dollars in budget. And this is the same crime map, like I said, created just using just an iframe with just three filters, municipality, time, and type of crime. And when you start running this, you start seeing time-based crime maps, and you see how crime

is more organic than you think. Crime behaves very differently from the time of year, and even excuse me, from the time of day. We started seeing how most crimes have a peak at 2 a. m. And yet house death hits its peaks was at 9 p. m. 9 a.m. and 12 p.m. Usually the times the working class were outside the homes. So even doing stuff as simple as sending employees at different time brackets to have lunch at their houses would have reduced the problem of house theft. This is this was one uh very interesting data. This is the Department of Solid West data And they gave it to me. They were very nice. This was one of the few government agencies that

really cooperated with AFord. And he said, but this is really was worthless. They had that. I'm gonna give it to you and say Just put it right there out there. People are gonna find a way to use this. And they did. This is a project from a hackathon. They actually won best uh mobile app uh web app. It was created by three uh university students uh in less than 24 hours. And it's called geotires. And it's the scenario for for the application is you are just doing internal tourism in the island and suddenly you can't uh You got a flat chart in a place you have no idea you don't know anything about that place. So the application will give you a map using our technology and give you all the places where you can go fix your car before you Become stranded with the metadata so you can call negotiate places and if you click will give you the route to get there as soon as possible.

All this from a data from a government agency that's actually disappeared because that's how unimportant the government think that is from a data that even the government agency that was producing it thought was worthless. Now we can have a commercial uh a product that can resolve a real social problem And this is a snippet of the code, and you can see we are serving, you can see the name of the company in the middle, and you can see that actually they are feeding the application from the the instance, the public instance. We are we are uh hosting, they get the latitude and the longitude via JavaScript from the user, they just filter it. Thank you So these efforts got noticed by one great awesome government agency, the Institutes of Statistics, and they

They they contracted us to start. They have a massive amount of information, a massive amount of data, and very few tools to get to them. So we got in contact with them and the stuff that been happening with that data is amazing. I'm gonna try not to get you off kill I did sacrifice my last copy of Microsoft Office to the gods so yes These are the maps I just showed you. This is using all open source software, the open source Libre Engine and the Cartz

BI and opens on e-dashboard applications. Application I created from Django 0. 6 beta. You can see the behavior of the electric company You can see the behavior of the Puerto Rico grid for the last 10 years and you can see the peaks and the valleys of how usage behaves in Puerto Rico and you can start predicting uh which month of the year the grid is most likely to collapse And the power company didn't do this. They were sending the brigades in June and July. And when they saw the data, we realized September or October are usually the most. So they were paying over time in two months that nothing was happening. and didn't have enough brigades at the times, at the months of the year that the electricity was collapsing. And we also saw a very interesting curve Uh the electric company likes to make everybody uncomfortable and blame the problems of the electric

reading the people that you are wasting too much electricity. Please turn out your likes. But these charts demonstrate That now one point three million kilowatts in ten ten years ago Puerto Rico is actually consuming less electricity now than 10 years ago. So why is the grid continue to collapse? It's not because of use, it's because of a lack of maintenance. So now it can start shifting plane and it can actually point fingers now. So this is just the open source version. And now this is the commercial version that we recreated from scratch. It doesn't use base64 anymore. It now uses my more sane solution And the kind of projects we are doing with it are much more interesting.

Like for example this one Exodus in Puerto Rico is a very serious problem. People are leaving the island and not coming back. At what rate? At an alarming rate. This is all Bureau Transportation data combined with census data and this is the comparison chart. of how many people are leaving the island, the destinations, the airports they're using to leave the island, and the final aggregation. 5. 2 million 5. 9 if you round it up. 5. 2 million people left the island in 2013 and only 5. 2 came back So I have a difference of minus 1,000 residents in an island of only 4 million people.

You can see the problem now And with stuff like this I can start predicting the peaks, the the touristic peaks. So at what times of years the government need to prepare to receive and to make enough accommodations for tourists to see if it can fix this problem. And we can start doing things like this. I'm gonna have to there There is a big problem in Puerto Rico and is it is a political issue that says that the argument is that There is more toxic emissions in places where there is a lower economic uh income.

in the area. And this is a great project to start experimenting with that. It is a map that will I'm getting some latency there. It is a map that correlates the amount of toxic emissions, which companies are emitting them, if they are emitting the correct toxics they are registered for, and the income level of the area compared compared to the mean gross uh uh product uh uh gross debt uh gp GPD of the of the island And you can start doing experiments like this to see how this theory is correct in the island. And you can see that some companies are stin are starting to throw into the atmosphere these nasty chemicals just a few miles behind your backyard.

Nobody knew this until we did this. So that pretty much It's my wrap-up. If you have questions, uh comments, please be kind Setum ,