Hi,
Usually I can think of ways to do queries like this, but the only solutions I can think of are post row fetching and was wondering if there were another way.
Brief background: You know how dating sites have the ability to pull people from a database within a certain range of miles? Well I have a longitude and latitude and zipcode database and distance calculating function. I have every person in my database attached to their zipcode, upon sending the zipcode to the database I can retreive their long and lat and process them with the zipcode information I already have on the person who is searching to find the distance.
Here is the problem, I don't want to have to search each row pulled from the database to see if its distance is under 10 miles away.
Do you think there would be a way, I can work with pseudo code(sp), to check the distance in the query?
Or am I just dreaming?
I really haven't done this sort of stuff with MySQL as of yet, so it's kind of interesting to learn.
Thanks,
-Timo
Ps: If you're looking for a free MySQL SQL or MS SQL db with the zip code, long, and lat, and formula/codes I found this while searching... <!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->
It's, of course, free.
<!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->
Zip Contents:
* mssql_stored_proc.txt - includes stored procedure for performing zip search on MS SQL server
* mysql_query.txt - includes sample query for performing zip search on MySQL server
* ZipCodes.mdb - Zip Codes database, with ZipCode, Latitude, Longitude, and State fields. Use this to import data to other databases. Radius seaches will NOT work with MS Access.
* ZipCodes.sql - MySQL Database Dump for importing data into MySQL
did you look in mysql_query.txt??
if not here it is
# demo query for SQL searches for zipcodes
# up to 50 miles away from zip 54915
SELECT o.ZipCode, o.State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ZipCodes z,
ZipCodes o,
ZipCodes a
WHERE z.ZipCode = 54915 AND
z.ZipCode = a.ZipCode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) <= 50
ORDER BY DistanceOriginally posted by illogique
<!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->
did you look in mysql_query.txt??
if not here it is
Nope, I thought he was only going to show how to use the forumla, never knew he was going to actually show how to do all that.
Thanks illogique for showing me I'm pretty illogical myself.
:rocker: haha no really, thanks.
-Timo
Usually I can think of ways to do queries like this, but the only solutions I can think of are post row fetching and was wondering if there were another way.
Brief background: You know how dating sites have the ability to pull people from a database within a certain range of miles? Well I have a longitude and latitude and zipcode database and distance calculating function. I have every person in my database attached to their zipcode, upon sending the zipcode to the database I can retreive their long and lat and process them with the zipcode information I already have on the person who is searching to find the distance.
Here is the problem, I don't want to have to search each row pulled from the database to see if its distance is under 10 miles away.
Do you think there would be a way, I can work with pseudo code(sp), to check the distance in the query?
Or am I just dreaming?
I really haven't done this sort of stuff with MySQL as of yet, so it's kind of interesting to learn.
Thanks,
-Timo
Ps: If you're looking for a free MySQL SQL or MS SQL db with the zip code, long, and lat, and formula/codes I found this while searching... <!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->
It's, of course, free.
<!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->Zip Contents:
* mssql_stored_proc.txt - includes stored procedure for performing zip search on MS SQL server
* mysql_query.txt - includes sample query for performing zip search on MySQL server
* ZipCodes.mdb - Zip Codes database, with ZipCode, Latitude, Longitude, and State fields. Use this to import data to other databases. Radius seaches will NOT work with MS Access.
* ZipCodes.sql - MySQL Database Dump for importing data into MySQL
did you look in mysql_query.txt??
if not here it is
# demo query for SQL searches for zipcodes
# up to 50 miles away from zip 54915
SELECT o.ZipCode, o.State,
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) AS Distance
FROM ZipCodes z,
ZipCodes o,
ZipCodes a
WHERE z.ZipCode = 54915 AND
z.ZipCode = a.ZipCode AND
(3956 * (2 * ASIN(SQRT(
POWER(SIN(((z.Latitude-o.Latitude)*0.017453293)/2),2) +
COS(z.Latitude*0.017453293) *
COS(o.Latitude*0.017453293) *
POWER(SIN(((z.Longitude-o.Longitude)*0.017453293)/2),2)
)))) <= 50
ORDER BY DistanceOriginally posted by illogique
<!-- m --><a class="postlink" href="http://www.kevinroth.com/projects/project.asp?id=434">http://www.kevinroth.com/projects/project.asp?id=434</a><!-- m -->
did you look in mysql_query.txt??
if not here it is
Nope, I thought he was only going to show how to use the forumla, never knew he was going to actually show how to do all that.Thanks illogique for showing me I'm pretty illogical myself.
:rocker: haha no really, thanks.
-Timo