Highlighted

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

Participant ,
Jan 02, 2019

Copy link to clipboard

Copied

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!

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

723

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Participant ,
Jan 02, 2019

Copy link to clipboard

Copied

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!

Adobe Community Professional
Correct answer by BKBK | Adobe Community Professional

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>

Views

724

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Jan 02, 2019 0
LEGEND ,
Jan 03, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2019 0
Adobe Community Professional ,
Jan 03, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2019 0
LEGEND ,
Jan 03, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2019 0
LEGEND ,
Jan 03, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2019 0
Participant ,
Jan 03, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 03, 2019 0
LEGEND ,
Jan 04, 2019

Copy link to clipboard

Copied

The only thing you need are the zip code(s).  Add "?minimal" to the URL, and you'll get back:

http://www.zipcodeapi.com/rest/AeNuIWoSYJ0d8LVO71lBaVA5MGyIzIlA2WTe2moAmSEU4siXIDTS4he9y3NgLlJG/radi...

{"zip_codes":["63099","63127","63088","63126","63122","63119","63131","63144","63124"]}

From there, I've never worked with JSON that much.  "zip_codes" is an array within JSON.  So assuming that the above is assigned to a variable called data:

(I haven't tested this)

zcList = data.zip_codes.join(",");

Should result in:

"63099,63127,63088,63126,63122,63119,63131,63144,63124" which can be passed to your query as is.

SELECT colA, colB, colC

FROM tableA

WHERE zip_code in (<cfqueryparam value="#zcList#" list="yes" />)

HTH,

^ _ ^

UPDATE:  I have tested the JavaScript part, and it does, indeed, work.

<script>

testing = {"zip_codes":["63099","63127","63088","63126","63122","63119","63131","63144","63124"]};

alert(testing.zip_codes.join(","));

</script>

Load the page, get an alert:  63099,63127,63088,63126,63122,63119,63131,63144,63124

UPDATE2: If you wanted to, you could sort the array in ascending order.

<script>
testing = {"zip_codes":["63099","63127","63088","63126","63122","63119","63131","63144","63124"]};

testing.zip_codes.sort(function(a,b){return a-b;});
alert(testing.zip_codes.join(","));
</script>

Load this code, and get an alert:  63088,63099,63119,63122,63124,63126,63127,63131,63144

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2019 0
LEGEND ,
Jan 04, 2019

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 04, 2019 0
Participant ,
Jan 05, 2019

Copy link to clipboard

Copied

Wolfshade,

Thank you for your help!

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

Also, I was going to use CFHTTP. I'm not sure how to do AJAX.  Will this not work with CFHTTP?

Thanks!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 05, 2019 0
Adobe Community Professional ,
Jan 06, 2019

Copy link to clipboard

Copied

BACFL  wrote

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

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 06, 2019 1
LEGEND ,
Jan 07, 2019

Copy link to clipboard

Copied

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,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 07, 2019 1
Participant ,
Jan 09, 2019

Copy link to clipboard

Copied

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?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2019 0
Adobe Community Professional ,
Jan 09, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 09, 2019 0
LEGEND ,
Jan 10, 2019

Copy link to clipboard

Copied

A database isn't necessary.  As BKBK pointed out, you can convert the JSON into a query object and sort the query object using Java.  (As opposed to using BKBK's suggestion of a QoQ, which seems like an extra step.)

<cfset qQuery.sort(qQuery.findColumn("nameOfSortColumn"), TRUE)>

<!--- TRUE = ascending, FALSE = descending --->

HTH,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 10, 2019 0
Participant ,
Jan 10, 2019

Copy link to clipboard

Copied

BKBK and Wolfshade, thanks again for your help.  I am really lost on this. 

I know how to do the CFHTTP but after that I get a bit lost.  Is it possible you can go step by step, assume I know nothing, from the retrieval of the JSON from the CFHTTP on down please?  For instance I don't understand the deserialize JSON, how I get the varialbles from that and then how I sort it and query against my database.  Again my database is set up in the following manner:

Col1          Col2

Zipcode    Vehicle make

