Faster geospatial queries in MySQL

Searching a table in MySQL based on latitude and longitude columns.

——————————————————
📚 Learn more about PlanetScale

——————————————————

00:00 A point of interest
00:21 Haversine formula
01:00 ST_DISTANCE_SPHERE distance calculation
03:25 Filtering by ST_DISTANCE_SPHERE
04:28 It's too slow
05:11 Bounding boxes
06:55 Using a bounding box
08:10 Adding an index
10:20 Adding a compound index
11:34 A note about POINT columns

——————————————————

💬 Follow PlanetScale on social media

[TravisNorthcutt]

Good to know this function has the optional radius argument so we can still use it when we go to Mars

[toninho]

i really like the way how he teaches ...

[Maazin]

If you're wondering how he got the 4 values on lines 6 and 8 at 7:00, use these formulas:

LAT +/- (180/pi)*(1.05 miles / 3958.71 miles) and
LON +/- (180/pi)*(1.05 miles / 3958.71 miles) / cos(pi * LAT / 180)

[ZacZajdel]

These videos by Aaron are so informative. I look forward to every single one and learn so much along the way. PlanetScale give this man a raise 😅🎉

[ambuj.k]

I had once used haversine formula for one of my freelance projects and didn't realize how much performance I left on the table after watching this video. Please keep making this awesome content!

[osamaa.h.altameemi]

fantastic video but if we use a composite index on "x", "y" and change the query to be
where x>x1 and y<y1 and x < x2 and y>y2
that should work and make full use of the composite index on (x, y). Again thx for the fantastic video

[ahmad-murery]

It's always good idea to reduce the number of rows needed to be filtered using an expensive function beforehand.
Keep it up, Aaron!

[yangsui]

I really like the way you explain to people, especially adding some funny stuff in the middle XD Keep going Aaron!

[hans-joergwahmkow]

I knew the trick with the bounding box. What I didn't know was that MySQL has that nice function. So I always did that calculation in my applications. Still learning new things every day. Keeps the job exciting. ;-)

[alexrimui]

This really helped me at work today. Thanks.

[MichaelWilding-nw]

This video was great! I actually will try to implement this because even though the geolocation functions I do have are for Admin only, it would be great to release to the wild and have users have access to it with speed.

[fededelsur]

amazing explanation ! love this video and of course thanks!

[InfinityFnatic]

Cool video! I like the minimal and focused presentation style and the usage of the simple UI. It looks very focused and doesn't distract from what you are trying to do. I've never had a chance to use planetscale but I will keep on watching your videos.

[Alekzv]

This is a really fun problem, reminds me one of my first tasks as a developer 7 years ago. Although I did it in SQLServer

[nishatsayyed]

This is the type of quality in any tutorial. Looking forward to a LOT of these. Thanks ❤

[TWPO]

Excellent! You communicate concepts extremely well

[tashcoder]

wow! gonna use the same concept to improve job search by lat, long in my current project. Such a simple idea but a huge improvement. thank you man.

[sanyokes]

This is the most useful and interesting video I have seen in the last few days. Amazing and insightful

[jhechtf]

"see if i can explain it to you very quickly"
"... what the f--"
"okay, new plan"

every damn video you get a chuckle out of me.

[rafaelacioly]

Amazing video! It would be great to see how to calculate the box and how to use the "Point" column as well :)