MySQL Union on two tables finding nearest results based on GIS points

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 t开发者_Python百科his.

You might get better help if you put your query on http://gis.stackexchange.com, where the readers deal with this kind of data all the time.

You're looking to calculate a haversine formula, and there are several ways to do this.

Here is a page I found with some sample haversine calculations.
http://www.movable-type.co.uk/scripts/latlong.html

Here's a link with some trig function syntax in SQL. I've not used MySql, so I don't know if all trig functions are supported. http://www.coderecipes.net/sql-trigonometric-functions.aspx

Of course, there's plenty of discussion as to what's better... https://gis.stackexchange.com/questions/4906/why-is-law-of-cosines-more-preferable-than-haversine-when-calculating-distance-be

I'm sorry I can't just whack out the SQL you want, but I hope this helps.