MultiPlanetScale

🫡 thank you!

You're kind to say so. Thank you so much Zac! Forwarding that last part to my boss

Also, do note that these values are not universal. It depends on where your point of interest actually is located. Specifically how far it is from the equator.

> Still learning new things every day. Keeps the job exciting. ;-) Honestly this is such a good perspective. Thank you for the thoughtful comment

We've only just begun 😎 Thanks for the kind words!

Thank you! Trying my best 🫡

I feel like every developer has been asked to do this at some point!

Absolutely! On my list. I feel like this video could've been an hour long 😅 More to come

@@PlanetScaleI store my lat and lng as GEOMETRY, but I still pull them out using X() and Y() to do some work. video wanted!

These are a ton of work, so I really appreciate the kind feedback!

Thank you! I really enjoy teaching

Thank you! We'll keep 'em coming 💪

Haha that's the goal! 🥰

Boy do I have good news for you PlanetScale.com/mysql

TablePlus looks like 👍

beat me to it! TablePlus indeed

Haha I made the square a little bigger because I was afraid I was gonna do it wrong! It's probably fine to do it tangentially, but I got scared 😂

Could you expand on bigger on your geometries of interest?

To be fair I do know more about databases than celestial bodies, so stay tuned for more database content

These comments are wonderful to read. Thank you so much

You can! I mention that at the end. I'll have to do another full video on that, as it's a big topic.

Interesting question... I think I'll do another video geo stuff. There's a lot more here to explore

Unfortunately not. You'd need to calculate drive times for that, and that's beyond the scope of a database.

I'm guessing you will want to do the time calculation outside of the database, as suggested. That calculation is probably expensive, so the fewer times you have to run that, the better... Hey presto! Another reason to use a bounding box! Decide what is the max distance you could travel in 1 hour from your location in each direction. Add & subtract from your centre, and that becomes a new bounding box.

Answering my own question: yes! I work with UK postcode data occasionally, and adding an generated column and then indexing it improved the speed for finding postcodes nearby by a further MASSIVE factor: alter table postcodes add column gridsquare varchar(15) GENERATED ALWAYS AS (CONCAT(TRUNCATE(latitude, 2), ":", TRUNCATE(longitude, 2))) STORED; alter table postcodes add index gridsquare_index (gridsquare); My performance improvement is hard to work out as it got vanishingly small: Find all postcodes within 10km of one point: 1452 postcodes returned - using no bounding box: 0.4 seconds - using bounding box and index: 0.1 seconds - using gridsquares:

Yes! You can absolutely do that. I originally wrote an article with that method, but it didn't make the video: aaronfrancis.com/2021/efficient-distance-querying-in-my-sql Perhaps the next one 🤔

Bravo on the explanation 👏👏 This is delightful

​@@PlanetScale Thank you! Ooh, I didn't realise you had an article on it too! And compliment right back at you - it's a really well explained article! And, yes, that's so clear that by tranching one of the variables, we can make a compound index that is SOOO much better! (For others who haven't read the article - and btw, REALLY do! - the idea is to create a new column rounding each decimal to the nearest 0.001, thereby allowing you to query for specific starts of the decimal like 'it must be 30.254 or 30.255' as a way of indexing bounding boxes and getting another performance boost! It's really neat and Aaron explains it very nicely!) Also, I didn't know that MySQL `IN` was so much more optimised than `BETWEEN` - perhaps another weird one for humans vs computers! Is there any benefit then to making the index ENTIRELY from tranched values - would it be quicker? Or is the point of an index that we don't want to have so many rows with the same index and so reduce its power/versatility for other query patterns? (I feel like I have only started scratching the surface of the power of indexes recently, so I'm intrigued by how to get more out of them!)

This is able to calculate the distance between two points in a straight line, which doesn't always correspond with roads or driving time. If two points are one mile apart, but there is a mountain or lake between them, that will dramatically change the travel time! You'd need a navigation API for that. Something that can tell you where roads are.

@@PlanetScale Thank you Perhaps I'll look into using the Google maps API

That's... a great question. I don't know! I'd have to research

I think your longitude wouldn't matter - so just set a maximum latitude if you are looking around the north pole, and a minimum latitude if looking around the south pole? Though I'm imagining the centre point is at the poles, but if you are 0.5miles south of the North Pole... not a clue! Yes, time to get researching!

Thank you so much! Lots more to come

found it, in your mysql tuts...

They aren't super common, even if they are neat

Unfortunately I can't. There might be some open data sets with lat/lon pairs though!

TablePlus!

Google Maps measures by driving distance typically. This is "as the crow flies"

Honestly, just to be kinda safe? I don't think the bounding box calculation I used is done on a sphere, but rather on a plane. So to take into account the haversine deal, I just bumped it up a bit. The worst possible thing would be for my redundant condition not to be redundant after all, but rather filter out some things that should be there.

I don't know enough about GIS to say, but even over several miles I think it would get inaccurate quickly

For short distances like across a large city. But it will become quite inaccurate if need say 100 miles

The earth is a sphere, so no matter in which direction you travel a 100 miles, you always follow the same curvature. So, it doesn't matter if you just use Pythagoras's theorem instead right? You only need a calculated distance when you want to select it or compare it to some arbitrary value. Otherwise I think it's safe to use a heuristic.

If your "points" are in longitude-latitude where units are degrees, then you'll get the "distance" in degrees as well. The distance per degree latitude changes as a function of longitude, so accurate conversion isn't simple. That's why the haversine formula exists.

@@quillaja I understand that the haversine formula calculates the accurate distance between two points, but I think that when only comparing the distance, you can use a heuristic like pythaghoras

Did I do it wrong? Or you just prefer meters?

@@aarondfrancis I just meant you had such a nice number of km (3.1415...) between two first points and you promptly got rid of it. but I'm just joking - apologies if that wasn't clear. Great video in general - not many people know you can deal with spatial data on the db side at all, not to mention how to do it efficiently. Thanks mate and keep it up

oh no

TablePlus!

@@PlanetScale Thank you!

Thanks for the comment! Every little thing helps 🤗

~3576.32 cubits

@@PlanetScale already forgot it lol

Other than that part what'd you think?

@@PlanetScale awesome video! Love the incremental approach to optimising queries.

@@invinciblemode great! glad to hear it

Big if true (it's not)

FlatPlanetScale

@@PlanetScale You can't spell planet scale without plane

Even set your name to "Ban Aaron," good commitment to the bit! (My name is Aaron)