Solved

Need CFML help with GoogleGeocoder3

Posted on 2011-09-30
9
315 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
  • 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Coldfusion redirect to new URL when timeout 4 72
paging 3 44
What is the new coldfusion video tag for CF 11 or CF2016 for HTML5 playback 3 44
Fixing Old Legacy Code 1 35
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…
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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