Solved

Need CFML help with GoogleGeocoder3

Posted on 2011-09-30
9
320 Views
Last Modified: 2012-05-12
For my real estate site, I thought it would be nice to have all the latitude and longitude coordinates for my properties in a database table for quick lookup. I did some research and found http://googlegeocoder3.riaforge.org/ . I installed the googlegeocoder3.cfc and was able to get the example template (attached below) working with my query and its returning results like this:

[Record # 1]
FORMATTED_ADDRESS: 2633 Oak St, Carrabelle, FL 32322, USA
LATITUDE: 29.8929689
LOCATION_TYPE: RANGE_INTERPOLATED
LONGITUDE: -84.5795465
RESULT_TYPE: street_address
STATUS: OK
query

 
[Record # 1]
FORMATTED_ADDRESS: 2637 Oak St, Carrabelle, FL 32322, USA
LATITUDE: 29.8941504
LOCATION_TYPE: RANGE_INTERPOLATED
LONGITUDE: -84.5769592
RESULT_TYPE: street_address
STATUS: OK
query

My question is how can I get the data into my table with the corresponding ListID of the property?

Thanks!
<cfset newlisting = "#DateFormat(DateAdd("d", -6, Now()), "YYYY-MM-DD")#">
<cfquery name="mytable" datasource="myds">
SELECT * from mytable WHERE Listdate >= '#newlisting#' AND PropType NOT LIKE '%rental%'
ORDER BY Price DESC
</cfquery>
<cfoutput query="listings">
  <cfinvoke component="googlegeocoder3" method="googlegeocoder3" returnvariable="variables.geocode_query">
  <cfinvokeargument name="address" value="#StreetNumber# #StreetName#, #City#, #State# #Zip#">
  <cfinvokeargument name="ShowDetails" value="false">
  </cfinvoke>
  <cfdump var="#variables.geocode_query#" format="text">
  <cfset sleep(200)>
</cfoutput>

Open in new window

0
Comment
Question by:Bang-O-Matic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 

Author Comment

by:Bang-O-Matic
ID: 36894163
BTW -  Windows server / CF9 / MySQL 5.5
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36894243
> My question is how can I get the data into my table with the corresponding ListID of the property?

I don't see a ListID anywhere ?



You query "geocode_query"  is like a regular query with columns:

FORMATTED_ADDRESS
LATITUDE
LONGITUDE
LOCATION_TYPE
RESULT_TYPE
STATUS: OK

Just insert the ones you want in to your table... I don't really understand the question I guess..

   '#geocode_query.latitude#'

etc...

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36894254
oooh,  is there a chance that

<cfquery name="mytable" datasource="myds">
SELECT * from mytable WHERE Listdate >= '#newlisting#' AND PropType NOT LIKE '%rental%'
ORDER BY Price DESC
</cfquery>

The "MyTable" query is the same as the ...

<cfoutput query="listings">

Listings query, looped here?   ... changed names to protect the innocent?


And pehaps that query with the table:  "MyTable"  has the ListID in it?

0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:Bang-O-Matic
ID: 36894295
Hi gdemaria, yes I made a typo there, the query "listings" does include ListID of the property. I've never taken a dumped query and inserted into a table, I've always just parsed from a  txt file or inserted via form, so I guess my main stumbling block here is how I can take these results http://www.floridastgeorgeisland.com/googlegeocode3/test_geocode.cfm generated here and get them in a table along with the ListID?

Thanks
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 36894340
Ok, well, it seems that you are looping the listings, with ListID... for each iteration of the loop you are getting the coordinates...

So you just need to take the two query values and insert them into your table...




<cfoutput query="listings">
  <cfinvoke component="googlegeocoder3" method="googlegeocoder3" returnvariable="variables.geocode_query">
  <cfinvokeargument name="address" value="#StreetNumber# #StreetName#, #City#, #State# #Zip#">
  <cfinvokeargument name="ShowDetails" value="false">
  </cfinvoke>
  <cfdump var="#variables.geocode_query#" format="text">

  <cfquery name="insertValues" datasource="#request.datasource#">
  insert into tableX (
      listID
     ,longitute
     ,latitude)
   values (
      #listings.listID#
    , #geocode_query.longitude#  
    , #geocode_query.latitude#
    ) 
  </cfquery>

  <cfset sleep(200)> <!--- are you tired? ---->
</cfoutput>

Open in new window

0
 

Author Comment

by:Bang-O-Matic
ID: 36894362
<cfset sleep(200)> <!--- are you tired? ----> LOL!
If you don't rest a little between queries, google just errors out.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 36894369
Good to know...

I could use a couple hundred units of sleep; minutes though, not milliseconds ;)
0
 

Author Closing Comment

by:Bang-O-Matic
ID: 36894418
Perfect! Thanks so much for your help!
0
 

Author Comment

by:Bang-O-Matic
ID: 36894647
hey one quick question pertaining to this, sort of...Of course if i run this code twice it errors out because its trying to insert duplicate primary key in the table. I handled it like this:
-------------------------
 <cfquery name="insertValues" datasource="mydatasource">
  insert into map (
      listID
     ,longitude
     ,latitude)
   values (
      #listings.listID#
    , #geocode_query.longitude#  
    , #geocode_query.latitude#
    )
    ON DUPLICATE KEY UPDATE longitude=#geocode_query.longitude#, latitude=#geocode_query.latitude#;
  </cfquery>
--------------------------------------

And that seems to work. Is " ON DUPLICATE KEY UPDATE" best way to handle that issue??
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question