Good to know this function has the optional radius argument so we can still use it when we go to Mars
@PlanetScale11 ай бұрын
MultiPlanetScale
@20toninho11 ай бұрын
i really like the way how he teaches ...
@PlanetScale11 ай бұрын
🫡 thank you!
@ZacZajdel11 ай бұрын
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 😅🎉
@aarondfrancis11 ай бұрын
You're kind to say so. Thank you so much Zac! Forwarding that last part to my boss
@Maazin511 ай бұрын
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)
@SriTejaChilakapati11 ай бұрын
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.
@ambuj.k11 ай бұрын
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!
@ahmad-murery11 ай бұрын
It's always good idea to reduce the number of rows needed to be filtered using an expensive function beforehand. Keep it up, Aaron!
@osamaa.h.altameemi55926 ай бұрын
fantastic video but if we use a composite index on "x" , "y" and change the query to be where x>x1 and yy2 that should work and make full use of the composite index on (x,y). Again thx for the fantastic video
@hans-joergwahmkow262111 ай бұрын
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. ;-)
@PlanetScale11 ай бұрын
> Still learning new things every day. Keeps the job exciting. ;-) Honestly this is such a good perspective. Thank you for the thoughtful comment
@JakobTheCoder4 ай бұрын
Such a good video, thank you, Aaron
@nishatsayyed832611 ай бұрын
This is the type of quality in any tutorial. Looking forward to a LOT of these. Thanks ❤
@PlanetScale11 ай бұрын
We've only just begun 😎 Thanks for the kind words!
@yangsui130111 ай бұрын
I really like the way you explain to people, especially adding some funny stuff in the middle XD Keep going Aaron!
@PlanetScale11 ай бұрын
Thank you! Trying my best 🫡
@alexrimui4 ай бұрын
This really helped me at work today. Thanks.
@fededelsur43064 ай бұрын
amazing explanation ! love this video and of course thanks!
@Alekzv911 ай бұрын
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
@PlanetScale11 ай бұрын
I feel like every developer has been asked to do this at some point!
@MichaelWilding-n2wАй бұрын
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.
@MatthiasFeist-de11 ай бұрын
A follow up video about the spatial column type and the geospatial functions would be awesome.
@PlanetScale11 ай бұрын
Absolutely! On my list. I feel like this video could've been an hour long 😅 More to come
@KyleMcNally11 ай бұрын
@@PlanetScaleI store my lat and lng as GEOMETRY, but I still pull them out using X() and Y() to do some work. video wanted!
@InfinityFnatic11 ай бұрын
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.
@PlanetScale11 ай бұрын
These are a ton of work, so I really appreciate the kind feedback!
@rafaelacioly325211 ай бұрын
Amazing video! It would be great to see how to calculate the box and how to use the "Point" column as well :)
@ricohumme43285 күн бұрын
I wish, I truly wish, I found this video sooner than today
@TWPO10 ай бұрын
Excellent! You communicate concepts extremely well
@PlanetScale10 ай бұрын
Thank you! I really enjoy teaching
@Formula7Driver8 ай бұрын
I felt so smart when I figured this out on my own a couple of years ago
@tash1coder8 ай бұрын
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.
@sanyokes11 ай бұрын
This is the most useful and interesting video I have seen in the last few days. Amazing and insightful
@PlanetScale11 ай бұрын
Thank you! We'll keep 'em coming 💪
@jhechtf8 ай бұрын
"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.
@PlanetScale8 ай бұрын
Haha that's the goal! 🥰
@federico.magnani11 ай бұрын
Incredibly clear and useful!
@eddie108 ай бұрын
Excellent video! Thank you! 🚀
@dimitridovgan636411 ай бұрын
Very interesting topic! Thank you very much!
@creativenull11 ай бұрын
I read your post on this before! Really glad to see a video on it nicely explained :D
@anmai380511 ай бұрын
This is a beneficial video. Hope you can upload more videos on this subject. Keep going !!!
@AlexCastroDev11 ай бұрын
My new research
@huuhhhhhhh11 ай бұрын
This is gold
@rafaelhernand3z11 ай бұрын
Excellently explained 🎉 Great videos with clear explanations. Do you offer consulting? I'm running into a time series issue with timezones and I'd be willing to book a consultation to get an understanding of how to best approach the problem.
@johnypatera1608 ай бұрын
Nice video Aaron, but: You should use “Restaurant” condition from the very beginning, because in that case initial query will take less then ~2.2s. So it its a bit misleading and can lower value of otherwise very informative video. #feedback #keepGoing
@meetpithadia74610 ай бұрын
We need an entire MySQL course from scratch from Aaron
@PlanetScale10 ай бұрын
Boy do I have good news for you PlanetScale.com/mysql
@erikgurney3068 ай бұрын
32 second mark is legendary
@keenoogodlike11 ай бұрын
Very useful feature I just know. I must try it
@erlanggafauzanrezaganie959511 ай бұрын
great content, hoping there is new contnt about performance
@zsoltk614520 күн бұрын
How do you guarantee that the bounding box condition is determined by the engine, before the costly distance calculation is even performed? Do the order of the WHERE conditions that matters?
@hrd17118 ай бұрын
Good video. You might also want to look into Geohash where you can represent every coordinate with a string. You can then index that column with a string and use like % query.
@jameslucas559011 ай бұрын
I live in SA. very cool.
@i000alen10 ай бұрын
"because I don't speak meters" lmao😂
@pflasterstrips72546 ай бұрын
hmmm, i wonder how you could boost performance by putting the points in a grid to quickly decide handle grid squares that are entirely in the circle
@danielkirsch435111 ай бұрын
Great video, clear explanations - this gets me looking forward to learn more about MySQL geospatial capabilities. I wanted to ask what MySQL client are you using - it looks like it may run queries instantly as you type, almost like hmr- is that so?
@danielkirsch435111 ай бұрын
TablePlus looks like 👍
@PlanetScale11 ай бұрын
beat me to it! TablePlus indeed
@AlexCernat9 ай бұрын
excelent video, "bounding rectangle" is an excellent way to speed up such kind of queries (personally I just "assumed" that earth is "flat", so euclidian distance ftw :-P - of course, it's not correct, but it was about closest locations, not all location in a range, so ... trading some accuracy to performance) what I didn't understand from your video is why do you need some extra distance offset, shouldn't be the rectangle just tangent to the circle in all four points? (or maybe I make the same mistake thinking 2D, but the earth is 3D?)
@PlanetScale9 ай бұрын
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 😂
@kayodionizio28866 ай бұрын
i have an applicatipion that save your geolocation position when you check in... isn't best to use decimal for EXAT location? and how much long should be this numbers?
@quillaja9 ай бұрын
Why are you storing your "point" in 2 different fields? Just use the POINT column type and add (spatial) index on that. Then buffer on your geometries of interest, and select those that intersect with the buffer. Done.
@Tbagstealer5 ай бұрын
Could you expand on bigger on your geometries of interest?
@nikhilpsathyanathan8 ай бұрын
New to me
@njdarda11 ай бұрын
the earth is not flat? and to think that I trusted this guy!
@PlanetScale11 ай бұрын
To be fair I do know more about databases than celestial bodies, so stay tuned for more database content
@Laflamablanca96911 ай бұрын
Damn your videos are so good
@PlanetScale11 ай бұрын
These comments are wonderful to read. Thank you so much
@medilies11 ай бұрын
Why not group LONG and LAT in one POINT type column
@PlanetScale11 ай бұрын
You can! I mention that at the end. I'll have to do another full video on that, as it's a big topic.
@TheMoonWatcher10 ай бұрын
I wonder if you would be able to gain some performance if you use a spatial index and compound index by doing a sub-query/join 🤔 Or having the sub-query would negate any potential gains from using a spatial index.
@PlanetScale10 ай бұрын
Interesting question... I think I'll do another video geo stuff. There's a lot more here to explore
@jogindersingh90967 ай бұрын
What editor/IDE are you using?
@Ceyesse11 ай бұрын
Is there a way to select all the points in a 1 hour radius, instead of 1 mile radius ?
@PlanetScale11 ай бұрын
Unfortunately not. You'd need to calculate drive times for that, and that's beyond the scope of a database.
@nathanbrown264011 ай бұрын
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.
@svoloSlovakia8 ай бұрын
which editor/ide are you using?
@night234128 ай бұрын
guys don't pause at 0:01
@nathanbrown264011 ай бұрын
@PlanetScale, just as you created your compund type_lon index, I wondered: "Could we create a compound index with truncated values of our latitude and longitude?" It would be like indexing all points by with map grid square they fall into. Then our 'bounding box' redundant query becomes 'the point falls into one of the 4/8/16 grid squares' A bit more work perhaps in query design to calculate what the valid grid squares would be, but perhaps that would allow for the index reducing down to just the bounding box contents more immediately, rather than still reading all of our long rectangle?
@nathanbrown264011 ай бұрын
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:
@PlanetScale11 ай бұрын
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 🤔
@PlanetScale11 ай бұрын
Bravo on the explanation 👏👏 This is delightful
@nathanbrown264011 ай бұрын
@@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!)
@tunmis311 ай бұрын
Could someone please explain why he said he wouldn't use MySQL for driving directions? @11:30 Because that's exactly what I wanted my app to do and is the reason I clicked on this video 😂
@PlanetScale11 ай бұрын
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.
@tunmis311 ай бұрын
@@PlanetScale Thank you Perhaps I'll look into using the Google maps API
@RandomGeometryDashStuff11 ай бұрын
05:20 how would bouding box look if it contains north or south pole?
@PlanetScale11 ай бұрын
That's... a great question. I don't know! I'd have to research
@nathanbrown264011 ай бұрын
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!
@AngelEduardoLopezZambrano10 ай бұрын
Shout out to the flat earthers that didn't like this video 0:33
@nomadshiba11 ай бұрын
pratically open world chunks
@sanathkumaru635811 ай бұрын
This is God level content!!!!.Glad that i stumbled upon the video.Made my day❤
@PlanetScale11 ай бұрын
Thank you so much! Lots more to come
@coordinates_6 ай бұрын
sorry, what's the name of the sql too you're using please
@coordinates_6 ай бұрын
found it, in your mysql tuts...
@sohailyunusmogambo9 ай бұрын
Why no mention of geohash?
@PlanetScale9 ай бұрын
They aren't super common, even if they are neat
@jessieonfly11 ай бұрын
Can you share the test data?
@PlanetScale11 ай бұрын
Unfortunately I can't. There might be some open data sets with lat/lon pairs though!
@lavisharma321011 ай бұрын
What db client/tool is that ?
@PlanetScale11 ай бұрын
TablePlus!
@osama67959 ай бұрын
good explanation but im not getting the most accurate result the same as it is in google maps
@PlanetScale9 ай бұрын
Google Maps measures by driving distance typically. This is "as the crow flies"
@sirhenrystalwart830311 ай бұрын
Why do you make the bounding box larger than the circle? I would have expected the box have sides of length 2*r, and thus perfectly bound the circle.
@PlanetScale11 ай бұрын
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.
@MrJellekeulemans11 ай бұрын
To find the closest point you can probably just use pythagoras right? Unless you need to find points on the other half of the earth, this will work just fine.
@PlanetScale11 ай бұрын
I don't know enough about GIS to say, but even over several miles I think it would get inaccurate quickly
@musicmaker36810 ай бұрын
For short distances like across a large city. But it will become quite inaccurate if need say 100 miles
@MrJellekeulemans9 ай бұрын
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.
@quillaja9 ай бұрын
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.
@MrJellekeulemans8 ай бұрын
@@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
@j4ggi11 ай бұрын
> 2:45 you had π km distance and you converted that to miles. wtf
@aarondfrancis11 ай бұрын
Did I do it wrong? Or you just prefer meters?
@j4ggi11 ай бұрын
@@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
@Mempler11 ай бұрын
Correct, earth is not flat. It is donut shaped!!
@PlanetScale11 ай бұрын
oh no
@iyadahmed43011 ай бұрын
btw which gui is that?
@PlanetScale11 ай бұрын
TablePlus!
@iyadahmed43011 ай бұрын
@@PlanetScale Thank you!
@BrunoGabrielAraujoLebtag8 ай бұрын
I don't speak meters... Muricas
@wwilson6911 ай бұрын
Converts meters to miles. * close the tab
@aarondfrancis11 ай бұрын
Thanks for the comment! Every little thing helps 🤗
@raphaelmobistacla223611 ай бұрын
The highest point of this video was "... since I don't speak meters.".
@der.Schtefan10 ай бұрын
A bunch of POIs? A speckle of POIs? A murder of POIs? A database table of POIs?
@leotravel8511 ай бұрын
wtf is a mile
@PlanetScale11 ай бұрын
~3576.32 cubits
@leotravel8511 ай бұрын
@@PlanetScale already forgot it lol
@invinciblemode11 ай бұрын
When you explicitly converted from metres to miles, I had to unsubscribe. Sorry man 😂
@PlanetScale11 ай бұрын
Other than that part what'd you think?
@invinciblemode11 ай бұрын
@@PlanetScale awesome video! Love the incremental approach to optimising queries.
@PlanetScale11 ай бұрын
@@invinciblemode great! glad to hear it
@akam991910 ай бұрын
the earth is flat
@PlanetScale10 ай бұрын
Big if true (it's not)
@c01nd01r11 ай бұрын
> 0:33 Dislike, unsubscribe
@PlanetScale11 ай бұрын
FlatPlanetScale
@becelli11 ай бұрын
@@PlanetScale You can't spell planet scale without plane
@BanAaron11 ай бұрын
The earth is flat. Disliked, didn't comment, unsubscribed
@PlanetScale11 ай бұрын
Even set your name to "Ban Aaron," good commitment to the bit! (My name is Aaron)
@Ceyesse11 ай бұрын
Is there a way to select all the points in a 1 hour radius, instead of 1 mile radius ?