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 have two shape files (e.g. postal code areas and cities) which I want to join based on an id that both shape files have in common (the id would be the city name in this example). Joining the two tables is not the problem (via Join and selecting the city name as field on which the join is based).

But what I really want is an enumeration of all the postal codes of a city - if I join them and take cities with postal codes, every city will get one random postal code that has the same city name as the city, but I want all of them as comma-separated list as new attribute in the cities shape file).

Is there a way to do that?


Example table postal codes:

Postal Code | City
          0 | A-Town     
          1 | A-Town     
          2 | A-Town     
          3 | B-Town     
          4 | B-Town     
          5 | C-Town     


Example cities table:

City Name
   A-Town
   B-Town
   C-Town     


Desired outcome table:

City Name | Postal codes
   A-Town | 0, 1, 2
   B-Town | 3, 4
   C-Town | 5
share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

I remembered an entry from ESRI's Geoprocessing blog which describes a tool which concatenates row values and does what you're after.

I haven't used it before as I haven't had a need for it yet. But I thought it looked useful and I think this will be an easy solution for you.

From the ArcGIS Resource Center:

This tool is based on a python script that takes an input table, selects a set of rows based on a field value and concatenates the row values for a specified field. This tool does not create a new output, but updates the input data.

share|improve this answer
    
+1 I have not used it either but it looks like it will meet @geoSuleiman's requirement - nice find! –  PolyGeo Apr 8 '13 at 2:03
    
+1 - It might be useful to add some more description of the tool and how to use it, in the case that either one or both of those links is damaged at some point in the future. More detail would also allow other people searching to come up with this answer. Just a thought, but a great find regardless!! –  Get Spatial Apr 8 '13 at 16:29
    
Thanks a lot - the version for 10.0 worked after some modification of the python code (resources.arcgis.com/gallery/file/geoprocessing/…). –  geoSuleiman Apr 8 '13 at 21:39
add comment

Please don't think that you can or should do this in DML. Doing so is not only painful for you and your systems, but it is excruciating to watch as a third-party.

Your options are limited to either scripting, writing an extension or using a pivot table. While ESRI has a pivot table function, excel's is much easier to use and manage.

Below is an simple example for how you can script the output you require in C#. It can be quite easily converted to python.

Dictionary<String, List<String>> output = new Dictionary<string, List<string>>();
foreach (var row in table.rows) // Not sure what data structure you're working with.
{
  if (!output.ContainsKey(row.city))
  {
    output.Add(city, new List<string>());
  }
  output[city].Add(row.postcode);
}

String rowTemplate = "\"{0}\", \"{1}\";";
StringBuilder sb = new StringBuilder();
sb.AppendLine(String.Format(rowTemplate, "City", "Postcodes");
foreach (var row in output)
{
  sb.AppendLine(String.Format(rowTemplate, row.Key, String.Join(", ", row.Value.ToArray())));
}
String report = sb.ToString();
share|improve this answer
add comment

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.