Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
    
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

2 Answers 2

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.

share|improve this answer
    
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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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