?
Solved

Need CFML help with GoogleGeocoder3

Posted on 2011-09-30
9
Medium Priority
?
322 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
Application Discovery Service in AWS

In the era of the cloud, customers migrating away from their existing on-premise infrastructure. This requires lots of planning, strategies, and effort to identify their existing resources and determine how best to migrate.  Datacenter migrations happen in four phases -

 

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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

771 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