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
    Legend
    January 4, 2019

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

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    January 10, 2019

    BKBK  wrote

    JSON data => Array => Query is in the direction of increasing data structure. After coming all this way, why look back to JSON for sorting your data?

    Possible solution (off the top of my head):

    1. Create a database table having columns id(int, primary-key), jsonName(varchar), city(varchar), distance(float), state(char), zip(int)
    2. You then need a simple extension of the above code to transfer the data from the arrays to the database table.
    3. To get sorted data, just do a select-query with the appropriate order-by clause

    I don't think writing to a database will work as there could be 1,000s of API requests at one time so how would I know how to query the results just for a single user?


    Ah, so when you say,

    Is there a way to use the distance information that is provided in the JSON?  I need to be able to sort by distance.

    we have to assume you wish to do it dynamically. If so, my suggestion still stands.

    Forget the database and do it on the fly. Use queryNew() and queryAddColumn and the above arrays to create a query object from each JSON. Then do query-of-query.

    WolfShade
    WolfShade
    Legend
    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
    Legend
    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
    Legend
    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
    Community Expert
    BKBKCommunity Expert
    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