topic Re: Geocoding to Database in Data Management Questions

Geocoding to Database

BrianLeroux — Mon, 04 Apr 2011 12:38:03 GMT

So i have roughly 500K policies that I geocode monthly from an access database. The access database is populated from a Microstrategy report as I do not have direct access to the policy server. Most of the policies have previously been geocoded. The geocoded results are currently saved in a file geodatabase. I would like to create a workflow where I would only have to geocode new policies and not all policies every month.

Any ideas? Can ArcCatalog geocode back to an access database?

PS. I only have an ArcView license.
Thanks in advance.

Re: Geocoding to Database

JoeBorgione — Mon, 04 Apr 2011 18:21:59 GMT

So i have roughly 500K policies that I geocode monthly from an access database. The access database is populated from a Microstrategy report as I do not have direct access to the policy server. Most of the policies have previously been geocoded. The geocoded results are currently saved in a file geodatabase. I would like to create a workflow where I would only have to geocode new policies and not all policies every month.

Any ideas? Can ArcCatalog geocode back to an access database?

PS. I only have an ArcView license.
Thanks in advance.

Is it reasonable to create a realitionship between the monthly input records and those that have been geocoded already? Perhaps through a unique identifier such as the policy number? That way you could examine the incoming records against those already geocoded, and concetrate your efforts where they need to be. Another approach may include modifying the Access schema by adding a field that indicates if a record has been geocoded or not.

Can ArcCatalog geocode back to an access database? The short answer is yes, you need to set up an odbc connection from ArcCatalog and your Access database.

Hope this helps-

Re: Geocoding to Database

BrianLeroux — Mon, 04 Apr 2011 18:35:02 GMT

Joe- That does help a little bit. I already have an odbc connection to the access database. I actually use it in a model to geocode the addresses and output to a file geodatabase. I can't figure out how to geocode back to teh access database. Is there any documentation on that? Does it matter that I am using an access 2007 accdb?

Re: Geocoding to Database

JoeBorgione — Mon, 04 Apr 2011 18:59:41 GMT

Joe- That does help a little bit. I already have an odbc connection to the access database. I actually use it in a model to geocode the addresses and output to a file geodatabase. I can't figure out how to geocode back to teh access database. Is there any documentation on that? Does it matter that I am using an access 2007 accdb?

Sorry about that, I guess I read your post incorrectly. I don't think you can directly write the results of geocoding to an Access database unless that DB was a personal geodatabase, and even then it would still be in form of a point feature class. You would obviously have the Geocoding_Results attribute table there, for what it's worth.

That is an interesting proposition though; somehow translate just those attributes in the gecoded results into a stand alone access db table...

Re: Geocoding to Database

BrianLeroux — Mon, 04 Apr 2011 19:04:31 GMT

Thanks Joe. I probably could have worder that better. I will keep digging to see what i can come up with.

Re: Geocoding to Database

JoeBorgione — Mon, 04 Apr 2011 19:08:04 GMT

There is a tool called Export Feature Attribute to ASCII that I just tried for the first time a moment ago. You might look at that, and then see if you can figure out a way to automate importing that into access. Certainly you could import it manually...

Good Luck!

Re: Geocoding to Database

BrianLeroux — Thu, 07 Apr 2011 11:48:43 GMT

There is a tool called Export Feature Attribute to ASCII that I just tried for the first time a moment ago. You might look at that, and then see if you can figure out a way to automate importing that into access. Certainly you could import it manually...

Good Luck!

Joe-
Thanks mentioning that tool. I was able to develop a workflow as follows. Import policies to my DB and have a query run that selects only the new policies. I have a model that uses that query to geocode and return an ASCII file to a specific location. I then import the ASCII file into DB and a query appends the new records to my previously geocoded policies.

There are a few manual steps but better than geocoding hundreds of thousands of records monthly. I may be able to even update this weekly..
Thanks again!!

Re: Geocoding to Database

JoeBorgione — Thu, 07 Apr 2011 12:53:26 GMT

Good for you! Happy to help out.