Finding The Closest Metro Area Using Latitude and Longitude

[3dColor]

I have a database table of zipcodes that includes latitude and longitude coordinates.

CREATE TABLE `zipcodes` (
	`zip` INT(5) NOT NULL DEFAULT '0',
	`suburb` VARCHAR(30) NOT NULL DEFAULT '',
	`state` CHAR(2) NOT NULL DEFAULT '',
	`latitude` DECIMAL(10,6) NOT NULL DEFAULT '0.000000',
	`longitude` DECIMAL(10,6) NOT NULL DEFAULT '0.000000'
	PRIMARY KEY (`zip`),
	INDEX `zip` (`zip`),
	INDEX `latitude` (`latitude`),
	INDEX `longitude` (`longitude`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC

I also have a database table of major metro areas for the United States. Where 'csName' is the name of the metro area like Denver.

CREATE TABLE `cs` (
	`csId` INT(6) UNSIGNED NOT NULL AUTO_INCREMENT,
	`csName` VARCHAR(28) NOT NULL DEFAULT '',
	`stateFk` INT(5) UNSIGNED NULL DEFAULT NULL,
	`county` VARCHAR(60) NULL DEFAULT NULL
	PRIMARY KEY (`csId`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=733

What is known is a housing rental's latitude and longitude coordinates.
ie. 39.093808, -108.559622

I would like to query my 'zipcodes' table with an INNER JOIN to my 'cs' table to find the closest metro area (csName) to the rental's latitude and longitude coordinates.

I have an old Query that Rudy helped me out with a long time ago that I have been trying to modify but I can't seem to figure it out. Can anyone provide an input?

<cfquery name="GetNearestMetroFk" datasource="#DSN#">
SELECT city
     , MIN(distance) AS min_distance
     , AVG(distance) AS avg_distance
     , MAX(distance) AS max_distance
  FROM (
       SELECT zip
            , latitude
            , longitude
            , state
            , suburb
            , ROUND((ACOS((SIN(#GetCityState.latitude#/57.2958) *                        
                           SIN(latitude/57.2958)) +
                          (COS(#GetCityState.latitude#/57.2958) * 
                           COS(latitude/57.2958) *
                           COS(longitude/57.2958 - 
                               #GetCityState.longitude#/57.2958))))
                       * 3963) AS distance
         FROM zipcodes
        WHERE latitude >= #GetCityState.latitude# - #GetCityState.radiusMiles#/111
          AND latitude <= #GetCityState.latitude# + #GetCityState.radiusMiles#/111
          AND longitude >= #GetCityState.longitude# - #GetCityState.radiusMiles#/111
          AND longitude <= #GetCityState.longitude# + #GetCityState.radiusMiles#111
       ) AS data
GROUP
    BY city
ORDER 
    BY avg_distance ASC LIMIT 1
</cfquery>

 

[3dColor]

Wow, I can't believe I actually figured it out by myself!

<!--- Davenport FL 33836 closest to 172 --->
<cfset dbvlatitude = "30.476181">
<cfset dbvlongitude = "-83.403030">

<cfquery name="GetNearestMetroFk" datasource="#DSN#">
    SELECT 	zip
            , latitude
            , longitude
            , stateAbv
            , cs
            , city
            , ROUND((ACOS((SIN(#dbvlatitude#/57.2958) *                        
                SIN(latitude/57.2958)) +
                (COS(#dbvlatitude#/57.2958) * 
                    COS(latitude/57.2958) *
                    COS(longitude/57.2958 - 
                    #dbvlongitude#/57.2958))))
                * 3963) AS distance
    FROM 	zipcodes
    WHERE	topMetro = 1 
            AND stateAbv = 'FL'
	ORDER BY	distance
    LIMIT 1
</cfquery>

<cfdump var="#GetNearestMetroFk#" label="Geocode Information">