如何在mysql查询中选择对角线区域之间的数据 [英] how to find exactly data between selected diagonal area in mysql query

问题描述

这些是

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

下面是我当前的mysql查询不会给我完美的选择diagonal.any之间的结果吗？

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 

推荐答案

消除边界框查询中的点但不在pol内您需要使用 多边形中的点 算法。

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

最简单的方法是使用数组中的坐标。这些可用于查询查询的最大和最小坐标以及pointInPolygon（）函数的参数。

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

在您的Map代码中使用 jQuery getJSON（）

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

使用Bounding Box和Point in Polygon获得的地图。

Map obtained using Bounding Box and Point in Polygon.

这篇关于如何在mysql查询中选择对角线区域之间的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！