A quick about me, the long journey. Basically these days, I'm an independent web developer, independent freelance consultant, but I've been all kinds of things. Software developer, build engineer, project manager. More recently, Nepal Trekker. I just snuck that one in from last month.

So when you're like a freelance web developer, you just find that from time to time, people find you like community groups and they'd like you to build a map for them. Totally reasonable. They generally don't have a budget at all. And you want to help them,

you'd like to be able to build them something that meets their needs. They start off, they say, we need a map. Often what they really mean is they want a database of some kind, then requirements start to come out. Okay, so it's a map, it's got pins on it. Okay, pins, that means point data, right? Great, so translate it. They'll usually have requirements about what it looks like.

They'll also start pretty vague and then, you know, you talk to them a bit more, and it's like, yeah, it's got to look like this kind of thing. Wait, actually more like that. So the point I'm making here is there'll be some sort of requirement for customization and that will often emerge over time. So I tend to want to build something that's JavaScripty

with the full power of CSS for stuff around the map and a lot of custom styling in the map itself. They'll want it to maybe match their existing branding. Usually not too hard if you've got control over the CSS. Then suddenly later on, oh, it's got to work really well on mobile,

which they never mentioned at the start. Then, oh Jesus, and we all need to update the data all at once simultaneously. Okay, everything's getting more complicated. Oh, and also we don't actually know anything about geospatial data. They've probably never heard the word geospatial. That one kind of emerges on, oh, and also, yeah, we want to be able to keep maintaining the data

long after you've left and moved on to other things, which is actually kind of cool. So basically the situation you end up with is you're building an interactive web map for some people. They're going to maintain the data. You don't have the time or effort to teach them geospatial. It's only point data. They probably don't need a complicated geostack.

So what do you do? How do you build it? Some examples where this has come up in the past, the briefly hilarious Hipster Map of Melbourne, which made page three of our major broadsheet newspaper, The Age. I can build the front end, but it's more interesting

if someone else is actually maintaining the data within the platform and writing all the snarky descriptions. You should check it out. Google Hipster Map of Melbourne, it's still around. It's all, as you would expect, half the places have now closed down because things to the Hipster don't stick around very long. So there's some really amazing descriptions in there.

Or this one more recently, this is running the Good Karma Network for a group called the Good Karma Effects. They have lots of local community groups and they wanted the simple site where you type in your address and it tells you, here's your nearest Good Karma Effect local network to join. Simple idea, right?

Not super complicated to implement, but again, they want to maintain the data. I just build the thing and then I walk away. And this is obviously matching their branding. Or this example, which I built for random hacks of kindness. I think I set a personal record from the time that I met them

to the time I shipped it with a domain name just over three hours. So that's why it's kind of ugly. So the requirements in this one are you're someone who's looking for a doctor and you want them to be LGBT friendly. If you're a lesbian, you want a lesbian friendly doctor.

If you're a trans, you want a doctor who actually has experience working with trans patients. So the thing at the top left is a filter. So maybe you want someone who has experience with both lesbian and trans patients. So you can tick on both of those boxes and then it'll just filter down the whole, like all the available doctors.

And here we've just shown on the map, LB means lesbian and bi experience. Yeah, simple concept, but nothing like that existed. Again, they want to maintain the data separately and they don't want to have to like bug me every time they've got a data update. So how do we do that? Like what's an actual mechanism to construct that?

What's a tech stack that we can use? So in times gone past, the answer was Carto. Carto was great, previously known as Carto DB. Had a wide range of visualizations directly in the platform. Often I could just point people to Carto and if there were somewhat tech savvy,

literally just telling them Carto and that was like the last I heard of them, they can figure it out. If they were less tech savvy, I'd kind of walk them through, set up a thing, leave them to it, it had a great GUI, a table-based editor. So you could either edit in geospatial mode or in table mode.

Put in as many attributes as you want. You could even do post GIS queries directly from the browser, which is really underutilized, but really cool JavaScript API. You might be getting a sense there's a big butt coming. Oh my God, they changed the pricing. The pricing used to be free

and Carto's definition of individual pricing designed for individuals and freelancers for the low, low price of $199 US dollars per month. I can tell you as an individual and a freelancer, that is approximately 10 times outside my price range. I reckon 20 bucks a month of maybe pay.

I mean, $200 a month is just, oh boy. So not Carto. Another potential platform that I've used a couple of times and I've seen in, not actually named, but I've seen in some other slides that people have shown is UMAP, which is actually a really nice collaborative

mapping platform for people who have some basic spatial experience. It's kind of complex, but the complexity kind of matches the power. It's got all these great features. It's really good if you've got tons of layers.

It supports points and lines and polygons. You can customize pretty much everything. You can have this layer is green and that layer is blue except for this individual feature is this one specific color and different kinds of icons and different kinds of interactions. Maybe there's a sidebar or there isn't and custom base maps and access control.

