MySQL Union 在两个表上根据 GIS 点查找最接近的结果

发布于 2024-12-01 11:47:57 字数 1059 浏览 0 评论 0原文

我正在尝试查找海滩的潮汐信息。我有两个带有经纬度值的海滩表，在提取海滩结果时，我还想查询潮汐表并根据潮汐中最近的纬度经度确定该海滩的高潮和低潮桌子。我有两个表，其结构如下：

这是我的表结构：

table 'beaches'
pri_id    location    lat         lng
1         seal beach  38.344      -122.877
2         newport     37.877      -121.988
3         rocks       34.987      -122.344


table 'tides'
pri_id    lat        lng        low_time   high_time
1         38.565     -123.454   05:00      13:00
2         42.343     -121.234   06:00      14:00
3         42.453     -122.433   05:30      13:30

我当然不想在这里听起来很懒，只是没有找到很多有关最佳方法的信息。本质上我需要获得 beachs.lat、beachs.lng值，然后查询潮汐表，找到最近的tides.lat和tides.lng，然后根据最近的位置返回tides.high_time和tides.low_time。

我正在使用这个联合，但没有返回任何结果。

(SELECT tide_date,tide_time,lat,lng FROM (SELECT * FROM tides ORDER BY lat ASC, lng ASC) a WHERE a.lat<=200 AND a.lng<=200 LIMIT 0,5)
UNION
(SELECT location,lat, lng FROM (SELECT * FROM beaches ORDER BY lat ASC, lng ASC) b
WHERE b.lat>200 AND b.lng>200
LIMIT 0,5)

我确信有一种更精确的方法可以做到这一点，只是我无法理解这一点。

原文

I am attempting to locate tide information for beaches. I have a two table of beaches with latitude longitude values, and when pulling up the results for the beaches, I also want to query the tides table and ascertain the high and low tide for this beach, based on the nearest latitude longitude in the tides table.
I have two tables which are structured below:

Here is my table structure:

table 'beaches'
pri_id    location    lat         lng
1         seal beach  38.344      -122.877
2         newport     37.877      -121.988
3         rocks       34.987      -122.344


table 'tides'
pri_id    lat        lng        low_time   high_time
1         38.565     -123.454   05:00      13:00
2         42.343     -121.234   06:00      14:00
3         42.453     -122.433   05:30      13:30

I certainly dont mean to sound lazy here, just not finding a lot of information about the best way to do this.
Essentially I need to gain
beaches.lat, beaches.lng values, then query the tides table, find the nearest tides.lat and tides.lng and then return the tides.high_time and tides.low_time based on the nearest location.

I am using this union, but getting no results returned.

(SELECT tide_date,tide_time,lat,lng FROM (SELECT * FROM tides ORDER BY lat ASC, lng ASC) a WHERE a.lat<=200 AND a.lng<=200 LIMIT 0,5)
UNION
(SELECT location,lat, lng FROM (SELECT * FROM beaches ORDER BY lat ASC, lng ASC) b
WHERE b.lat>200 AND b.lng>200
LIMIT 0,5)

I am sure there is a more precise method to do this, just cant get my head wrapped around this.

分享到QQ

分享到微博