So I need to match the zips and then extract the zipcode and vehicle make and then order the zipcodes by distance

Thanks so much for your help!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 10, 2019 0
Adobe Community Professional ,
Jan 10, 2019

Copy link to clipboard

Copied

You said earlier,

Thanks everyone, this was very helpful!

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

Apparently, your initial question, "How do I Get the Zip Codes Within a Radius and Query Them to a Local Database", had already been answered. Please mark the correct answer.

You may then proceed to ask further questions, of course,

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 10, 2019 0
Participant ,
Jan 10, 2019

Copy link to clipboard

Copied

BKBK I marked one of your answers as correct.

So my further questions are:

I know how to do the CFHTTP but after that I get a bit lost.  Is it possible you can go step by step, assume I know nothing, from the retrieval of the JSON from the CFHTTP on down please?  For instance I don't understand the deserialize JSON, how I get the varialbles from that and then how I sort it and query against my database.  Again my database is set up in the following manner:

Col1          Col2

Zipcode    Vehicle make

So I need to match the zips and then extract the zipcode and vehicle make and then order the zipcodes by distance

Thanks so much for your help!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 10, 2019 0
Adobe Community Professional ,
Jan 13, 2019

Copy link to clipboard

Copied

Ok, let us start by assuming the zipCodeJSON is given. You can convert this JSON string to a ColdFusion object in which arrays contain the data. To do so, use the function deserializeJSON.

What then do you know about the resulting ColdFusion object? The JSON contains a hint.

In ColdFusion we know that {} stands for struct and [] for array. Consider, for example, the following JSON

zipCodeJSON='{

  "zip_codes": [

  {

  "zip_code": "34238",

  "distance": 4.647,

  "city": "Sarasota",

  "state": "FL"

  },

  {

  "zip_code": "34242",

  "distance": 0,

  "city": "Sarasota",

  "state": "FL"

  }

  ]

}';

It has the structure,

{"zip_codes": [ {}, {} ] }

So it represents a struct containing an array called zip_codes. The array contains two structs which hold the data. That is what you will obtain when you deserialize the JSON:

zipCodeStructure=deserializeJSON(zipCodeJSON);

//writedump(zipCodeStructure);

In fact, you will see this when you dump the structure:

I shall now loop through the array elements, storing each as a row in a query.

zipCodeStructure=deserializeJSON(zipCodeJSON);

//writedump(zipCodeStructure);

// Define new, empty query

zipCodeQuery=queryNew("id,jsonName,city,distance,state,zipCode","Integer,Varchar,Varchar,Decimal,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);

    });

   

// Sort by distance (assuming you're on CF 11 or older)

//zipCodeQuery.sort(zipCodeQuery.findColumn("distance"),true);

//writedump(zipCodeQuery);

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 13, 2019 1
Participant ,
Jan 15, 2019

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 15, 2019 0
Adobe Community Professional ,
Jan 16, 2019

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 16, 2019 1
Participant ,
Jan 16, 2019

Copy link to clipboard

Copied

BKBK,

I didn't realize it was scripting and I'm not familiar with scripting, but good news! I put <CFSCRIPT> around your code and I am getting the output.

My final question, hopefully, is where do I put my database query in the code so that I can retrieve data matching on zipcode

Such as:

SELECT zipcode, vehiclemake

FROM zipmake

WHERE zipcode = #zipCode#

I am guessing from your code I would be searching the DB on #zipCode#

Thanks!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 16, 2019 0
Participant ,
Jan 16, 2019

Copy link to clipboard

Copied

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!

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 16, 2019 0
LEGEND ,
Jan 17, 2019

Copy link to clipboard

Copied

BACFL  wrote

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

You can "like" his answer, which will award points to BKBK.

V/r,

^ _ ^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 17, 2019 1
Adobe Community Professional ,
Jan 17, 2019

Copy link to clipboard

Copied

Hi BACFL​,

Thanks for the compliment. I get enough satisfaction from discussing with fellow developers and helping whenever I can.

I see your bigger picture. Let me put it down to confirm we're on the same track.

You have a database table called zipmake. It has 3 columns,

