8

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.

1
  • You will find this works much better if you use a Geometry (Point) datatype rather than separate latitude, longitude fields, as you will be able to use the spatial functions directly and index the spatial field, which will lead to non-trivial performance enhancements as table size grows. Is this something you are in a position to do? – John Powell Oct 13 '14 at 10:33
6

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

2
  • 1
    I answered a similar question on SO not long ago, which shows how to create a spatial type and index it, should you go that route. stackoverflow.com/questions/25232316/… – John Powell Oct 13 '14 at 12:56
  • 1
    Also worth mentioning that even with 5.6, all operations are still planar, ie no support for reprojections and coordinate systems. Also, and this will apparently change in an upcoming release, you can only index geometries in MyISAM. So, if you want transactional support (InnoDB) and spatial together, you can't. – John Powell Oct 13 '14 at 13:51
3

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.

2

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

2
  • thank you for your answer! but this will work just if ab parallel to x and the ad parallel to y, do you have another suggestions ? – david Oct 13 '14 at 10:12
  • Use only for rectangle. If a polygon you use the bounding box query to eliminate the majority of points outwith bounding box and then use a point in polygon algorithm to eliminate those outwith polygon see this to illustrate – david strachan Oct 13 '14 at 10:44
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.