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

[BACFL]

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!

[WolfShade]

Sorry for taking so long to respond.  Just now getting to my desk.

I forgot that you were going to sort by distance.  In that case, don't use the ?minimal parameter.  And BKBK has provided a way to use the returned information by deserializing the JSON.

Yes, this will work in CFHTTP.  I just prefer to use AJaX for things like this.  But that's purely subjective.

V/r,

^ _ ^

[BKBK · Accepted solution]

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>