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?

Improve this question
6
  • Any examples of working and non working joins would be of great help
    – Thomas
    May 16, 2017 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, 2017 at 16:24
  • 1
    Maybe an extract of your data?
    – Thomas
    May 16, 2017 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/…
    – GISHuman
    May 16, 2017 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, 2017 at 17:32

2 Answers 2

Reset to default
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.

Improve this answer
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 :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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