MySQL Query To Select Closest City -

i trying repeat following query rows. trying map closest city (based on latitude , longitude) places latitude , longitude. have table places contains places need mapped, , table citytable places matched to. have following query works single row:

select p.placeid, p.state, p.city, p.county, p.name,         sqrt(pow((69.1 * (p.lat - z.latitude)), 2 )         + pow((53 * (p.lng - z.loungitude)), 2)) distance,        p.lat,p.lng,z.latitude,z.loungitude,z.city  places p,citytable z  p.placeid = 1  order distance asc  limit 1; 

this works single location. need remove constraints apply entire table.the problem encountering seems want make copy compare every other element in table. example, if there 100 rows in p , 100 rows in z, resulting table seems 10,000 rows. need table of size count(*) p. ideas? also, there more efficient ways if table p contains on million rows? thanks.

you can find nearest city place using:

select p.placeid, p.state, p.city, p.county, p.name,         (select z.city         citytable z         order sqrt(pow((69.1 * (p.lat - z.latitude)), 2 ) + pow((53 * (p.lng - z.loungitude)), 2))          limit 1        ) city,        p.lat, p.lng places p order distance asc; 

(if want additional city information, join city table in on city.)

this doesn't solve problem of having cartesian product. does, however, frame in different way. if know city within 5 degrees longitude/latitude of place, can make subquery more efficient:

       (select z.city         citytable z         z.lat >= p.lat + 5 , z.lat <= p.lat - 5 ,               z.long <= p.long + 5 , z.long <= p.lat - 5         order sqrt(pow((69.1 * (p.lat - z.latitude)), 2 ) + pow((53 * (p.lng - z.loungitude)), 2))          limit 1        ) city,        p.lat, p.lng; 

this query use index on lat. might use index on lat, long.

if isn't sufficient, might consider way of reducing search space, looking @ neighboring states (in us) or countries.

finally, may want consider geospatial extensions mysql if dealing type of data.