如何在mysql查询中找到所选对角线区域之间的确切数据

Question

These are my coordinates of Selected diagonal points, will be more when selected points are more.

1: 25.312063043186914  2: 55.30672073364258
1: 25.24096949112138   2: 55.40525436401367
1: 25.224509592006314  2: 55.3462028503418
1: 25.22513076073063   2: 55.281314849853516
1: 25.27822894908031   2: 55.25899887084961

below is my current mysql query not giving me perfect result between selected diagonal.any help??

SELECT 
  * 
FROM
  tbl_custom 
WHERE latitude <= 25.312063043186914 
  AND latitude >= 25.224509592006314 
  AND longitude <= 55.40525436401367 
  AND longitude >= 55.25899887084961 

Answer 1

To eliminate the points within your bounding box query but not within polygon you require to use Point in Polygon algorithm.

The easiest way to do this is to have the coordinates in arrays. These can be used to find max and min coordinates for the query and for parameters for pointInPolygon() function.

function pointInPolygon(polySides,polyX,polyY,x,y) {
 var j = polySides-1 ;
  oddNodes = 0;
  for (i=0; i<polySides; i++) {
    if (polyY[i]<y && polyY[j]>=y  ||  polyY[j]<y && polyY[i]>=y) {
        if (polyX[i]+(y-polyY[i])/(polyY[j]-polyY[i])*(polyX[j]-polyX[i])<x)  {
            oddNodes=!oddNodes; 
        }
    }
   j=i; }

  return oddNodes;
}

In your Map code using jQuery getJSON()

var polySides = 4;//number of points in polygon
//horizontal Latitude coordinates of polygon  
var polyLat = new Array();
polyLat[0] = 51.5;
polyLat[1] = 51.5;
polyLat[2] = 52.5;
polyLat[3] = 53;
polyLat[4] = 51.5;//First point repeated to close polygon
//vertical Longitude coordinates of polygon 
var polyLng =  new Array();
polyLng[0] = 0.5;
polyLng[1] = -1.9;
polyLng[2] = -1;
polyLng[3] = 0.6;
polyLng[4] = 0.5;
//Coordinates for bounding box
var maxLat = Math.max.apply(null,polyLat);
var minLat = Math.min.apply(null,polyLat);
var maxLng = Math.max.apply(null,polyLng);
var minLng = Math.min.apply(null,polyLng);

//Using jQuery
var url = "yourFile .php";
 url +="?maxLat="+maxLat +"&minLat="+minLat +"&maxLng="+maxLng +"&minLng="+minLng;
 $.getJSON(url,function(data) {
    $.each(data.marker,function(i,dat){
        if (pointInPolygon(polySides,polyLat,polyLng,dat.lat,dat.lng)){
            var latlng = new google.maps.LatLng(dat.lat,dat.lng); 
            addMarker(latlng,dat.name);
            bounds.extend(latlng);
        }
    });
    map.fitBounds(bounds);
});

Map obtained using Bounding Box and Point in Polygon.