2

I have a shapefile of all the addresses in a city and a table of survey results for a sample of addresses, with NO spatial aspect. I am asked to make the survey data spatial, so used a simple join based on address in the table to the address shapefile.

After I performed the join, I discovered that 2,558 of the ~30,000 survey results rows did NOT join. Some of these are due to misspellings in the survey address list, but the vast majority of addresses seem to be present and accurate in BOTH fields. When I search for the addresses manually, they are in both of the fields used to join, and are identical and accurate. I trimmed the fields in both ArcGIS and in Excel, so it shouldn't be leading or trailing spaces.

I am at a complete loss at what could cause identical join values to NOT join. Is there something obvious I'm missing here? Any ideas?

  • Any examples of working and non working joins would be of great help – Thomas May 16 '17 at 16:19
  • I'm not sure what to give an example of. It is just a rows of street addresses in one shapefile, and the addresses from a survey in the other. Some will join, and others will not, despite being identical in both cases. The field is just like: "00 STREET ST" – A. Rutland May 16 '17 at 16:24
  • 1
    Maybe an extract of your data? – Thomas May 16 '17 at 16:39
  • Might want to try creating a geocoder based on your shapefile, and geocode the addresses from your table. desktop.arcgis.com/en/arcmap/10.3/guide-books/geocoding/… – GISKid May 16 '17 at 16:43
  • Things I've always looked for: spaces(before/after/in), typos, different field formats (string vs number), different cases, ie.. upper vs lower, or extra characters like hyphens and/or periods. Sometime I take both table in MS Access and use the mismatch queries to compare what's in A and not B, and what's in B and not in A. – Bill Chappell May 16 '17 at 17:32
1

(sorry if this is more of a comment, but I can't comment yet). I have found this numerous times with long fields in particular, and have tried many conversions, searches, even clearing/copying/pasting the exact value into two test files. It occurred in 10.2 and 10.3; haven't tried it in 10.4.1 yet. I have concluded it is a seriously annoying bug. If you have Access, you might try that.

0

I have just experienced the same, and I did a RTRIM(FIELD), my fields contained some spaces in the end, which I did not put there. Give it a try :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.