2

I am struggling to find the correct tool to do what I want and I feel I am overlooking something simple... I am working with census blocks and internet providers in those census blocks. I have a spreadsheet showing the census block ID number and the provider in that census block. However, in most cases there are multiple providers for each census block ID. So, I get a table with duplicate Census Block IDs in their own row but unique providers. Ideally, I would like to remove the duplicate rows, but maintain the unique provider list only in a new column instead of a row: enter image description here

Goes to this: enter image description here

I would need this to be at least semi-automated since I will be dealing with 500k+ lines of data per example. If I do a many-to-one join in ArcGIS it only takes the first row. I can create multiple features with identical geometries using the 'Make Query Table' function, but this map will ultimately be used by a layperson and they want to click on a census block and have a list of providers and not cycle through all the layers.

It makes no difference to me if this step is done in Excel, ArcGIS (with ET Geowizards Plugin), QGIS, or another program that may do it best??

closed as too broad by PolyGeo Aug 24 '17 at 20:48

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Please always decide which particular software you wish to ask about unless your question is about an algorithm or GIS principle when there should be no need to mention any. – PolyGeo Aug 24 '17 at 20:50
0

Strictly speaking, what you are asking is not possible within the classic relational data model, because you are asking to have a row or tuple to have a variable number of fields (depending on how many ISPs you have in a census block). A relational table is a set of tuples, and all tuples/rows should have the same number of fields.

That being said, in databases like PostgreSQL/PostGIS, it is possible to store an array of values in a single cell using, e.g., array_agg or another aggregate function as follows:

SELECT block_id,array_agg(isp) isps FROM the_table GROUP BY block_id;

You will get something like:

block_id  | isps
----------+------------------
1234      | {Verizon, Central Link, ...}

and then CREATE TABLE to store the query result.

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