Episode #49 — In this interview with Mike Dolbow, CEO of GeoHipster we walk through a GIS case study
This podcast is sponsored by OpenCage Geocoder
OpenCageData does not make maps or provide routing information, they focus on turning address or place name to coordinates and coordinates into addresses or place names and they are amazing at it! If you need help with geocoding OpenCage Geocoder is what you are looking for.
We made some socks for you!
You are more than welcome to reach out to me on social media, I would love to hear from you!
In Conversation
The Problem: Schools, Boundaries and an Annual Update
Daniel: Hi Mike, welcome back to the show. It’s an absolute pleasure to have you on again. I’m saying welcome back because you’ve been here before — we published an episode on the 27th of November 2019 called Don’t Just Make The Dots, Connect Them. I really liked that because it gave the audience a look into what the world of GIS is like when you’re out there working in the industry. We’re going to take a similar approach today: we’re going to use this interview as a case study. But first, could you give us a brief overview of the situation? Who are you, what’s your job title, who are you working for, and what sort of problem are you trying to solve?
Mike: Thanks for having me again. My name is Mike Dolbow, I work as a GIS coordinator for a department in the state of Minnesota. Everything I mention today is my views, of course, not the state of Minnesota’s. My job working for the Department of Education is primarily to manage spatial data and connect to other data systems in order to administer programs that affect various education programs throughout the state. School districts in the state of Minnesota are completely independent — they operate on their own budgets, their own systems and so on — but they get aid from our department with things like administering federal funding programs. So my job is to make sure that everybody knows where the schools are, where the libraries are, where the school districts are, and that sort of thing.
Mike: This case study we’re going to be talking about is basically an annual update of all those schools and school program locations, the attendance boundaries that affect those, and the district boundaries that those individual attendance boundaries make up. The district boundaries essentially encompass the attendance boundaries — you could think of the attendance boundaries as nested within the district boundaries. So really, very basic classic GIS data management, a lot of point and polygon information. If you’re out there just getting started in GIS, or you’re working for a different sort of vertical, you could think of this as maybe stores and sales territories, or facilities that are inspected by various inspection territories. So I’m going to be talking a lot about schools and school districts here, but this type of case study can apply to a lot of different situations.
Who Uses This Data and Why
Daniel: You did a really good job of setting the scene there. Perhaps we could go a little deeper now. We understand we’re going to be updating some data — could you tell us how this data is going to be used, what’s going to be the effect of these updates, and why it even needs to be updated?
Mike: Absolutely. This data is used both internally and externally. Internally within the Department of Education it’s primarily used to administer funds for federal lunch programs, or federal food programs run basically by the Department of Agriculture — and I believe the federal Department of Education is involved. So it’s basically making sure that those funding mechanisms get distributed fairly equally across the state. There are all kinds of spatial challenges out there.
Mike: For folks externally — the Department of Agriculture is a place where I used to work. I know that when a pesticide spill happens out there in the world, and folks hope that doesn’t happen but it does happen, if a school is nearby or within a certain distance, that spill gets ranked higher in terms of the risk of population exposure, because kids are more vulnerable to exposures like that than adults. That’s just one example. I know lots of local governments use the data to help look at challenges in transportation, to validate what their own records are on school district boundaries, and things like that. So there are a lot of different uses, both externally and internally, of this data.
Daniel: A couple of things really stuck out in my mind, having been in a similar situation before working as a geospatial consultant. External and internal parties need to see this data, so immediately I’m thinking there’s some kind of web service involved. Obviously there are other ways of distributing data, but that would be a go-to thing for me — put the data in a database, make the join, and distribute it through a web service. Was this an option in this particular use case?
Mike: Not just an option, but actually an expectation. One of the things I want to make clear today is that just because there’s one way to do something doesn’t mean it’s the only way. My predecessor was somebody that is very well known in our community — even won a lifetime achievement award, he just retired. So big shoes to fill there for me, and also big expectations. He did his work really well, did it in a slightly different way than I did it, but the end result is published, shared data both in file-based format and service format.
Mike: We have this thing called the Geospatial Commons, which is something I’ve worked on for years and I’m very proud of. It’s a single place for people to go and get spatial data in Minnesota. It’s very well put together, data’s well documented. Not everything is shared as a service, but quite a few things are shared as services. So I’m doing both.
The Architecture: SQL Server, ArcGIS and the Geospatial Commons
Daniel: It’s always interesting when you start a project like this and there are those upfront expectations — okay, last time we did this it looked like that, and people want to see the same if not better again from you. So maybe we could give people an overview of the architecture, and then dive into some of the database magic that you did.
Mike: Absolutely. From an architecture perspective, what we have for tools available to us is primarily Microsoft SQL Server as the primary backend, that source of truth database — we could call it the organization database. The GIS data is on SQL Server as well, unfortunately in kind of its own silo in a way, but merging the two is not difficult with SQL. So there are some good structures in there that really make this job a lot easier than if it was entirely siloed, or entirely in file-based situations. We’re lucky of course that we have Esri’s ArcGIS Server that can connect to that backend and produce web services, and then we have this Geospatial Commons as our portal for discovery. We also have access to ArcGIS Online.
Versioning Services Instead of Updating in Place
Mike: Here’s one of the things I did a little bit differently to meet those expectations. My predecessor shared the services on our locally hosted ArcGIS Server, and just sort of told people to use those and go run. That was publicized. When I looked at that I said, okay, there are a couple of issues there for me. One is, I don’t know what the usage is going to be when I just tell everybody to go have at it, and so I’m not sure I can support that long run sustainably alongside supporting all my own programs and applications.
Mike: What I did was I said, I’m going to take this yearly update of these three main layers — school programs, attendance boundaries and district boundaries — and I’m going to load them into ArcGIS Online as a single year’s cut, so to speak, and make them into feature services there. The data’s not that big — I don’t think any single dataset is more than half a gig or something to that effect. When we talk about school programs we’re talking 5,000 records. Not a huge data cost for that data hosting. But then I can advertise those services and tell folks to connect to them, and I can let Esri’s architecture in the cloud scale as necessary with the ups and downs of the demands there.
Mike: What goes along with that premise is the idea that I’m going to be versioning these services as well. So next year when I do an update, I’m not going to update those services in ArcGIS Online in place — I’m going to publish a new service. For example, what you find now if you go to the Commons and connect to the service, it says school programs in Minnesota for the school year ending in 2020, so school year 2019 to 2020. Next year I’m going to keep that service in place, I’m going to create a new service, and then I’ll let people know — hey, the new service is available, go start using that if you want to look at the current stuff. I’ll probably keep maybe three or five years’ worth of services up so people can move over time. That way it’s more like versioning against an API. It’s a little bit different than what people have seen before, where they could just set it and forget it. It puts more responsibility, but also more power, in the hands of the users who are either developing applications for this or just putting it into their ArcGIS projects or their QGIS projects.
Daniel: I’d like to ask a couple of questions about your use of ArcGIS Online. Oftentimes I’ve heard people say it was really difficult to forecast the cost of using ArcGIS Online, and my understanding is that you pay for reads and writes. I heard you say these are not huge datasets, so I’m assuming there’s not going to be a massive amount of traffic. Does that mean you come under a certain read-write level, where the first five or ten thousand is free and then you start to pay? Is that why you don’t really need to think about the budgeting side of this?
Mike: I have to be careful here, because some of what we have available from Esri is from the enterprise licence agreement we have in the state, so I can’t reveal too many details around that. But I actually don’t think it’s atypical that we don’t pay or don’t cost any credits for reads — I think that’s pretty standard for their cloud offerings. You do pay transaction costs — credits, you know, call them danger bucks if you want — when you do an upload, and that hosting over time, depending on how much data it is, you incur transaction costs for that hosting.
Mike: So you wouldn’t want to load up 100 gigabytes of data and then redo that every week. If you had to do that, that’d be ridiculous, you’d incur lots and lots of costs. But if you’ve got a small dataset like this and you upload it once, that upload is done. I’m paying less than a credit a month, I think, for the storage costs, and I’m not paying anything for the reads. So anyone that’s consuming that and reading that from me, I’m not incurring any charges. Now if I were editing it — if every time I added a new school I went in and did a transaction in ArcGIS Online — I would incur costs there. But I’m not. I’m saying this is a cut, this is a yearly report, here it is. I’m doing all those updates internally on my own stuff. What is on ArcGIS Online was the cut at that date when it was published, and that’s it, and next year there’ll be a new one.
Why Publish Three Separate Services
Daniel: Why did you decide to publish these as separate services? I can see some pros and cons around this. Another way of doing it might just be to merge both those datasets, publish them, and let people filter them themselves. I’d be interested to hear your views.
Mike: There are definitely different ways to do this, and my predecessor did have, I think, a single service with various different breakouts of the school programs, and a single service that had both district boundaries and attendance boundaries. I looked at it as, well, these are three separate resources on the Geospatial Commons — on the Commons a resource is a package of data, so there was already that sort of system laid out.
Mike: The school programs themselves are pretty complex. There are a lot of different ways to filter and break out that data. It’s not just all public schools in the state, for example — it includes private schools, it includes colleges, it includes libraries, adult learning programs, you name it. There are a lot of different programs in there, and there are classifications within that layer to further break that down. Sometimes people just care about that data. For example, that Department of Agriculture example I was telling you about earlier — they don’t care what school district the spill is in, they don’t care what the attendance boundary is for that area. They just care that there’s a vulnerable population nearby, and here’s how we can find that vulnerable population, through school information. So that’s a really complex layer that to me justifies publishing on its own, for people that are really interested in that detail. And then the other two layers make sense to publish separately because they have their own audiences and their own use cases that are a little bit simpler.
Daniel: It’s really important to have the audience in mind when you’re doing this kind of work — who is going to be using this, how can I best serve them. It really feels like that’s what you’ve done, and that’s the reason you’ve published different services the way you have. Another really important thing about what we’ve been talking about is the fact that you couldn’t choose your architecture. You had certain things in place already. You couldn’t just rip all the data out of SQL Server and put it somewhere else because you wanted to use another kind of tool. A lot of the building blocks for solving this problem were set — you couldn’t just change them around. I think this is pretty typical when you’re working with geospatial data: people will have the data in the tools they’re used to using, and the environments they’re used to using, and you just have to work around them.
Let the Database Do the Work
Daniel: Can we move on now and talk a little bit about what you did in the database to make all this work together?
Mike: As I mentioned there’s this organizational database that has been in place for a long time in SQL Server, and the districts themselves, the programs and the administrators within the department have a number of rules, incentives and maintenance routines to keep that data updated. So for example if a school district changes their website URL, they want to make sure that’s changed in there. If they have new contacts for a new principal or a new administrative assistant, they keep that in that database.
Mike: My predecessor would basically maintain a silo of just about all that data. I looked at that and said, well, there are a lot of demands on my time, I’m going to have a hard time keeping that sustainable — and the source of truth for that data is in that organizational database. I’ll just join to it. So I made sure that all the programs and all the attendance boundaries and all the district boundaries had the key identifier that was important for linking to that data. It’s an internally managed identifier. So I did joins essentially — taking the spatial data, starting with that, using the identifiers, and then pulling across from the organizational database, through inner joins primarily. Why should I type in URLs in all the spatial data when it’s already maintained someplace else? So this is really fairly straightforward joins, fairly straightforward SQL.
Mike: You talked about having to work with what you have in place. Most of my database experience before this job was in DB2 and PostgreSQL, but I couldn’t just take all this data and shove it into PostgreSQL — that didn’t make any sense either. It didn’t make any sense to create a silo. Many of the skills I learned querying those databases were very applicable to SQL Server. I just had to learn a few quirky syntax type things, which my IDE helps me with amazingly, and I was up and running. I could do those joins, I could do those selections, I could create data that was much more quality controlled because of those skills.
Mike: Going back to GeoHipster, I think one of my favourite recent interviews in particular was with a local faculty member, David Haynes here at the University of Minnesota. He said: if you learn the backend, you’re viable forever. I’ve really found that to be true, and good advice for folks coming up. You can use Python and crazy ETL schemes and FME — you can use all kinds of tools for moving data from one space to another. But in a lot of cases I’ve found that when you take a database that’s optimized for maintenance, that’s not typically what the GIS end user wants. They want a flat file. And there’s no way to design a database to meet both those needs. But you can design a database that’s optimized for maintenance and then create SQL views or routines to spit out a report, to spit out a published dataset that is really useful for users. That power saves me a lot of time, and that was kind of the primary thing that I did differently from my predecessor.
Daniel: I really like what you said there about databases, and I hate to say it, but I really like your enthusiasm for it as well. Centralizing data, having it in one place, and having the documentation in the database itself — and when I talk about documentation I’m talking about the views and the joins that you’ve made. They are in the database, anyone can go in there and have a look, and you can update them in one place and that’ll change the flow of data, that’ll change the filtering of the tables that you’re exposing to the world. In terms of maintenance anyway, that’s such a better solution than perhaps what some people might think of as automation. They might think of FME — this is a great tool, no question — Python is an amazing tool. But I think you can very quickly paint yourself into a corner when you start to build this web of systems around data, pulling it from different places and magically joining it together through Python or some other automation tool, and then spitting it out as flat files somewhere else that then get picked up by something else. It sounds to me that what you’ve built is going to be a lot easier to maintain in the long run.
Mike: I think it’s important to make a distinction about the situations too. If you are someone that’s aggregating data from say 20 different sources, lots of times you don’t get to have direct access to those databases and those choices. In those cases Python or FME can make a huge difference. In my last job one of the things I was really proud of was inheriting a big Python code base merging parcel data from 87 different counties in the state. I’m actually still doing that in my current job, until my former organization is ready to take it over, which is probably going to be pretty soon. But that was a big Python exercise because we don’t have direct access to those county databases. We have access to their open data sites, if there are any, or what they’ve provided to us individually as shapefiles.
Mike: So that’s a completely different scenario from the one I stepped into in my new job, where the organization has a lot of data that it is maintaining on its own — a source of truth, I don’t have a better word for it than that — internal systems that they leverage, and all these different school districts throughout the state contribute to that database and report through it. It’s a structure that is in place that it only makes sense to leverage. The power of SQL to be able to help you do that is almost endless.
Daniel: I think what you’re getting at there is that the tool depends on the job — it depends on the problem you’re trying to solve. But it’s really important to point out that you inherited that Python code base and you’re still working on it, even though you’ve left that organization, because it’s a critical piece of infrastructure and sometimes you can’t just hand it over. I’m willing to bet that the person who takes over from you, if you move on one day from your current position, will have a much easier job taking over and maintaining what you’ve built here with Microsoft SQL.
Conventions, Comments and Documentation
Mike: I really liked what you said about documentation. That’s the great thing about code — whether we’re talking about that Python set or the views that I’m building for these spatial data joins, that code is replicable, it’s readable. Someone can look at it, and if they’re skilled in Python they can read through it. There are lots of great comments in that code base, which is huge self-documenting information. So instead of having to take screenshots of 20 button clicks that you had to put together, you’ve got comments in your code that say this is what this does, and if it breaks this way, here’s how you go fix it.
Mike: The same with SQL. Every view that I build — I came to the space and two high quality DBAs were there for me to ask questions of, and to say hey, I’m going to build a view to get this job done, what are the conventions you guys have for this? And they said, here you go, here’s the naming convention, here’s a sample, here’s where we put our comments, here’s where we put dates of when it was created, who created it, modification lists and so on. And it’s all there in the view. It’s all there to be able to say, this is why we did this, this is how it’s done, this is where you go get those structures and what it accomplishes. So it’s replicable, and someone can come in and say, oh okay, there’s a new view now, or there’s a new table, or there’s a new tagging structure in the database that I need to leverage. To me one of the biggest appeals of coding, wherever you’re coding, is the repeatability of it — to make it almost scientific, to be able to say, yeah, we can do this again if we need to change it.
Daniel: I’m 100% there with you. I would just like to highlight what you said about documentation again. I don’t believe there’s any code out there that’s self-documenting — I think people need to write instructions. But you made an incredibly important point there: a lot of organizations where people are working together as teams have certain ways of doing things, certain ways of writing code, they put them in certain folders in certain structures, and documentation looks like this. That’s something people don’t really talk about, but that is a huge part of making systems that are able to be maintained and that are understandable by other people — following that standard set out by the team you’re working with.
Mike: I think that is really big. And no place is perfect with documentation — you’re right, there’s no code that’s perfectly self-documenting, you really have to be deliberate about writing in comments. The place I’m in now, it’s funny, there are some very good standards in place in some spots and not so good standards, or a lack of standards, in other spots. So when somebody comes and joins something new I do recommend that they try to find those conventions where they exist, but don’t get hamstrung, don’t get completely blocked if there’s no standard — because no place is perfect at this. No place is a hundred percent regimented and routine, where everything’s written down and everything is fully understood by everybody and there are constantly updating GitHub repos with things that are changing. At least I haven’t found that place yet.
Mike: I’m sure there are some really well-run organizations that get that to 80%, but in my experience typical organizations — medium to large size bureaucracies — in a lot of cases it’s more like 50%. So we’ve got good conventions around 50% of our work, and the rest is unwritten rules, verbal notes, or no convention at all, and you kind of have to blaze a trail and make things up on your own. That’s part of the challenge of technical work, figuring out how those things fit. But when the conventions exist, it’s tremendous. If some kind of major disaster happened to all the tech people in my organization and somebody else had to come in, they would find quite a bit of conventions in terms of database work and documentation and code repositories, to be able to discover patterns and pick up where we left off. That just makes things — it’s the grease that makes the gears turn, and it keeps them turning.
Daniel: Completely agree. I think structure is really important. I don’t think any organization or team has the perfect structure, and there’s always room for improvement. If you’re starting at a new organization it’s important to take the time to learn the understandings that team has — okay, we do things like this. And then once you’ve been there for a little while I think it’s really important to start questioning things as well. Why are we doing it like this? Could we do it like that? Could it be better? Oftentimes organizations that have been doing the same thing for a long time haven’t thought about those things for a long time either, and they just do it because that’s the thing we’ve always done — which is not necessarily the best way of doing it.
Mike: Absolutely. That’s a really bad situation to get stuck in, where every time somebody asks why, the answer is “this is the way we’ve always done it”. That’s always a blocker for innovation, for creativity, for progress. In contrast, going back to those database views and the DBA that showed me those pieces — I looked at what he gave us, the naming convention is very simple, just “uvw” at the beginning, so it’s just a short prefix, and then I could do what I wanted with the rest. The rest of the convention was just laying out the series of how you construct a view and where the comments were put. When I looked at that I didn’t have to ask why. I looked at it and said, that makes perfect sense, it’s not overly onerous, it’s not especially difficult for me to comply with, and I see the space where I get to put in my notes for myself and for anyone coming after me. So it’s a perfect kind of standard — it’s not really even a standard, it’s a guideline, it’s a convention. If you build conventions like that, that give people some basic structures to work with, some basic guidance on where to go, but aren’t overly restrictive, it can really set you up well for your future work.
Tools, a Blank Slate, and What He’d Do Differently
Daniel: I’d like to shift gears a little bit now and talk about the tools that you used and why. We mentioned earlier that the tools to use were kind of dictated — you’re in this certain environment so you use those tools. Could you briefly describe what your ideal situation might look like? Given your skills, and if you had a complete blank slate to start from, how would you put this architecture together?
Mike: That’s kind of two different questions — blank slate how I’d put the architecture together, compared to my skills. Because my skills actually go against how I would start from a blank slate. Primarily for cost, and looking out for being a steward of the public trust and of public funds, if I were starting with a blank slate for this situation I would start with a PostgreSQL database, I would start with PostGIS, and I would manage that data with QGIS. Even though I don’t have as high skills in QGIS as I do in some of the Esri spaces, I know that it works really well with PostgreSQL and PostGIS, and I know it’s got plug-ins for things like QGIS to GeoServer, so I can publish into that space in that stack, and I can spit out shapefiles and GeoPackages for publishing. So if I were starting from a blank slate, that’s what I would have done.
Mike: But I’m not starting with that. I come in, and SQL Server is kind of a new space for me, but it didn’t take me too long to pick up. The Esri suite — ArcGIS Desktop and ArcGIS Pro — both interface well with SQL Server, same with ArcGIS Server. I did use ArcGIS Desktop primarily for this. I definitely have 20 years of experience on that hill, and it’s hard to get off of that hill. It’s really easy for me — with all of its quirks and warts, ArcGIS Desktop, I’m extremely familiar with that platform and I can manage data up the wazoo. I’m not as familiar with ArcGIS Pro, I’m probably going to start moving into that space in the next few years, but I’m also going to start trying to use QGIS for certain tasks. Because the less I use the Esri products, the easier it is for me to justify moving off them in the long run, and diversifying the stack, and being able to say things like: if I lose access to ArcGIS Pro, or if I have too few licences, I have an alternative I can leverage.
Daniel: There are a couple of things I’d really like to highlight for the listeners. Firstly, how easy it would be to create this stack from open source — Postgres, PostGIS, QGIS interfacing with that, and distributing data through GeoServer. Done. All open source tools, tested, stable, amazing, and anyone can do this. Another thing I heard you say which I think is equally important is that idea of diversification. You understand how to use a lot of different tools, and therefore you can easily come into a new environment and quickly understand how things work, how they fit together, and get up and running. If you’ve worked in ArcGIS for a long time, of course you can take that conceptual understanding of what tools are doing in the background over into something like QGIS. But it’s really important to have that hands-on experience of using different tools, understanding how they work, and then when you approach a blank slate you can say, oh well, I know that this particular tool will function really well here, with these pros and cons, I can put together this stack.
Mike: It is, and really, as technologists we have to be ready to learn new things and to take on new tools and figure out what the situations are when we get there. When a carpenter shows up to a work site they’re not just bringing their hammer — they’ve got other tools that they use to get the job done, and sometimes they’re going to be given tools by the worksite, by the customer, to say here’s what we have available to do this work.
Mike: One tool I didn’t mention is my favourite new tool of the last three or four years or so: Microsoft’s Visual Studio Code. It’s a free IDE essentially for writing code, and I’ve used it to write and maintain SQL, Python, HTML, CSS, JavaScript — just about everything that I need to do. I’m sure it can be used to write things like backend scripts, Node and things like that, and I think it can be used for code compiling like C# and Java, but I haven’t seen it as much — it’s not quite that in-depth of an IDE. But for scripting and SQL it’s great. I got that installed on my machine when I got to the space, I connected to the SQL Server, and there I could browse the database right inside Visual Studio Code. That was tremendous — to be able to browse the database and see where the tables are, see where the views are, see what the columns are within those views, write some queries and understand how that data was there. It’s a really good tool, and that’s one that I’ve learned in the last few years. It’s not something I was familiar with 10 years ago.
Daniel: A tool like that, which can be applied to lots of different kinds of code — that’s fantastic. Instead of hopping from one environment to another depending on which language you’re using, you can save so much time with that. One of my final questions: now that we’ve been through the case study, we’ve talked about what the problem was and how you solved it and the tools and architecture involved — looking back on this, is there anything that you would have done or could have done differently?
Mike: Definitely, there are a few things. One of the things we haven’t really talked about is the spatial SQL. SQL Server and a lot of these enterprise databases have spatial functions in them for interacting with geometry, and that’s tremendously powerful. For example, with each of these school programs — if a school program is in district X, if it’s a public school it’s probably operated by that district. But if it’s not a public school, or even if it’s public but not directly connected to that district, it might not be operated by that district. Lots of people want to know what district is it in, what county is it in — and that’s point-and-polygon lookups. You can do that in ArcGIS with an overlay and make a cut. I didn’t do that. Instead I leveraged that power within SQL Server to do those lookups directly, to be able to say okay, I’m going to derive the county column based on the spatial information of that program and the counties that are out there, I’m going to derive the school district that it’s in — that’s a separate column from the organization that runs it, sometimes — and I’m going to derive that with spatial lookups and spatial queries.
Mike: So that’s what I did this first time around, and I’m going to double down on that next time. Next time I’m going to take it even further. For example those attendance boundaries — that’s the core boundary maintenance that I do, and those are nested inside the districts. So instead of manually dissolving those attendance boundaries into a separate layer of districts, I’m probably going to try to derive full districts by live merging those attendance boundaries with spatial SQL functions, which I already know are capable — I already have them working in views. That’s going to save me one more step.
Mike: I’m also working with one of the main developers on the organizational database. We’ve talked about this, we’ve agreed on the approach, we just haven’t executed it. I want to sync up more, so that when address changes take place in that database I get a notification, so that I can manage on the geospatial side. They have lat-long columns in the organizational database, but they’re not really being maintained, and so we want to sync those two things up, so that the organizational database is the source of truth for everything except for spatial, and my database is the source of truth for the spatial pieces. So when I make a change on the spatial side the lat-long gets updated on the organizational side. That’ll open the door to making other spatial reporting even more accurate. The reporting pieces on that database contain counties and congressional and legislative districts, and because those lat-longs aren’t really being maintained very well, those things are slowly getting out of date. We’re going to face a big redistricting in the next year or so, so we’re going to have to redo all that stuff. My goal is by that time to have the spatial side synced up with the organizational side constantly, so we get more accuracy in the long run.
Mike: The last thing I’m probably going to do is — my predecessor really did some manual manipulations to classify individual school programs as elementary school versus middle school, versus this is where the district office is, and this is the type of library it is. I’m going to try to align those more with the internal standards. That’s going to take some time for the users to get used to, but that’s going to make for better maintenance over time as well. So three things really: double down on the spatial SQL, sync up more with the organizational database, and adjust the classifications.
Daniel: I’d like to dive into those updates you mentioned, because you mentioned updating data based on actions taken on the database itself, and you mentioned notifications. For me I’m immediately thinking trigger. Is that what you’re using — an SQL trigger that’s going to be fired every time an update happens and send you a notification? Or what mechanism are you going to use?
Mike: I’m not entirely sure. It’ll probably be triggers. The developer who maintains the tech side of that database is likely going to be the one to execute that, so he knows the end goal and he’ll figure out how to do that for us. Luckily for us, we’re talking 5,000 or so active programs in the state, and so there are maybe 20 or 30 address changes in the database a month — it might be even a week. But that’s the scale we’re talking about. We’re not talking about thousands of changes a day, thank goodness. That scale is important to remember, because if it were thousands of changes a day then this idea I have for getting those notifications just wouldn’t work.
Mike: What it will do is he’ll generate, probably through triggers, a daily report for me that says here are the address changes, here are the organizational IDs and the names and the address changes that were recorded in the database over the last day or the last week. I’ll look at those, and I’m anticipating it’ll say old address, new address, and I’ll be able to quickly glance at that and say, oh well, in that case they just fixed the typo. In that case they just added a suite. In that case they didn’t have a nice address, they said County Road B and they should have said County Road B West, so they added the west — okay, in that case I don’t really have to make a change. But if I see that the house number has changed, or the house number and the street have changed, I could say okay, that means this program moved. Or in a lot of probably more frequent cases it’ll be: here’s a brand new program that I need to be on top of, or here’s a program that’s no longer active. So those will be additional notifications I’ll have to get. I do know that those components are already audited on his side of the database, so he has a log of those changes. That’s probably what the triggers do, is keep those logs updated, and he’s probably just going to report those logs to me on a daily or weekly basis.
Daniel: Hey Mike, I really want to thank you for coming along today and sharing this case study with us. My hope with this is that someone out there will listen to this one day and go, wow, okay, here is someone who’s showing their work — someone who’s brave enough to say I did this, it might not necessarily be the absolute perfect way of doing it, but this was my approach, and then we can all learn from that. Before I let you go, where can we go to reach out to you if we have questions?
Mike: If you’re someone that’s more interested in, I want to develop myself professionally, I want to learn more about this stuff — I definitely recommend that David Haynes interview on geohipster.com, or any interview there. Really good insights into unique people in the industry. And then my Twitter handle, @mmdolbow — you can tweet at me, I usually reply to those tweets, my DMs are open there. Certainly get in touch with me through that, or through the GeoHipster Twitter account. I really appreciate this opportunity to talk about this stuff. I really think it touches on a lot of things that I care about — being able to leverage databases and understand the power there, publishing data, sharing data with folks, and sharing our expertise. I really hope this episode has helped people get an insight into how I’ve done my work and how they might be able to solve a similar problem in the future.
Daniel: Thanks again Mike, much appreciated.
