Skip to main content
B
BACFL
Inspiring
January 3, 2019
Answered

How do I Get the Zip Codes Within a Radus and Query Them to a Local Database

  • January 3, 2019
  • 3 replies
  • 2730 views

I have a database of vehicles that have zip codes associated with them.

I want to allow the user to do a search, i.e. Within 40 miles of zip code 19606.  An API will be queried, I will match results to my database and display the vehicles by distance from 19606.

More deeply, although you probably have better ideas, this will send the distance and zip parameters via an API and return all zip codes and distance from the initial zip code.   I will then take these zip codes and query my local database to get zip code results and sort them by distance from the initial zip code.

Alternatively I have searched and have seen people use their existing zip codes and adding lat/long data and then using some magic (ok some formula) figuring out which zip codes are within the radius and how far they each are from the original zip code.

I have searched and most of the questions relating to this topic are rather old so I'm open to any and all ideas.

Cheers!

    This topic has been closed for replies.
    Correct answer BKBK

    BACFL  wrote

    How do I parse the JSON data and then what type of query do I use to the database  so that the zip codes in my results are then displayed on the webpage ordered by distance from the initial zip code?  In this instance I queried 5 miles from 34242. 

    For ease, let's presume my database has two columns, "zipcode" and "make" of the vehicle I want to display,

    i.e.

    Ford 34242

    Nissan 34233

    Mazda 34236

    Here is the sample JSON for a 5 mile radius of 34242:

    {

      "zip_codes": [

    ...

      ]

    }

    One suitable function to use here is deserializeJSON.

    <cfscript>

    zipJSON='{

      "zip_codes": [

      {

      "zip_code": "34238",

      "distance": 4.647,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34242",

      "distance": 0,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34231",

      "distance": 1.531,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34233",

      "distance": 4.171,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34239",

      "distance": 3.698,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34236",

      "distance": 3.964,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34230",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34276",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34277",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34278",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      }

      ]

    }';

    zipStruct=deserializeJSON(zipJSON);

    /* Enable this dump and you will see a structure containing an array called zip_codes */

    // writedump(zipStruct);

    /*

    The array, zip_codes, contains 10 elements, each a struct.

    Here follows an example to obtain the details of the last (10th) item.

    */

    //city10=zipStruct.zip_codes[10].city;

    //distance10=zipStruct.zip_codes[10].distance;

    //state10=zipStruct.zip_codes[10].state;

    //zipcode10=zipStruct.zip_codes[10].zip_code;

    /*

    Alternatively, should you want to obtain each value dynamically:

    */

    city=arrayNew(1);

    distance=arrayNew(1);

    state=arrayNew(1);

    zipcode=arrayNew(1);

    for(i=1; i lte arrayLen(zipStruct.zip_codes); i=i+1) {

        city=zipStruct.zip_codes.city;

        distance=zipStruct.zip_codes.distance;

        state=zipStruct.zip_codes.state;

        zipcode=zipStruct.zip_codes.zip_code;

    }

    </cfscript>

    3 replies

    B
    BACFLAuthor
    Inspiring
    January 4, 2019

    Thanks everyone, this was very helpful!

    I looked into zipcodeapi.com and it looks like it may have the solution.

    So my next question,being a bit rusty with my CF coding, where do I go from here? Below is the JSON returned from the API request.

    How do I parse the JSON data and then what type of query do I use to the database  so that the zip codes in my results are then displayed on the webpage ordered by distance from the initial zip code?  In this instance I queried 5 miles from 34242. 

    For ease, let's presume my database has two columns, "zipcode" and "make" of the vehicle I want to display,

    i.e.

    Ford 34242

    Nissan 34233

    Mazda 34236

    Here is the sample JSON for a 5 mile radius of 34242:

    {

      "zip_codes": [

      {

      "zip_code": "34238",

      "distance": 4.647,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34242",

      "distance": 0,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34231",

      "distance": 1.531,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34233",

      "distance": 4.171,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34239",

      "distance": 3.698,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34236",

      "distance": 3.964,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34230",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34276",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34277",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      },

      {

      "zip_code": "34278",

      "distance": 4.818,

      "city": "Sarasota",

      "state": "FL"

      }

      ]

    }

    Thanks again.

    WolfShade
    WolfShade
    Brainiac
    January 4, 2019

    This is, of course, assuming that you're using AJaX to get the data from the API, and not CFHTTP.

    BKBK
    BKBK
    Community Expert
    January 16, 2019

    BKBK,

    Thanks, and sorry for the slow reply.  We're getting there.

    I did the following and it works, I get the same structure output that you got. But...

    <cfset zipCodeJSON='{
      "zip_codes": [
      {
      "zip_code": "34238",
      "distance": 4.647,
      "city": "Sarasota",
      "state": "FL"
      },
      {
      "zip_code": "34242",
      "distance": 0,
      "city": "Sarasota",
      "state": "FL"
      }
      ]
    }'>
    <cfset zipCodeStructure=#deserializeJSON(zipCodeJSON)#>
    <cfdump var="#zipCodeStructure#">

    But then I don't know how I am supposed to code the rest of it.  CFLOOP?  I'm really not sure at this point.

    zipCodeQuery=queryNew("id,jsonName,city,distance,state,zipCode","Integer,Varchar,Varchar,D ecimal,Varchar,Integer")
    // Add each element of the zip_codes array as a row of the query
    for (row=1; row lte arrayLen(zipCodeStructure.zip_codes); row=row+1) {
        // I have included row number and "test" to each row.
        // "Test" is just an arbitrary name. You might need a name to distinguish between JSONs
         rowdata=[row,"test",zipCodeStructure.zip_codes[row].city,zipCodeStructure.zip_codes[row].  distance,zipCodeStructure.zip_codes[row].state,zipCodeStructure.zip_codes[row].zip_code];
        queryAddRow(zipCodeQuery,rowdata)
    }
    // Sort by distance (assuming you're on CF2016 or CF2018)
    querySort(zipCodeQuery,function(obj1,obj2){
            return compare(obj1.distance,obj2.distance);
        });
    Thanks again for your help!

    BACFL  wrote

    But then I don't know how I am supposed to code the rest of it.  CFLOOP?  I'm really not sure at this point.

    How you're supposed to code the rest of it? I don't understand what you mean, as the code is already given. The statement for (...) {...} is a loop.

      WolfShade
      WolfShade
      Brainiac
      January 3, 2019

      Zip Code API (www.zipcodeapi.com) looks like what you're looking for.  However, they charge if your site makes more than 50 requests per hour, and it's a sliding-scale.

      Zippopotamus (www.zippopotam.us) is free, but I don't think it will do zip codes in a radius.

      Zip-codes (http://www.zip-codes.com/content/api/samples/FindZipCodesInRadius.html) is also free AND has radius search.

      HTH,

      ^ _ ^

      WolfShade
      WolfShade
      Brainiac
      January 3, 2019

      The downside to Zip-codes is not all municipality names are used.  For example, the municipality that I live in isn't used when I enter my zip code - it says "SAINT LOUIS", not my municipality name.

      That's really no huge deal, unless you are anal-retentive like myself.  (Is "anal-retentive" supposed to be hyphenated??)

      V/r,

      ^ _ ^

      WolfShade
      WolfShade
      Brainiac
      January 3, 2019

      Going out on a limb, as I've never done anything like what you are asking for.  But I'm guessing the US Postal Service probably has an API that does precisely what you seek.  If so, they would have the instructions for querying their database in API documentation.

      HTH,

      ^ _ ^

      BKBK
      BKBK
      Community Expert
      January 3, 2019

      Follow WolfShade​'s suggestion and google zip code api.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices