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

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

  • January 3, 2019
  • 3 Antworten
  • 2761 Ansichten

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!

    Dieses Thema wurde für Antworten geschlossen.
    Beste Antwort von 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 Antworten

    B
    BACFLAutor
    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 17, 2019

    BKBK,

    You can ignore my last question, I have it all working now.

    I can't tell you how much I appreciate your patience and help!

    Is there a way in the forum to give you extra points or kudos?

    If you are ever on the Gulf Coast of FL let me know and I owe you some drinks.

    Cheers!


    <cfscript>

    /*At this point, you have zipCodeQuery (sorted by distance) */

    zipMakeSQLString="SELECT zipcode, vehiclemake

                      FROM zipmake";

    /*Query result-set: all zipcodes and vehicle-makes */

    zipMakeQueryResult=queryExecute(zipMakeSQLString,{},{datasource="yourDatasourcename"});

    vehiclemakeArray=arrayNew(1);

    /*

       Loop through the rows of zipCodeQuery ;

       for each zipcode, get the corresponding vehiclemake;

       store the vehiclemakes in an array

    */

    for (i=1; i lte zipCodeQuery.recordcount; i=i+1) {

        vehiclemakeSQLString="SELECT vehiclemake

                              FROM zipMakeQueryResult

                              WHERE zipcode=:zip";

        vehicleMakeQueryResult=queryExecute(vehiclemakeSQLString,{zip={value=zipCodeQuery['zipcode']}},{dbtype="query"});

        vehiclemakeArray=vehicleMakeQueryResult["vehiclemake"][1];

    }

    /* Add the vehiclemake column as a new column of zipCodeQuery */

    queryAddColumn(zipCodeQuery,"vehiclemake","varchar",vehiclemakeArray)

    //writedump(zipCodeQuery)

    /*Test: a select query*/

    sqlString="SELECT *

               FROM zipCodeQuery

               WHERE zipcode=:testZip";

    qResultSet=queryExecute(sqlString,{testZip={value=34242}},{dbtype="query"});

    writedump(qResultSet);

    </cfscript>

    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.

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