How to know if a point is inside a rectangle on the map?

Question

I have four points that represent a rectangle (a,b,c,d). I got these points from mobile GPS.

In my database I have this table of business locations:

bussId | longitude | latitude
-----------------------------
  ..      ..          ..

How can I know which businesses are inside the rectangle?

I want to implement this function:

function isInsideRectangle(Point buss,Point a,Point b,Point c,Point d)

where the Point contains longitude and latitude.

Answer 1

The actual answer is a mixture of the other answers/comments you've already received.

MySQL is limited

I see from the tags you're using MySQL, the problem is, it can only do bounding boxes until 5.6, not the actual geometries. http://dev.mysql.com/doc/refman/5.0/en/spatial-relation-functions.html

If you want to use the bounding box:

SELECT *
FROM `test`
WHERE CONTAINS(`bounds`, geomfromtext('POLYGON(0 0,10 0,10 10,0 10,0 0)'))

In 5.6.1 it can do the actual geometries, so just change CONTAINS to ST_CONTAINS. http://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions.html#functions-that-test-spatial-relationships-between-geometries

SELECT *
FROM `test`
WHERE ST_CONTAINS(`bounds`, geomfromtext('POLYGON(0 0,10 0,10 10,0 10,0 0)'))

Spatial Indexes

Note, in line with John's comment (and the problem with David's answer), is that whatever database you go with, you want to be storing your data as a point spatial data type. See: https://dev.mysql.com/doc/refman/5.0/en/spatial-datatypes.html - This allows you to take advantage of a number of optimisations including spatial indexes which will make things faster. See also this question: https://stackoverflow.com/questions/5453063/benefits-of-spatial-over-using-standard-bounding-box-query

Answer 2

From the longitude and latitude coordinates, you can create spatial structures (Points) in your (spatial) database, like PostGIS. (see ST_GeomFromText)
Instead of your bussId | longitude | latitude, you would have a bussId, Point database called BussSpatial. You can then just query the following

    var request =
    'SELECT bussID
    FROM BussSpatial
    WHERE ST_CONTAINS(ST_MakePolygon(ST_GeomFromText('LINESTRING(' + a + ',+'+' + b + ',' + ' + c ','+ d+ ')'), buss);';

where a, b, c and d would be your variables (actually it should be a.longitude, a.latitude, etc ...)

Be careful: this SQL statement cannot be directly used like this, it is a String in your

function isInsideRectangle(Point buss,Point a,Point b,Point d)

and than you can do the request inside the function.

Answer 3

The bounding box query for SQL is(Using PDO)

$sql ="SELECT bussid FROM tablename WHERE(latitude  BETWEEN ? AND ? )AND (longitude  BETWEEN ? AND ?)";
$stmt = $dbh->prepare($sql);
// Assign parameters
$stmt->bindParam(1,$minLat);
$stmt->bindParam(2,$maxLat);
$stmt->bindParam(3,$minLng);
$stmt->bindParam(4,$maxLng);

WHERE parameters come from top left and bottom right of rectangle

Answer 4

if the map table has a field p of type POINT with SPATIAL index you can do this:

set @p = concat('Polygon((',_x0,' ',_y0,',',_x1,' ',_y0,',',_x1,' ',_y1,',',_x0,' ',_y1,',',_x0,' ',_y0,'))');

select * from map
where mbrcontains(GeomFromText(@p),p);