Big Math in SQL

[johnnyb]

Hi,

So, I have this table of postal codes, (what we call ZIP codes up here in the great white north), and their associated latitudes & longitudes. I am developing an application that searches for stores within X kilometers of a given postal code. First step: Make a query that will return all postal codes within x kilometers of that postal code. After that some joins will be easy.

Here's what I have:


SELECT postalcodes.postalcode,
(ACOS(
COS(RADIANS('.$point_item['Latitude'].'))
*COS(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*COS(RADIANS(postalcodes.Longitude))
+
COS(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*SIN(RADIANS(postalcodes.Longitude))
+
SIN(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
)
* 6378.15) AS distance
FROM postalcodes WHERE

(ACOS(
COS(RADIANS('.$point_item['Latitude'].'))
*COS(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*COS(RADIANS(postalcodes.Longitude))
+
COS(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*SIN(RADIANS(postalcodes.Longitude))
+
SIN(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
)
* 6378.15)
< 500



So, all of the big math gives me the distance, (great circle route even!). $point_item['latitude'] and $point_item['longitude'] are the lat & lon of the originating postal code. The <500 at the end means I am searching within 500 kilometers of the originating postal code.

So, I should have some results, right? But no :( My query returns 0 rows. Apparently I'm doing something wrong in the SQL, any pointers?

Also, I'd like to not have to run the first query to get the lat & lon of the originating postal code. I think I should be able to run it all in one query, I just don't know what that query is. Finally, I'd like to avoid typing the math twice if possible. I tried some subqueries in the first portion of the statement but was told by my server that I had an error in my SQL syntax.

Anyhow, if anyone knows what I'm doing wrong please let me know!

Thanks a lot.

John

[raf]

lets start with the first problem --> no rows returned.

what do you get when you run:

SELECT postalcodes.postalcode,
(ACOS(
COS(RADIANS('.$point_item['Latitude'].'))
*COS(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*COS(RADIANS(postalcodes.Longitude))
+
COS(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
*COS(RADIANS(postalcodes.Latitude))
*SIN(RADIANS(postalcodes.Longitude))
+
SIN(RADIANS('.$point_item['Latitude'].'))
*SIN(RADIANS('.$point_item['Longitude'].'))
)
* 6378.15) AS distance
FROM postalcodes LIMIT 100

also, what MySQL version are you running?

[guelphdad]

Here (http://forums.devshed.com/mysql-help-4/sql-queries-help-find-nearby-within-x-km-using-lat-and-315358-2.html) is a thread from another forum I frequent. It may give you some help with what you are trying to accomplish, though I see your calculations are a bit more exact.

[johnnyb]

For raf: I have solved the first problem. I had the math wrong so I was getting the wrong answer and therefore returning no rows. I now get rows as expected since I updated to the second formula posted here.

Now, I just want to make everything run in one query, and if possible not have to type out the math twice.

For guelphdad: I just took a look over there, thanks. They appear to need 2 queries as well, perhaps we can get a solution here & post it over there too!

[guelphdad]

I just took a look over there, thanks. They appear to need 2 queries as well, perhaps we can get a solution here & post it over there too!

You don't have two queries above but a single query.

The only thing that may work is this (pseudocode)


select postalcode,
mathstuff as distance
from yourtable
group by postalcode
having distance < 500


you can't refer to distance in a where clause since it is a calculated column, but you can in a having clause. Though I do remember on a couple of occassions that you have to spell the entire query out again becuase of the specific limitations on how mysql does things. (i.e. would work in oracle or ms sql but doesn't in mysql)

Of course spelling out the math clause in both cases does work.

[johnnyb]

Hi,

I haven't written the first query above, that's why there's only one. I just explained it. The input from the user will be a postal code and a distance, so, I need to find all postal codes within the distance from the postal code that the user enters.

Currently I have 2 queries 1) (not shown above) Finds the lat & lon of the postal code that the user entered (SELECT lat,lon FROM postcodes WHERE postalcode=user-entered-data). 2) (shown above) finds all postal codes within the given distance of the user-entered postal code.

What I want to do is combine both queries into one.

John