Is there a way to force geocode to validate and change any wrong geocodes

[questions]

In the process of upgrading and moving servers, geocoding stopped working.  A number of addresses have been updated via import from an NCOA result.  Unfortunately, the old geocode did not get updated nor deleted so now I have a bunch of addresses with bad geocodes.

I've tried runnning the geocode job with parameters specifying one that is wrong but I believe the code skips over any that have a geocode.

I suppose I could delete all the geocodes and then it will populate them.

[JonGold]

I think that's the right approach - using "Batch Update Via Profile" as a search action should help here!

[questions]

Hmm, I think I'm missing something.  I haven't any idea which contacts have bad geocodes.  I can't think of a way to search for bad geocodes.  Batch update is limited to 100 records, I'm sure there are a lot more than that.

I've done a little trial and error and I think there is a bug on import update.  There is a check box for look up mapping info on import.  If you don't check the box (the default) and do an import that updates the address, the geo codes don't get updated (as would be expected) but they also don't get nulled out so the geocode job doesn't think it needs updating.  So you end up with a bunch of bad geocodes.  I don't find any issues in the issue tracker for this.  Should I open one?

It looks like I can get it to redo all of them from an import via sql (select id, null as postal_code from civicrm_contact) and check the box.  I think probably just deleting all the geocodes via sql and running the job would be a better way.  Though it will have to redo them for all the records in the db which if using google might exceed the limits. 

It looks like the geocode job will update if either geo_code_1 or geo_code_2 is null or 0.

[questions]

For completeness here's what I did to fix the bad geocodes

I deleted all the geocodes

  UPDATE  civicrm_address
  SET   geo_code_1 = NULL ,
           geo_code_2 = NULL

And ran the geocoder from drush

/usr/bin/drush -u 1 -r path_to_civi @sites civicrm-api job.geocode auth=0 -y

[jimcrist]

I've got the same problem. When I first imported all the contacts, I didn't check the geocoding box 'cuz of the warning. Now I have 11,000+ contacts and the only way I can get the lat/longs in there is if I edit a person's address. I've tried importing zeros into the geocode fields, but it doesn't help. The previous poster used SQL to null out the lat/long fields. I can't figure out how to do that. Can I do that from within CiviCRM? I went to the Bluehost cPanel and clicked on the mySQL icons, but I didn't find anyway to enter SQL commands. I'm not a cPanel/Linux person. Please help...thanks!

[jimcrist]

C'mon, this shouldn't be a real tough one. This is a glitch in CiviCRM, there must be a work around. Please help...thanks!

[jimcrist]

I figured out how to see the CiviCRM database via SQL and all the lat/longs are already nulled out, so that wasn't the problem. Then I reran the cron job and looked at the job log and it showed an error. The documentation says start and end can be null, but the error report says they have to be integers. I guess it meant I should leave those parameters out? It's running now...yay!

geocoding=1 (0 to skip geocoding)
parse=1 (0 to skip address parsing)
start=null (optional, begin processing with this contact ID)
end=null (optional, process contacts with ID's <= this value)
throttle=0 (1 to add a 5 second sleep between each geocoding request)