从MySQL数据库中获取谷歌Maps .getBounds内的所有记录?

Ok I have a database with about 1800 rows, each has a column lat and long, what I am trying to do, it query against Google Maps V3 .getBounds The result is something like ((33.564398518424134, -79.38014701875002), (35.375726155241175, -76.08424858125002)) What I want to do, is get every record whose lat & lng in the database is within those bounds. Any suggestions?

好我有一个数据库有1800行,每一列lat和长,我想做什么,它对谷歌地图查询V3 .getBounds结果类似((33.564398518424134,-79.38014701875002),(35.375726155241175,-76.08424858125002))我要做的,就是让每个记录的lat和液化天然气在数据库中是在这些范围内。有什么建议吗?

Im thinking I would have to extrapolate the other 2 corners of the map, since .getBounds only contains 2 points (2 corners of the map I suppose), and I would want to be within 4 points. If I did that I would end up with an array something like...Now correct me if I am wrong but this should be NW, NE, SW, SE corners of the map, in lat-lng.

我想我必须要外推地图的另外两个角，因为。getbounds只包含2个点(我想是地图的2个角)，我希望在4个点之内。如果我这样做，我就会得到一个数组，比如…如果我错了，请纠正我，但这应该是NW, NE, SW，在地图的角落，在latlng。

33.564398518424134, -79.38014701875002
33.564398518424134, -76.08424858125002
35.375726155241175, -79.38014701875002
35.375726155241175, -76.08424858125002

Ok, so If I have this data, how would I build a query to get the rows that are within these coordinates? Table is called tilistings - columns are lat and lng ...also if there is an easier way, or I am just crazy with my methodology, please feel free to let me know.

如果我有这些数据，我该如何构建查询来获取这些坐标中的行呢?表被称为tilisting -列是lat和lng。如果有更简单的方法，或者我只是对我的方法疯狂，请随时告诉我。

Thanks

谢谢

10 个解决方案

#1

if from Google: ( (a, b), (c, d) )

(a, b)， (c, d)

SELECT * FROM tilistings WHERE lat > a AND lat < c AND lng > b AND lng < d

从lat > a和lat < c和lng > b和lng < d的tilisting中选择*

#2

All previous answers only work for 1/4 of the world!

所有之前的答案只适用于1/4的世界!

W3t Tr3y's answer was close, but it had an error (extra "<").

W3t Tr3y的答案很接近，但是它有一个错误(额外的“<”)。

All of them only work for USA, because it's in the NORTH hemisphere. They don't work for the south hemisphere, nor eastern countries (right of greenwich).

他们都只在美国工作，因为它在北半球。他们既不为南半球工作，也不为东方国家工作。

Here's a simple solution without functions or complicated stuff.

这里有一个简单的解决方案，没有函数或复杂的东西。

letters are the results in the order you get them from map.getBounds() i.e. swlat, swlng, nelat, nelng = a, b, c, d.

字母是您从map.getBounds()中获得的结果，即swlat、swlng、nelat、nelng = a、b、c、d。

SELECT * FROM tilistings WHERE
(CASE WHEN a < c
        THEN lat BETWEEN a AND c
        ELSE lat BETWEEN c AND a
END) 
AND
(CASE WHEN b < d
        THEN lng BETWEEN b AND d
        ELSE lng BETWEEN d AND b
END)

or another solution with and/or (you can test it for speed, I don't know how to run it more than once in WorkBench)

或者使用and/or的另一个解决方案(您可以测试它的速度，我不知道如何在WorkBench中多次运行它)

SELECT * FROM tilistings WHERE
(a < c AND lat BETWEEN a AND c) OR (c < a AND lat BETWEEN c AND a)
AND 
(b < d AND lng BETWEEN b AND d) OR (d < b AND lng BETWEEN d AND b)

Now you can map the whole world :) I hope someone can mark the above answers as incorrect, because they are making us lose a lot of time to millions of learning people like me. I wonder how they get so many votes when they actually dont work!

现在你可以描绘整个世界:)我希望有人能把上面的答案标记为不正确的，因为它们让我们失去了很多时间给数以百万计像我这样的学者。我想知道他们是怎么得到这么多选票的，而实际上他们并没有这么做!

PS: The chances of of your map pixel edge matching the exact 15 decimals value of the coordinates is zillions of times less than the chances of missing a whole 3/4 of the world!

PS:你的地图像素边缘匹配精确的15个小数点值的机会是无数次，比错过整个世界3/4的机会少很多!

#3

