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:
I'm wondering if anyone has come up with an easier way to do this.
How many records have null Lat_Long values? If it's not too many, I recommend using pyodbc to select the address column(s) from records in your SQL table with Null Lat_Long values. Then for each row returned by your selection, you can write the address fields to an in_memory table with a single row. Geocode that table and output to an in_memory feature class with one point feature. Then, you can read the Lat/Long values from the feature class using an arcpy.da.SearchCursor and update your SQL Server table with that value/values.
The advantage is all the work would happen in-memory and so you wouldn't have to deal with an actual CSV and Shapefile being on your disk.
Hope this helps!
Thank you so much for your response. I'll be dealing with anywhere from 30,000 to 80,000 rows. But, your concept definitely bears looking into.
I really appreciate your input.
- Eric -