Check if lat/long is inside a rectangle [MySql]
If you have geographical bounds stored in your database of users or places and you want a custom search based on user input lat/long, to find out all the places having it, use the below mentioned solution. For reference, search anything on www.theveu.com
TL;DR [Simple Vanilla Solution]
To search any coordinate inside the below marked rectangle, use the below mentioned query
SELECT * from <table> WHERE ST_CONTAINS(ST_GEOMFROMTEXT(
'POLYGON((
89.08749961764374 24.205456654838912, //north-east long & lat
85.71962856217499 24.205456654838912, //north-west long & lat
85.71962856217499 21.559019025359103, //south-west long & lat
89.08749961764374 21.559019025359103, //south-east long & lat
89.08749961764374 24.205456654838912 //north-east long & lat
))'
),POINT(88.36389500000001,22.572646)); #lat/long you want to searchDetailed Solution [With dynamic inputs]
At Veu, we have a lot of photographers who mark their serviceable area to get work in their local geographic bounds, this helps them get more work frequently around them. An example of how they mark their serviceable area is below :
They can drag and resize this square to mark their bounds and we take note of it and store in the database. An example of a stored bound is below :
We are interested in the “bounds” node in the rectangle object, because that’s what creates the rectangle and mark the geographical limit. We check whether a customer input lat/long exists inside this bound, if it does, that means this photographer can serve the customer.
Let’s say a customer wants to find local photographers in Delhi. When he searches for Delhi on www.theveu.com, the coordinates that we get for Delhi are :
Lat: 28.68627380000001
Lng: 77.2217831
Now comes the real task.
On the back-end, I need to take this input and match against the serviceable area of 1000’s of photographers on Veu.
To be precise, I need to create a rectangle for each of the photographer’s serviceable area bounds which are stored in the database in each query and then match that against the coordinate that I got from customer.
If the customer coordinate exists inside the rectangle, its a match.
Also, make sure that the bounds follow the right-hand rule for geoJSON specification, to plot the rectangle correctly.
Then, map the stored JSON location object data, using mysql JSON functions correctly to the bounds node in the column and frame the mysql query so explicitly to avoid all the mysql syntax errors.
Then finally, if all is good, you might get a result.
Yup, that’s it. Kids stuff, right? Fucking no ! It took almost 4 hours to fix the mysql syntax issues with JSON nested nodes inside MYSQL GIS functions and then merge the final query with extended queries for the search.
But, firstly, what the hell is right-hand rule?
When you construct a polygon, you can order the coordinates in one direction or another. If you’re drawing a circle, you might start on the left and go counter-clockwise around to meet the original point. Or, you might go clockwise.
Here is the specification:
A linear ring MUST follow the right-hand rule with respect to the area it bounds, i.e., exterior rings are counterclockwise, and holes are clockwise.
To contextualize, let’s try to apply to the bounds that I mentioned above in the example picture.
To successfully create a polygon inside mysql query, I need to pass the GIS data in the below order
- North-East Long & North-East Lat
- North-West Long & North-West Lat
- South-West Long & South-West Lat
- South-East Long & South-East Lat
- North-East Long & North-East Lat [Most important, as this is what closes the rectangle on the Cartesian System]
Imaging, creating a rectangle in Coordinate Geometry in your Math’s class during school days, using coordinates given, you need to follow a linear approach, you can’t jump from A — >C without plotting B first.
Yup, that’s how easy it is. Phew, It took me another few hours to understand this.
But finally, I was able to get the exact result as I wanted. Its a great feeling.
Resulting dynamic query :
You can check the gist for actual query here.
To check how this works in real time, please visit www.theveu.com and perform a search to find local photographers in any city.
Shameless Plug 🤪:
Hey everyone!
I’m excited to announce the launch of my first book, “Building Serverless Applications on AWS: A Step-by-Step Guide !”
You can get my book in multiple formats, including Kindle, paperback (both in India and internationally), and Gumroad.
So, whether you prefer a physical book or a digital copy, you can choose the format that works best for you.
If you wish to buy it, please use the link below:
About the book : https://bit.ly/about-my-book
Kindle : https://bit.ly/buy-kindle-version
Paperback India : https://bit.ly/buy-paperback-version-india
Gumroad : https://bit.ly/buy-on-gumroad
Paperback International : https://bit.ly/buy-paperback-version-international
I’m excited to hear what you think!