These people can edit, those people can view, edit. It's great, it's really powerful. And of course it's completely free and open source. It's based on Python. It's great, but there's kind of a but here. If all you're looking for is something to edit the data collaboratively with people,

it's kind of overkill because you can't turn off all that style editing stuff. There's really knowingly no API, which is kind of mind blowing sometimes. So you create all this data, but the only way to get the data out of it is to actually log into it, click the buttons and click download GeoJSON.

So you can't really use it as your database. It's not a platform to build stuff on. Falls apart with large datasets because it doesn't use vector tiles or anything. It loads all the data directly into the browser. Kind of concurrently craps out. You'll delete each other's work. And it's a bit too much for beginners.

I tried it with a research group and it wasn't great. Then I had an epiphany. Well, no, that's going too far. I had a slide. Everyone loves spreadsheets. I'm just gonna leave that asterisk there

and you can all in your mind fill in what should be written next to the asterisk. So I use spreadsheets now to power the sites that I build. So in the case of the good karma network, it's Vue.js is the kind of framework for the whole thing, starting with the template, which I'll show.

Mapbox GL.js, which I'm super familiar with. It's kind of my bread and butter for the mapping bit. And then there's Google Sheets, hiding behind the scenes, lurking there where it's dastardly CSV API. So why would we wanna use Google Sheets as our database?

Well, everyone knows it. If they don't know it, they know Excel and it's close enough. It's really, really robust on concurrent editing. It won't fall apart. Supports commenting, supports history,

all these kind of other little features that you wouldn't really think about. It's actually useful being able to unwind when people have messed up your database. Or adding little comments, things to fix, all that meta information, who edited what cell. That's actually pretty useful sometimes. There's a whole ecosystem built around Google Sheets. There are other tools that tap into it. You can add little plugins,

so you can have workflows around it. And Google Sheets has been around quite a while already, and I really get the feeling it is not going anywhere. I would bet a lot of money it's gonna be there in two years. I'd bet quite a lot, it's gonna be there in five years, and I'd probably take some sort of bet it'll be there in 10 years. And that's kind of crazy longevity in this world.

And the CSV API is like the undocumented little surprise packet that makes all this stuff work really well. I have to admit, I didn't actually realize the CSV API existed when I wrote the abstract of this talk. Cool, okay, so the two steps when you're making a Google Sheet as your database

for a mapping website like this is first you share the document itself, the spreadsheet so that other people can add stuff into, which probably all done this before. But then the second secret bit is publish to the web, which is kind of hidden there sneakily under the file menu where you wouldn't expect to find it. You publish it, you choose CSV.

Not too tricky. And you'll get this handy little URL. And when you go to that URL, you know what you get? No, you get a CSV file. It's not a trick. It's not a trick. Cool, so when I'm building anything like this,

I've just built my own little map template. I have to admit, sorry, it's online, but I hadn't really built it for the purpose of other people using it. But hey, go ahead. So that gets you quite far because I was building a few of these things and they're all kind of similar. So it gives you like a Vue.js template and it loads Mapbox and a bunch of other little things.

Then we're gonna need to write some code. Well, as a JavaScript developer, that's what I do. So I'm just including this to show how little code there is really. I use something like D3, the D3 Fetch library. I pass in the CSV URL

and that little bit of code is gonna grab our CSV file when the webpage loads. The next bit is we convert the CSV to GeoJSON and I've written this code so many times. It's sort of not worth having a library to do it for you. So we ended up just writing it.

Essentially, you're just constructing the GeoJSON object in the browser. So a GeoJSON object is gonna have a type, a feature collection. It's gonna have an array of features. Each feature is gonna have a type of feature. It's gonna have a geometry object. And in our case, each row in the Google sheet

is going to represent a point. So it's gonna have a type of point and the coordinates. Here's the bit where we actually reference the names of the columns. So in this particular spreadsheet, the longitude column is called lung and the latitude column is called lat. And we do the sneaky thing where we put a plus sign

in front of those column names and that tells JavaScript to kind of smush it into a number type. It's just a way of doing type conversion. Most of the ways that JavaScript does type conversion suck. This one's useful. And the last little bit-

It sucks though, doesn't it? JavaScript. I mean, it's useful but it sucks, right? You're right. Sucks and useful. And then finally, all the other attributes of the entire row, we can just jam into the properties object so that every point in our map has the full attributes that represents the entire row.

And then finally, having fetched our CSV, converted into GeoJSON, we're going to add it to our map with Mapbox GLJS. So we'll add a source of type GeoJSON and we'll add a way of representing that data using pins. Hopefully we've loaded our icons before this bit.

