How to geocode addresses dynamically?

[JunMeng]

Hi,

I am still new to ArcGIS, so please give me any advice you can think of.

I have two databases: one for normal business to add/update/delete user addresses; one for GIS to display those user addresses using ArcGIS Server.

One important step is to geocode user addresses of course. But as those addresses are dynamic (being added/updated/deleted), how can I reflect the changes on GIS side?

There are two options:

1. Batch geocode addresses at night and append to GIS database. This is good for new addresses. How to deal with updated/deleted addresses?
2. Geocode addresses dynamically when an address is added/updated. But how to do that? How to remove record in GIS database when a user address is deleted in the business database?

Any advice is appreciated!

[BruceHarold]

Hello Jun

If you implement a process which maintains the copy of the address table in your GIS database (say by nightly update), and you have a locator in the same workspace as the table, then you can re-geocode automatically on update by using the option shown in the attached dialog (also available in the Geocode Addresses geoprocessing tool) to create a dynamic feature class related to the table.

Regards

[JunMeng]

Thanks for the reply!

Two questions:

1. How to deal with deleted address in the address table? Will it be automatically deleted in feature class as well?

2. If I publish the geoprocessing tool to ArcGIS Server, will it be called automatically when the address table is changed? Will ArcGIS Server add a SQL database trigger so that when the address table is updated, the geoprocessing tool will be called?

[CarlAlexander]

Bruce, what process should I implement to maintain a copy of address in our geodatabase.  Right know we run a stored procedure in SQL to update an address table.  The dynamic geocode does not work with this process.  Thanks.

[BrianLeroux]

This thread is a bit old but I am wondering if anyone was able to geocode dynamically when updating a table in in SQL. It seems dynamic geocoding only works when updating through arcgis. Updating the same table using ArcPY allows the dynamic geocoding to work. Is there a way to have SQL run a python script?

Thanks..

[BruceHarold]

Hi

Dynamic geocoding relies on geodatabase functionality so will only work when table edits are performed using ArcGIS.

It should be possible to call the operating system from within your DBMS SQL code, for example in SQL Server there is a stored procedure call:  EXEC master.dbo.xp_cmdshell - which could run a Python script that does the geocoding using ArcPy.  However, I must add that I'm no DBA and this might be an idea from the chamber of horrors for DBAs.

Regards

[BrianLeroux]

Thanks for the reply Bruce. I am going to discuss that idea with my DBAs.

In the meantime I was able to create a GP service that copies data from one table A to Table B. Table B has a geocoding relationship with a feature class and since the server is performing the geodatabase function, the geocoding works. Additional testing is needed but my initial experience is positive.

[JoeBorgione]

....I'm no DBA and this might be an idea from the chamber of horrors for DBAs.

Regards

I like that... A lot!   😉