I may have missed a trick here but I can't seem to find a solution.

I have a table of ~1.6 million postcodes, also containing co-ordinates, and a separate file detailing ~6000 a population with postcodes attached. I am trying to attach the co-ordinates from the postcode file to the population file. There is, but not always, two or more individuals associated to one postcode. The theory, in my head, is simple. Join the postcode table to the population table on the postcode attribute. To start with this idea in ArcGIS 10.1 seemed simple, using a basic join, but what seems to be happening is that a 1:1 relationship is being enforced. After researching further I found the Make Query Table tool which claimed to solve this M:1 problem. Though that doesn't seem to be working. I am currently in a two-step process to see if I can solve this. First is looking through some, don't particularly want to look through all files in either table, to see if there is a formatting error that would explain this error. The second is looking into using MySQL to see if that can be used to join the tables. Or at least create a query of the two tables that will produce the desired output table.

First off if you have any advice on making this work in ArcGIS that'd be brilliant, I'm sure it can do it, just not sure how to make it work. Alternatively if you know of another method that would easily join these tables, be that QGIS, MySQL or something else, then please do say.

Hope you can help, and if there's anything I've missed out that would help you understand my problem, let me know.

  • What format is your data in? (Shapefile, Geodatabase?) – Mapperz Feb 13 '13 at 14:47
  • Geodatabase. The Make Query Table tool requires it to be so. – dlovett Feb 13 '13 at 14:50

This is possible in ArcGIS by creating a spatial view or query layer of your table with the columns for post codes and the shape ( coordinates) and joining that to your population table using SQL join and matching the post codes column to your spatial view. This will essentially give you post codes and coordinates for every population matched. You can also do this in a regular SQL environment like MS SQL or any of the other spatial RDBMS. Esri has help docs about spatial views if you do a little searching from resources.arcgis.com and pick ArcGIS desktop help. The syntax should be something like this: Select a.postcodes, a.shape, b.population (and whatever other columns) From postcodes table a join on population table b Where a.postcodes = b.postcodes


There is some useful online documentation that recommends using a relate or relationship class for one-to-many (and many-to-many) because ...

In all cases of 1:M joins, only the first matching record is joined and displayed in the layer's attribute table.

  • The relationship class works. But I need attributes from the larger file appended to the smaller. Is this possible? – dlovett Feb 19 '13 at 11:01
  • @dlovett I'm not sure how you would plan to "shoe horn" multiple matches into a single record, but one way is to comma delimit them. – PolyGeo Sep 24 '14 at 4:56

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.