So this is really like, if you've worked with Mapbox GLJS, this is really basic. This is sort of one-on-one stuff. The main reason I'm showing this is just to say, yeah, that's all the code for the entire guts of this, yeah, the representation of the data. And that actually, yeah, surprisingly brings me to the end of my presentation,

but I have some extra slides just in case anyone needs. So questions. Actually, I might even also show the live demo of the site while we're here and while the microphone is moving around. So- Great, hit me up.

Hi, just to say, great presentation. Like what you just talked about just solves a problem that we currently have with issue we have back home. My question is just to ask about like any new points that come in, for example, if you enter new data into the spreadsheet, does it automatically update on the map as it goes or?

Yes, yeah, if you're using the CSV API, but well, the next time the user refresh their browser, it would automatically load, that data would be taken into account immediately. If you needed it to be more immediate than that, then you could just build that into the browser and automatic sort of polling loop or something like that.

But I haven't had a use case like that yet. Up to date in my world usually means like within a few weeks. But yeah, if you're talking like, you know, sub-second timing, it'd be something a bit different. I'll just ask my questions after to you.

Do you find that people have trouble typing in latitude, longitude coordinates and do you do any like testing that they're in reasonable bounds? Often the data sets I'm working with are small enough that you can kind of go through by hand

and immediately pick out any that are wrong. And yeah, obviously people make mistakes because people are people and they might put coordinates in the wrong hemisphere or something. Or another thing you can do is, have you ever heard of geocoding? Here's a slide I prepared earlier. So if they're really not comfortable with,

you know, if lat-longs aren't a thing they've got, then you can actually use this amazing plug-in to Google Sheets called geocode by awesome table. And they can provide addresses and then you run this step and it will literally geocode those addresses into lat-longs. So that's another really useful thing you can do

for working around, if even that level of geoliteracy is too much, then that's a useful solution. Is that still restricted to a number? Oh, probably. I could probably have a limit like a thousand a day or something. No, no, no, no, no. Oh. I'm just saying, I'm just warning you.

It's possible, I think I looked at a brief, like, just before. Yeah. Yeah, geocoding, oh my God, is a whole world. And it's a fantastic free resource which I can just extend through that.

Yeah. If you need lots of geocoding, then can I recommend my other website called getlong.lat and boy, did I put a lot of work into researching every freaking geocoding plan out there to build this whole, like, comparison tool. Yeah, if you need x number of geocodes per month or per day or whatever, oh boy.

Oh, there's a lot of geocoding plans out there. Oh, so many geocoding, oh, a lot of geocoders. Any other questions? Did you write one of those? No, I'm not that crazy. I also have need for a community-based website with a Mac.

So what do you end up with, just basically JavaScript file in a GitHub? I was totally going to add a slide about that, yeah. So I published the thing to GitHub pages and that's actually built into that template site. It's already got, like, a script in there. These days it's quite easy to just push up to GitHub pages

and you don't need to make a separate branch in the GitHub repo. You can just make a subdirectory and there's a bit of configuration. Yeah, I should probably actually write something about that because once you've done it a couple of times, then, yeah, publishing to GitHub pages works really smoothly. Yeah. Great idea. How do you actually stop people changing the titles

on the top line or is? So because I don't like LNG, I like L-O-N-G and I'm going to change it and now my map doesn't work anymore. That brings me to my other hidden slide. If you went through the JSON API instead of the CSV API,

it wouldn't be dependent on the name of the column and then you could access the column by its position. So it would just be the third column and even if they muck that up, then you're still good. If they reorder the columns, then you might need some extra smarts to try and figure out where have they hidden their latitudes and longitudes this time. If that fails, it might be time for a difficult conversation.

Might be one more. Yeah, we've got time for a couple more. So after you've made one of these for a client and they call you the next day and they ask for support for lines and polygons, what do you say?

Oh, that's great. That's the freemium model. So then I upgrade them to an individual consulting plan. Yeah, we have a different kind of conversation and I send them a proposal. Yeah, so you're probably not going to want to try and jam polygons and lines into Google Sheets. I'm sure you could try it using like WKT

or something along those lines. Another option actually, this is my last secret slide. This is great. I'd probably recommend something like geojson.io. So this is a pretty convenient way of allowing people who again not very spatial to create some basic spatial data.

Not great on the concurrent editing or any of those things, but when someone just needs to create a polygon and send you a polygon, this is a pretty good tool and I just wish it had sort of better storage options. It can save stuff to GitHub. Tom McRite for a while, who I think worked on the original version, was working on a new version called geojson.net.

I don't know where that got to. That was supposed to stop. Yeah, I mean, he now works at Observable. Yeah, he was still working on it for a while. But yeah, anyway, this is one of the solutions for extracting polygon and line data out of non-spatial people.

And I've used it a few times for that kind of purpose and it works okay because it does so little. It only does that one thing. It turns out to be a really useful feature.