Some maps need a latitude and longitude rather than an address. The Locations Map plots only points given as coordinates. The Route Map pins a stop as soon as the form opens only when the stop is given as coordinates; a stop given as an address is pinned only after the route is calculated. The Route Map never saves the coordinates Google finds for an address, so if your records hold only addresses, the coordinates have to come from somewhere else.


The US Census Geocoder is a free service from the US Census Bureau that turns a US address into coordinates. It needs no key, and the coordinates it returns are yours to keep on your records. It works for US addresses only.


Adding coordinates as records are added or changed

Call the Census Geocoder from workflow with the Make REST Call action, then join the two numbers with a Record Field Update. Add these actions, in this order, to the workflow or Action Set that runs when a record is added or its address changes. The examples use fields named Street, City, State, Zip, Latitude, Longitude and LatLon; use your own field names.

Make REST Call - On the API Info tab, choose GET and enter this as the URL:

https://geocoding.geo.census.gov/geocoder/locations/address?street=[URLENCODE([Street])]&city=[URLENCODE([City])]&state=[URLENCODE([State])]&zip=[URLENCODE([Zip])]&benchmark=Public_AR_Current&format=json

Then on the Success Field Updates tab, click Add Field Update twice and set the Source Type of both to Property:

  • Field Name Latitude, with the Source Name result.addressMatches(0).coordinates.y
  • Field Name Longitude, with the Source Name result.addressMatches(0).coordinates.x


Record Field Update - Builds the single lat,lon value the maps use. On the Update tab, set the Field Name to LatLon and the Field Value to [Latitude],[Longitude]. On the Rules tab, add a Record Value Check rule with Latitude as the field name and !# (not blank) as the value, so the action runs only when a latitude was found. Without the rule, an address with no match would leave LatLon holding only a comma, which no map can use.


Use LatLon wherever a map needs a location: as the Lat/Lon of a Locations Map point, or as the location of a Route Map stop.


When an address is not found

When the Census Geocoder cannot match an address, it still answers successfully, just with no matches. Latitude and Longitude are then written blank, which also clears any coordinates they held before. The Record Field Update is skipped by its rule, so LatLon keeps whatever it held.


To catch these records, add one more action with a Record Value Check rule on Latitude with the value # (blank). For example, a Record Field Update can set a field such as GeocodeStatus to Not Found, so the records can be found with a search and their addresses corrected.


Adding coordinates to existing records

The actions above handle records as they are added or changed. Records that are already in DocMgt can be filled in two ways.


In DocMgt, for up to a few hundred records. Put the actions from this topic, including the Not Found flag, in an Action Set of their own, for example Geocode Address. Then run that Action Set on every record that still needs coordinates:

  • Make a second Action Set with a single Search and Run Action Set action. On its Search tab, set Search In to your Record Type, and under By Values use Add Search Value to search for Latitude with the value # (blank) and GeocodeStatus with the value # (blank). The second value leaves out addresses already found to be unmatchable, so they are not sent again on every run. On its Action Set tab, set Run Action Set to Geocode Address.
  • To run it, open the Record Type, go to its Automation section and click Add Automation. Give it a Description, set its Action Set to the second Action Set, set a Schedule, and make sure it is enabled. When the records have their coordinates, turn the Automation off or delete it. You can also leave it on to catch any record that is saved without coordinates later.


Each record is a separate call to the Census Geocoder, so this suits hundreds of records better than tens of thousands.


With a file, for large numbers of records. The Census Geocoder also takes a whole file of addresses at once on its website, and the DocMgt Importer can then write the results back onto the matching records:

  • Make a CSV file with one row per record: the DocMgt Record ID, then the street, city, state and ZIP code, in that order. The Census Geocoder calls the first column the Unique ID, and it is what ties each result back to its record. A file can hold up to 10,000 records and be up to 5 MB; split larger sets into several files.
  • On the Census Geocoder's batch address page, choose the Public_AR_Current Benchmark:, choose the file under Select Address File:, and send it. The Census Geocoder returns a results file with a row for each address.
  • Open the results in a spreadsheet and arrange them for import: a column with the Record ID, and columns named for your coordinate fields, such as Latitude, Longitude and LatLon, with LatLon written latitude first, such as 40.8136,-96.7026. Check the order of the two numbers in the Census results before you build LatLon; DocMgt's maps need the latitude first. Leave out the rows the Census Geocoder could not match.
  • Import the file with the DocMgt Importer, set up so that each row updates an existing record instead of creating a new one. On the folder's Match Fields tab, put the Record ID column in Lookup Record ID: as a variable, such as [RecordID], and turn on Skip non-matched records so a row that matches no record does not create one. The matched records' Latitude, Longitude and LatLon are replaced with the values from the file. The DocMgt Importer guide's Indexing topic covers setting up a folder and its Match Fields.


Troubleshooting

  • The call fails with an error such as "A task was canceled". The Census Geocoder did not answer in time. A call is cut off after 30 seconds, or after longer when it runs in the background, such as from an Automation. The coordinates are not updated. The service is sometimes slow; run the actions again for that record later.
  • Latitude and Longitude are swapped. The Census Geocoder gives longitude as x and latitude as y. Check that Latitude has the Source Name ending in .y and Longitude the one ending in .x.
  • Addresses that look right are not found. Check that each field the URL uses holds what its name says. At a minimum, the Census Geocoder needs the street and the ZIP code, or the street, city and state.