(I posted this over at the forge for google maps, too)
Here's how I geocoded all of my CB users, figure this might help someone. First, make sure you are comfortable working
in phpmyadmin and kinda understand sql queries. There's probably an easier way to do this with scripting but, I'm no
coder.
First, export/extract your comprofiler table (if you can get it to extract only the user_id, address, city, cb_state,
cb_country, and postal code that would be best... my host's export was acting buggy, as per usual). I just exported all
fields INCLUDING THE HEADER ROW WITH COLUMN NAMES into Excel. Once in Excel, I deleted all columns (do this by clicking
the letter column (A,B,C...) above the column so it selects the entire column, then right click and select delete...the
entire column should disappear) except for the ones mentioned above that are needed to geocode and the user_id so I can
reassociate the members with their location.
So, now you should have just the columns of userdata you need. Next step is to select all your data (Ctrl +A) and copy
all (Ctrl +C). Go to the website
www.batchgeocode.com
, there you will see in Step #2 the textarea where you are going to
paste your data (there's already a bunch of pre-filled data in there, I just clicked inside the window and Ctrl +A to
select everything, then pressed delete). You want to paste all of your userdata into the text area. Once that is done,
click validate source to make sure everything is proper. Next all you need to do is make sure that the location fields
in Step 4 are identified correctly (the four fields on the left are all you need to be concerned with). Finally, click "
Run Geocoder" and it will begin processing your users (you'll see a counter as it is processing).
When that's all done, you should have a new text area with all your new userdata on the page. Select all of that data
and paste it back into excel (I would advise to copy & paste it somewhere else, too..it's best to have another copy of
the data so you don't have to geocode everything again). Once in excel, delete all columns except for your user_id and
bg_lat and bg_long. Here's what was the trickiest part for me, you need to now create you sql queries with this data.
The structure of the query is going to look like this:
UPDATE comprofiler SET cb_geolatitude=' 28.137 ', cb_geolongitude='-82.4503' WHERE user_id=62;
To do this I cut and pasted the user_id column after the two lat/long columns, and then added new columns before and
after the data with the proper info. For example:
Column A= UPDATE comprofiler SET cb_geolatitude='
Column B= bg_lat data
Column C= ', cb_geolongitude='
Column
bg_long data
Column E= ' WHERE user_id=
Column F= user_id data
Column G= ;
The easiest way to do this in excel that I found was to enter text in the top field of the column, click the letter
column above the top field to select the entire column then, go up to the top menu and select Edit>Fill>Down, that
should fill the entire column with the first field's data. When you have all your data looking right, you need to get it
out of excel. The problem I had here is that exel adds a lot of hidden markup characters that can screw with your
inserts. What I did was just cut and pasted the data into a blank dreamweaver .html file (you can probably do the same
in note pad, HTML Tidy, or whatever). I still ended up with many many tab spaces but, that won't hurt your insert. JUST
MAKE SURE YOUR INSERT QUERIES ALL LOOK LIKE THIS:
UPDATE comprofiler SET cb_geolatitude=' 28.137 ', cb_geolongitude='-82.4503' WHERE user_id=62;
(Don't sweat all the space caused by hidden tabs, my actual statements looked like this:
UPDATE comprofiler SET cb_geolatitude=' 28.137 ' , cb_geolongitude=' -82.4503 ' WHERE user_id= 62
Just make sure there are no extra characters in the query. Now, go back to phpmyadmin, MAKE SURE YOU HAVE BACKED UP YOUR
TABLES, and click the SQL tab. Copy and Paste all of your sql queries into the query window and execute . That should
do it, if everything was formatted correctly. I know the above seems like a lot but, the whole process shouldn't take
more than 10-20 minutes. And all your users should now be geocoded!
