Finding Points in a Rectangle or Circle with mysql

Question

I have a mysql database table with a list points with their Co-ordinates (x,y)

I want to find the list of points which fall inside the rectangle. This would have been simple had any one side of the rectangle been aligned parallel or perpendicular to any axis. But is not. Which means the rectangle is rotated. I also have to find the points inside a circle.

Known Data for Rectangle -Coordinates for all the four points Known Data for Circle -Co-ordinates for the center and the radius.

How do I query the mysql table to find the points falling in the rectangle and the circle?

If it matters then the front end I am using is PHP.

I do not want to call all the points and analyze each point, as the list of points is extensive, can it be done any other way? — lal_bosdi, Apr 05 '13 at 16:18

Sammitch · Accepted Answer · 2013-04-05T17:43:43.333

2

A rectangle can be defined by two points representing the opposing corners, eg: A(x,y) and B(x,y). If you have a point C(x,y) that you want to test to see if it is inside the rectangle then:

IF( (Cx BETWEEN Ax AND Bx) AND (Cy BETWEEN Ay AND By) ) THEN
  point C is in the rectangle defined by points A and B
ELSE
  nope
ENDIF

A circle can be defined by a single point C(x,y) and a radius R. If the distance D between the center and the point P(x,y) is less than the radius R, then it is inside the circle:

And of course you remember the Pythagorean Theoreom, right?

C² = A² + B² SO C = SQRT(A² + B²)

So:

D = SQRT( ABS(Cx - Px)² + ABS(Cy - Py)²)

IF( D <= R ) THEN
  point P is inside the circle with center C and radius R
ELSE
  nope
ENDIF

edit:

The algorithm for checking if a point is within a polygon is a bit more complex than what I'd prefer to write in a SQL query or stored procedure, but it is entirely possible. It's worth noting that it runs in constant-time and is very lightweight. [requires roughly 6 arithmetic ops and maybe 2 or 3 logic ops for each point in the poly]

To pare down the number calculations required you can simply write your select to get points within a rough bounding box before procesing them further:

WHERE
  x BETWEEN MIN(x1,x2,x3,x4) AND MAX(x1,x2,x3,x4)
  AND
  y BETWEEN MIN(y1,y2,y3,y4) AND MAX(y1,y2,y3,y4)

Assuming the columns containing the x and y values are indexed this might use a few less CPU cycles than simply doing the math, but it's debatable and I'm inclined to call it a wash.

As for the circle you can't possibly get more efficient than

WHERE
  SQRT( POW(ABS($Cx - x),2) + POW(ABS($Cy - y),2) ) < $radius

You're far too concerned with the perceived cost of these calculations, just write the code and get it working. This is not the stage to be performing such niggling optimizations.

edited Apr 05 '13 at 17:43

answered Apr 05 '13 at 16:09

Sammitch

30,782
7
50
77

I know the formulas, but what i need to know is that can I write a stored procedure or a query, with which I pass the known parameters and get a list of points falling inside the area. Is that possible using php+mysql. – lal_bosdi Apr 05 '13 at 16:13
His rectangle is rotated apparently so your inRectangle code isn't going to work. Have to rotate it so it' perpendicular with the Y axis which will be damned painful. – Tony Hopkinson Apr 05 '13 at 16:13
I do not want to call all the points and analyze each point, as the list of points is extensive. – lal_bosdi Apr 05 '13 at 16:17
@Vishak well unless the circles and rectangles you're thinking of are *always* the same you can pre-compute and store the results, otherwise there is no possible way to just *magically* know. You can probably pare down the number of points you actually process by filtering out points that are more than X units away from any of your test points, where X is sufficiently large, but that would probably entail just as much math as actually processing them, which is not that much. Math is sort of a computer's *whole job*, and they're pretty good at it. – Sammitch Apr 05 '13 at 16:33
Thanks. The Edit helps. I was thinking along the same lines. I was planning to take the max x, max y, min x and min y from the rectangle vertices to create another rectangle surrounding the required one and fetch all points within the main rectangle and then run the function on only these returned points, eliminating the rest. Fetching the points in the main rectangle would be very simple through a query with four conditions of max x, min x, min y and maxy. For a circle, I was thinking to creating a square around it the same way. – lal_bosdi Apr 06 '13 at 03:34
"You're far too concerned with the perceived cost of these calculations, just write the code and get it working. This is not the stage to be performing such niggling optimizations." -I am developing an API for to fetch Sigmet points along a flight path. There might be more than 5K points and i donot want to fetch and run the function on each of them every time the flight path function is called. :) – lal_bosdi Apr 06 '13 at 03:36
@Sammitch - The functions does not work when the triangle is rotated. :( – lal_bosdi Apr 08 '13 at 14:29

sed · Answer 2 · 2017-02-09T22:04:19.033

One thing to add to @Sammitch's answer is, calculating haversine distance in case you are looking at latitudes and longitudes on world map (distance calculation on a spherical surface, since Earth is a sphere ;) https://en.wikipedia.org/wiki/Haversine_formula)

Here's a vanilla Javascript example for calculating that:

          function calculateHaversineDistance(lat1x, lon1, lat2x, lon2) {
            var R = 6371; // km
            var dLat = toRad(lat2x-lat1x);
            var dLon = toRad(lon2-lon1);
            var lat1 = toRad(lat1x);
            var lat2 = toRad(lat2x);
            var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
                    Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);
            var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
            return R * c;
          }
          function toRad(x) {
             return x * Math.PI / 180;
          }

EDIT->

Here's a php version I wrote:

function toRad($x) {
 return $x * pi() / 180;
}

function calculateHaversineDistance($lat1, $lon1, $lat2, $lon2) {

    $R = 6371; // km
    $dLat = $this->toRad($lat2-$lat1);

    $dLon = $this->toRad($lon2-$lon1);

    $lat1 = $this->toRad($lat1);

    $lat2 = $this->toRad($lat2);


    $a = sin($dLat/2) * sin($dLat/2) +
            sin($dLon/2) * sin($dLon/2) * cos($lat1) * cos($lat2);

    $c = 2 * atan2(sqrt($a), sqrt(1-$a));

    return $R * $c;
}

Finding Points in a Rectangle or Circle with mysql

2 Answers2

edit: