Author Topic: mapping in africa (Read 7542 times)

tobiaseigen · February 04, 2008, 11:49:25 pm

hey folks,

I've got 1200+ organizations in civicrm I'd like to plot on a map of Africa. I've turned on mapping and have the google maps key installed - and have been able to view a United States address on a map. I'd be grateful for any guidance on steps required to get everyone else on the map - short of manualyl putting in their longitude and latitude coordinates!

Thanks !

Tobias

Donald Lobo · February 05, 2008, 12:14:15 am

most likely you added the google mapping information after loading the contacts in, or in your custom import script.

There is a script called bin/UpdateAddress.php. You should be able to run that script and populate lat/long for all addresses in the db that do not have a lat/long. You'll potentially have to tweak the script, since it was written for a 1.x version, but i suspect not too many changes are needed

can you please report back on what happens etc . Ping me on IM if you get stuck

lobo

tobiaseigen · February 05, 2008, 02:42:12 pm

Hi Lobo,

As we discussed on IM - this didn't work out of the box. No doubt I'll have to tweak the script to only take country and city for mapping purposes to get everybody in there that doesn't have state and zipcodes set.

If anyone's done this already or willing to help me figure this out, drop me a line here.

Cheers,

Tobias

Chris Burgess · March 06, 2008, 03:48:34 am

Hey

Try changing the SQL to this, or just using my attached version instead of the one which comes with CiviCRM. We don't have states either in NZ, but this SQL works fine for our addresses, and should behave identically for countries who have States as well. If State is a LEFT JOIN instead, it can be used where relevant without disadvantaging the rest of the world.

Here's what I used instead (roughly - try the attached, and please post your success/failure)

Code: [Select]

SELECT c.id, a.id as address_id, a.street_address, a.city, a.postal_code, s.name as state, o.name as country 
FROM civicrm_contact c 
JOIN civicrm_address  a ON c.id = a.contact_id 
JOIN civicrm_country o ON a.country_id = o.id 
LEFT JOIN civicrm_state_province s ON s.id = a.state_province_id 
WHERE c.domain_id = 1 
AND c.id = a.contact_id 
AND a.geo_code_1 is null 
ORDER BY a.id;

I've attached a modified version of UpdateAddress.php to this post. Hope it behaves for ya!

Code: [Select]

cd /path/to/civicrm/bin
php UpdateAddress.php -n YOURLOGIN -p YOURPASSWORD

Impressively fast too - we chugged through 2400 mapping lookups while I wrote this post

xavier · March 06, 2008, 05:52:46 am

Great, I'll try it in Europe as well and report.

BTW, has the "standard" UpdateAdress isn't on the svn version of civi 2 or do I have a config problem/temporary blindness ?

X+

tobiaseigen · March 06, 2008, 08:54:18 am

Thanks for sharing this code! I really appreciate it.

I tried it, both using your script and by inserting the queries into my original script.. and am getting the following error. Not sure what I am doing wrong here. Lobo - does this have soemthing to do with the customizations you've done?

BTW, I'm on civi 1.9.

-Tobias

Code: [Select]

  [callback] =&gt; Array
        (
            [0] =&gt; CRM_Core_Error
            [1] =&gt; handle
        )

    [code] =&gt; -19
    [message] =&gt; DB Error: no such field
    [mode] =&gt; 16
    [debug_info] =&gt; 
SELECT c.id, a.id as address_id, a.street_address, a.city, a.postal_code, s.name as state, o.name as country 
FROM civicrm_contact c 
JOIN civicrm_address  a ON c.id = a.contact_id 
JOIN civicrm_country o ON a.country_id = o.id 
LEFT JOIN civicrm_state_province s ON s.id = a.state_province_id 
WHERE c.domain_id = 1
AND c.id = a.contact_id 
AND a.geo_code_1 is null 

ORDER BY a.id;
 [nativecode=1054 ** Unknown column 'a.contact_id' in 'where clause']
    [type] =&gt; DB_Error
    [user_info] =&gt; 
SELECT c.id, a.id as address_id, a.street_address, a.city, a.postal_code, s.name as state, o.name as country 
FROM civicrm_contact c 
JOIN civicrm_address  a ON c.id = a.contact_id 
JOIN civicrm_country o ON a.country_id = o.id 
LEFT JOIN civicrm_state_province s ON s.id = a.state_province_id 
WHERE c.domain_id = 1
AND c.id = a.contact_id 
AND a.geo_code_1 is null 

ORDER BY a.id;
 [nativecode=1054 ** Unknown column 'a.contact_id' in 'where clause']
    [to_string] =&gt; [db_error: message=&quot;DB Error: no such field&quot; code=-19 mode=callback callback=CRM_Core_Error::handle prefix=&quot;&quot; info=&quot;
SELECT c.id, a.id as address_id, a.street_address, a.city, a.postal_code, s.name as state, o.name as country 
FROM civicrm_contact c 
JOIN civicrm_address  a ON c.id = a.contact_id 
JOIN civicrm_country o ON a.country_id = o.id 
LEFT JOIN civicrm_state_province s ON s.id = a.state_province_id 
WHERE c.domain_id = 1
AND c.id = a.contact_id 
AND a.geo_code_1 is null 

ORDER BY a.id;
 [nativecode=1054 ** Unknown column 'a.contact_id' in 'where clause']&quot;]

[/code]

Dave Greenberg · March 06, 2008, 08:55:58 am

Quote from: xavier on March 06, 2008, 05:52:46 am

BTW, has the "standard" UpdateAdress isn't on the svn version of civi 2 or do I have a config problem/temporary blindness ?

http://svn.civicrm.org/civicrm/branches/v2.0/bin/UpdateAddress.php

Dave Greenberg · March 06, 2008, 09:05:25 am

Quote from: tobiaseigen on March 06, 2008, 08:54:18 am

I tried it, both using your script and by inserting the queries into my original script.. and am getting the following error. Not sure what I am doing wrong here. Lobo - does this have soemthing to do with the customizations you've done?

BTW, I'm on civi 1.9.

Looks like that script is written for the 2.0 schema. You'll need to modify it a bit if you want to use it for 1.9. The main difference being that the civicrm_address records are joined to contact records through a civicrm_location record in 1.9 (rather than directly in 2.0).

Chris Burgess · March 06, 2008, 12:56:45 pm

yes, it's for 2.0. you could also just comment out the references to "civicrm_state_province.*" and "s.name as state" for 1.9 probably

Donald Lobo · March 06, 2008, 01:17:04 pm

Issue filed and fixed for 2.0: http://issues.civicrm.org/jira/browse/CRM-2811

also made state optional in the geocoding routines

tobiaseigen · March 06, 2008, 02:14:02 pm

Hiya -

I followed xurizaemon's advice, and it seems to have completed correctly. I now get this:

Addresses Evaluated: 2288
Addresses Geocoded : 628

So it seems I am making some progress. ;-)

Is there a way to view only the contacts that have been geocoded?

Cheers,

Tobias

Donald Lobo · March 06, 2008, 02:25:59 pm

Unfortunately not

though this would be another good example of a custom search component. Display all my mappable contacts and then map them

lobo

tobiaseigen · March 06, 2008, 05:06:22 pm

Interesting - every time I run the script it continues to find addresses to geocode.. though the number is going down every time. Now we're at evaluated 1234 and geocoding 8. I'll keep running it and see what happens.

Cheers,

Tobias

Chris Burgess · March 06, 2008, 05:24:00 pm

When I ran it, addresses that failed to geocode were assigned co-ordinates at Santa's house (specifically 0,0 - is that the north pole?). I'm figuring this is shorthand for "failed to look up"? Or is it an actual error?

And yes, my script ended with two addresses to geocode, but I wasn't smart enough to run it a second time and see if they got handled - heh

PS. No ... 0,0 is a LOT closer to Tobias than it is to Santa. It's off the coast of Western Africa.

tobiaseigen · March 07, 2008, 07:08:44 am

<grin> north pole???

I kept running it, and now I'm stuck at 1217 evaluated and 0 geocoded. I presume these are contacts that just can't be mapped for some reason, but I'm not sure.

Here's a few perhaps dumb questions:

- Is it safe for me to empty the geo1 and geo2 tables and rerun the script from scratch?
- Can you think of a way to add geocoded contacts to a group so they can all be viewed on a map?
- Are there ways to geocode addresses to place them at least in a country? E.g. by repeating the script omitting other address fields?

I am asking because I still haave 1217 addresses that are not geocoded at all. I have countries for them all, cities for most, but street addresses and postal codes are not complete.

Cheers,

Tobias

CiviCRM Community Forums (archive)

News:

Author Topic: mapping in africa (Read 7542 times)

tobiaseigen

mapping in africa

Donald Lobo

Re: mapping in africa

tobiaseigen

Re: mapping in africa

Chris Burgess

Re: mapping in africa

xavier

Re: mapping in africa

tobiaseigen

Re: mapping in africa

Dave Greenberg

Re: mapping in africa

Dave Greenberg

Re: mapping in africa

Chris Burgess

Re: mapping in africa

Donald Lobo

Re: mapping in africa

tobiaseigen

Re: mapping in africa

Donald Lobo

Re: mapping in africa

tobiaseigen

Re: mapping in africa

Chris Burgess

Re: mapping in africa

tobiaseigen

Re: mapping in africa