I haven't used Google's API, but my understanding is that if you get ((33.564398518424134, -79.38014701875002), (35.375726155241175, -76.08424858125002)) back from a getBounds call then (33.564398518424134, -79.38014701875002) is the Southwest corner and (35.375726155241175, -76.08424858125002) is the Northeast. I say that as I think they return the Northeast and Southwest corners and I'm assuming the points are latitude, longitude.

我没有使用谷歌的API，但是我的理解是，如果你(33.564398518424134，-79.38014701875002)，(35.375726155241175，- 76.024858125002)从一个get边界调用返回(33.56563801433983781745002)我说，因为我认为它们返回东北和西南角我假设这些点是纬度，经度。

If that's correct, then Bensiu's query would work. Typically using BETWEEN is more efficient.

如果这是正确的，那么Bensiu的查询将会有效。通常使用BETWEEN会更有效。

SELECT * FROM tilistings WHERE lat BETWEEN a AND c AND lng between b AND  d

#4

I now it's to late for the comment, but maybe it will be usefull for someone. sergio unswer is not quite correct the query for whole world has to look a little bit different, if I'm not mistaken something like this one:

我现在评论有点晚了，但也许对某人有用。塞尔吉奥·昂斯维尔不太正确整个世界的问题看起来有点不同，如果我没弄错的话

SELECT * FROM tilistings WHERE (sw_lat < ne_lat AND lat BETWEEN sw_lat AND ne_lat) OR (sw_lat > ne_lat AND (lat BETWEEN sw_lat AND 180 OR lat BETWEEN -180 AND ne_lat)) (sw_lon < ne_lon AND lon BETWEEN sw_lon AND ne_lon) OR (sw_lon > ne_lon AND (lon BETWEEN sw_lon AND 180 OR lon BETWEEN -180 AND ne_lon))

SELECT * FROM tilistings(sw_lat < ne_lat和纬度之间sw_lat和ne_lat)或(sw_lat > ne_lat和(lat sw_lat至180或lat -180至ne_lat))(sw_lon < ne_lon和朗sw_lon和ne_lon)或(sw_lon > ne_lon和(经度sw_lon至180或朗在-180年和ne_lon))

#5

This works for me:

这工作对我来说:

$condition1 = $a < $c ? "lat > $a AND lat < $c" : "lat > $a OR lat < $c";
$condition2 = $b < $d ? "lon > $b AND lon < $d" : "lon > $d OR lon < $b";
$q = "SELECT * FROM tilistings WHERE ( $condition1 ) AND ( $condition2 )";

#6

We can find result between Maps.getBounds northEast and southWest latitude and northEast and southWest longitude using below query.

我们可以在地图之间找到结果。getBounds东北和西南纬度和东北和西南经度使用以下查询。

Search query should be between northEast.latitude AND southWest.latitude AND northEast.longitude AND southWest.longitude

搜索查询应该在东北之间。纬度和西南。纬度和东北部。经度和southWest.longitude

$nelat=$_GET['nelat']-0.5;
$nelng=$_GET['nelng']-0.5;
$swlat=$_GET['swlat']-0.5;
$swlng=$_GET['swlng']-0.5;

$sql ="SELECT * FROM tablename where (CASE WHEN ".$nelat." < ".$swlat."
    THEN s.latitude BETWEEN ".$nelat." AND ".$swlat."
    ELSE s.latitude BETWEEN ".$swlat." AND ".$nelat."
END) 
AND
(CASE WHEN ".$nelng." < ".$swlng."
        THEN s.longitude BETWEEN ".$nelng." AND ".$swlng."
        ELSE s.longitude BETWEEN ".$swlng." AND ".$nelng."
END)";

#7

Take a look at the new spatial data and functions available in MySQL 5.7, now also for InnoDB.

看看MySQL 5.7中新增的空间数据和函数，现在也适用于InnoDB。

Using the examples above, it takes about 1s for a table with 1 mio records to get the locations within a certain bounding box.

使用上面的示例，使用1个mio记录的表大约需要1才能获得某个绑定框中的位置。

With ST_Within() and ST_MakeEnvelope() and the correct spatial index I get the result in less the 0.01s.

通过ST_Within()和ST_MakeEnvelope()以及正确的空间索引，我得到的结果是小于0.01。

#8

A slightly simplified version of Vladimir's answer is working perfectly for me.

弗拉迪米尔回答的一个稍微简化的版本对我来说非常有用。

Taking an area bound by a box with a southern edge (south), a western edge (west), a northern edge (north) and an eastern edge (east). These can be derived from the Google Map .getBounds which provide south-west corner and north-east corner - you only need the two corners as these fully describe the square bounding box.

