Solved

Need CFML help with GoogleGeocoder3

Posted on 2011-09-30
9
318 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
recordcount is coming as undefined in query 4 65
Weird Behavior in ColdFusion 8 80
Coldfusion Error: The toString method was not found 5 75
paging 3 46
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…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
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…

860 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