AnsweredAssumed Answered

Updating SQL Server table with Geocodes

Question asked by eric.anderson@cdcr on Jun 22, 2017
Latest reply on Dec 3, 2017 by notoriusjackdevtrial

In our environment we have a ArcGIS 10.5 in a federated environment. For security reasons, we are using a Portal for our Web delivered maps instead of ArcGIS Online. Some of our maps are based off of SQL Server database tables. One of our tables has a column for Latitude and longitude. The table comes from another source. The other source is only able to geocode about 60 percent of the addresses. Through some hacking, we have been able to figure out that we are able to geocode the remainder of the addresses using the Geocoding tool in ArcMap. The process we are having to go through is as follows:

  1. Using SSIS, select all records with ‘null’ value in Lat_long field and place them in a CSV file.
  2. Using a Python script, execute the ‘Geocode Address (Geocoding) tool’ to geocode the addresses selected based on a Composite location file. This will result in a shape file.
  3. Extract information from the Shape file and place it in a CSV file using the 'Export Data tool' that is executed by a Python script.
  4. Using SSIS and the CSV file from step 3, update SQL table rows without geocodes, so that they now have values.

I'm wondering if anyone has come up with an easier way to do this.

Outcomes