用一个盒子包围的区域，盒子的南部边缘(南部)，西部边缘(西部)，北部边缘(北部)和东部边缘(东部)。这些可以从谷歌Map . getbounds中得到，它提供了西南角和东北角——您只需要两个角，因为这些角完全描述了方形边界框。

In this example our database table is called locations and contains a column for latitude and longitude.

在这个示例中，我们的数据库表被称为位置，并包含一个经度和纬度的列。

SELECT * FROM locations WHERE
(latitude BETWEEN south AND north) AND
((west < east AND longitude BETWEEN west AND east) OR
 (west > east AND (longitude BETWEEN west AND 180 OR longitude BETWEEN -180 AND east)))

This works based on the fact that we only need to account for crossing 180/-180 longitude line as a special case - i.e. where the western longitude is a higher value than the eastern longitude. In any case where we do not cross that line the western longitude will always be less than the eastern longitude.

这是基于这样一个事实:我们只需要把横越180/-180经度线作为一个特殊的情况来考虑——例如，西经比东经高。无论如何，如果我们不越过那条线，西经总是小于东经。

With regards to latitude, the southern edge will always be a lower value than the northern latitude as there is no concept of wrapping around over the poles. To cover an area over the north pole, for example we simply have all longitudes (i.e. from -180 to 180) and latitude from the southern boundary to +90.

关于纬度，南缘的值总是比北纬的值低，因为没有围绕两极的概念。例如，要覆盖北极上空的区域，我们只需有从-180到180的所有经度和从南界到+90的纬度。

#9

I'm not sure the above statement is correct. Firstly, I believe the corners are nelat, nelng, swlat, swlng not as stated above "swlat, swlng, nwlat, nwlng". Secondly I believe the nelat is always going to be higher than the swlat of the window (unless the window covers one of the geographic poles which I'm not sure is even possible with google maps).

我不确定上面的说法是否正确。首先，我认为弯角是耐拉特、耐拉特、耐拉特，不像上面所说的“耐拉特、耐拉特、耐拉特、耐拉特”。其次，我相信内拉总是比窗口的swlat要高(除非窗口覆盖了一个地理极点，我不确定在谷歌地图上这是可能的)。

Finally, if the window covers the international date line (approx +/-180lng) i.e d < b then surely it should be "NOT BETWEEN b AND d". Perhaps someone with more specialist knowledge could confirm?

最后，如果窗口覆盖了国际日期线(大约+/-180lng) i。e d < b那么肯定应该是“不在b和d之间”。也许有更专业知识的人可以证实?

#10

From my Gist https://gist.github.com/jesuGMZ/0d7f38d80e2f67d0bc4b7fb620345344

从我要点https://gist.github.com/jesuGMZ/0d7f38d80e2f67d0bc4b7fb620345344

Having MySQL >5.7 with a table that contains a column type POINT named location and the following Google Maps response:

拥有MySQL >5.7和包含名为location的列类型点的表，以及以下谷歌映射响应:

"geometry": {
    "bounds": {
        "northeast": {
            "lat": 40.5638447,
            "lng": -3.5249115
        },
        "southwest": {
            "lat": 40.3120639,
            "lng": -3.8341618
        }
    },
    //....

you can perform a SQL query to retrieve all your locations contains in that boundary like this:

您可以执行SQL查询来检索该边界中包含的所有位置，如下所示:

SELECT * FROM my_table
WHERE Contains(
  ST_MakeEnvelope(
    ST_GeomFromText('POINT(40.5638447 -3.5249115)'),
    ST_GeomFromText('POINT(40.3120639 -3.8341618)')
  ),
  location
);

Consider to index location to improve the performance of your queries if apply. Also, is possible to use Within instead of Contains changing the order of the parameters.

考虑索引位置以提高查询的性能。同样，也可以在内部使用，而不是包含修改参数的顺序。

Useful links:

有用的链接:

https://dev.mysql.com/doc/refman/5.7/en/gis-general-property-functions.html
https://dev.mysql.com/doc/refman/5.7/en/gis-general-property-functions.html
https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html
https://dev.mysql.com/doc/refman/5.7/en/spatial-relation-functions-mbr.html
https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html#function_st-makeenvelope
https://dev.mysql.com/doc/refman/5.7/en/spatial-convenience-functions.html function_st-makeenvelope
https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html
https://dev.mysql.com/doc/refman/5.7/en/creating-spatial-indexes.html

#1