id(int, Primary Key)

zipcode(int)

vehiclemake(varchar)

It contains all your vehicle-makes and their respective zip codes. You wish to do a join, on zipcode, between the zipmake table and the dynamic query result,  zipCodeQuery.

If so, then the obvious method to use is query-of-a-query. However, its implementation of Joins is limited. To get around this, we could use the following strategy:

  1. select all zipcode and vehiclemake rows from zipmake;
  2. loop through the rows of zipCodeQuery and, for each zipcode, get the corresponding vehiclemake from 1.;
  3. create a column containing the vehiclemakes;
  4. add the column to zipCodeQuery.

This expanded zipCodeQuery has all the information you need. And dynamically, too. Some code suggestions follow.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 17, 2019 1
Adobe Community Professional ,
Jan 17, 2019

Copy link to clipboard

Copied

<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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 17, 2019 0
BACFL LATEST
Participant ,
Jan 17, 2019

Copy link to clipboard

Copied

BKBK,

I simplified the table  in my example to make it easier, but below is the solution I came up with.  My next step would be to try to do a cfstoredprocedure because of the time it is taking to loop 500 miles worth of zip codes, I'm shaky on the cfstoredprocedure side and the SQL server stored procedure side.  Anyhow here is my current code that is working.and thanks again for all of your help.

<CFHTTP  url="http://www.zipcodeapi.com/rest/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/radius.json/34242..." method = "get" result="httpResp">

</cfhttp>

<!----

<cfdump var="#httpResp#" />

<cfscript>

writeDump(var = httpResp.filecontent, label = "Zipcodes");

</cfscript>

---->

</cfoutput>

<cfset zipCodeJSON='#httpresp.filecontent#'>

<cfset zipCodeStructure=#deserializeJSON(zipCodeJSON)#>

<!----

<cfdump var="#zipCodeStructure#">

---->

<cfscript>

zipCodeQuery=queryNew("id,city,distance,state,zipCode","Integer,Varchar,Decimal,Varchar,Integer");

for (row=1; row lte arrayLen(zipCodeStructure.zip_codes); row=row+1) {

    rowdata=[row,zipCodeStructure.zip_codes[row].city,zipCodeStructure.zip_codes[row]. distance,zipCodeStructure.zip_codes[row].state,zipCodeStructure.zip_codes[row].zip_code];

    queryAddRow(zipCodeQuery,rowdata);

}

zipCodeQuery.sort(zipCodeQuery.findColumn("distance"),true);

</cfscript>

<!----

writedump(zipcodequery.zipcode);

writedump(zipCodeQuery);

---->

 

<cfloop query=zipcodequery>

<!---- add back in for make and order (if order other than distance is wanted, check what others do)

b.makeid = '#makeid#' and c.makeid = '#makeid#' and *** see search.cfm for order

*** add back in where c.vehicleid = d.vehicleid if I ONLY want live auctions

----->

<cfquery datasource="#datasource#" dbname="#dbname#" password="#password#" name="getthem">

SELECT a.model, b.make, c.model_year,c.image1,c.vehicleid,c.trim_level,d.auctionid,d.enddater,d.openingprice,d.highbid,d.currentbid,d.origenddater,c.memberid,d.memberid,a.modelid, c.vin

FROM  modelsfinal a, makesfinal b,vehicles c,auctions d

WHERE  b.makeid = a.makeid and c.modelid = a.modelid and zip = #zipcode#

group by vin, model, make, model_year, image1, trim_level, auctionid, enddater, openingprice,highbid,currentbid,origenddater,c.vehicleid,d.memberid,c.memberid,a.modelid

</cfquery>

<cfoutput query=getthem group=vin>

<cfif #getthem.model_year # is "">

Model Blank - #zipcode#

<cfelse>

#zipcodequery.zipcode# #zipcodequery.city# #zipcodequery.distance# #getthem.make# #getthem.model#<br>

</cfif>

</cfoutput>

</cfloop>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 17, 2019 0
Adobe Community Professional ,
Jan 05, 2019

Copy link to clipboard

Copied

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>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Jan 05, 2019 1