• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 324
  • Last Modified:

Need CFML help with GoogleGeocoder3

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
Bang-O-Matic
Asked:
Bang-O-Matic
  • 5
  • 4
1 Solution
 
Bang-O-MaticAuthor Commented:
BTW -  Windows server / CF9 / MySQL 5.5
0
 
gdemariaCommented:
> 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
 
gdemariaCommented:
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
Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

 
Bang-O-MaticAuthor Commented:
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
 
gdemariaCommented:
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
 
Bang-O-MaticAuthor Commented:
<cfset sleep(200)> <!--- are you tired? ----> LOL!
If you don't rest a little between queries, google just errors out.
0
 
gdemariaCommented:
Good to know...

I could use a couple hundred units of sleep; minutes though, not milliseconds ;)
0
 
Bang-O-MaticAuthor Commented:
Perfect! Thanks so much for your help!
0
 
Bang-O-MaticAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now