1

I am trying to make a matrix with all the distances from 4000 objects to 1000 other objects. I used the Generate Near Table and deselected "find closest option only". The output is table with 4 million rows. However I need it in excel in a 4000x1000 matrix.

Does anyone have an option to do this quick? Without creating 4 different excel tabs (maximum number of rows is 1 million) and using something like a INDEX MATCH function (this takes 24 hours to calculate per tab).

If you only need the closest feature, you can simply join the table. However, that doesn't work in this case.

Can anyone help me?

I have ArcGIS 10.1 and 10.2 and the object sets are both point polygons. I have spatial analyst.

Jip Claassens
  • 21
  • 3
  • 1
    Please edit your question to specify the version of ArcGIS in use, and the format in which the data resides. If it's a SQL-Server geodatabases, then this would apply: http://stackoverflow.com/questions/15260067/pivot-or-transpose-a-table-in-sql-server-without-grouping-by – Vince Nov 04 '15 at 18:25
  • Since this question shows how the license level now known as Advanced can be used to pivot a table, you should also specify which license level you have available. – Vince Nov 04 '15 at 22:47
  • Please clarify what you mean by "... both point polygons". – Kirk Kuykendall Nov 05 '15 at 02:35
  • Have you considered writing a python script to read through the 4million row table, and write 4000 lines to a csv file, where each line has 1000 values? (I'm assuming Excel can open a csv file that's that large). – Kirk Kuykendall Nov 05 '15 at 02:40
  • @Kirk, the specs for newer Excel Worksheets allow for 16,384 columns. Older xls was limited to 256 I believe. – Paul Nov 05 '15 at 04:07

2 Answers2

1

Multiple solutions possible, the one below tested with shapefiles as inputs for near table and to be run in ArcCatalog, processing extent = Default.

import arcpy
from arcpy import env
env.overwriteoutput=True
import numpy as np
nearTable=r'C:\FELIX_DATA\near.dbf'
outASCII=r'C:\FELIX_DATA\GRID.asc'

table=arcpy.da.TableToNumPyArray(nearTable,("IN_FID","NEAR_FID","NEAR_DIST"))
inFids=[row[0] for row in table]
nRows=max(inFids)+1; del inFids
nearFids=[row[1] for row in table]
nCols=max(nearFids)+1; del nearFids
arr2fill=np.zeros((nRows,nCols))
for nr,nc,v in table:
    arr2fill[nr][nc]=v
myRaster = arcpy.NumPyArrayToRaster(arr2fill)
arcpy.RasterToASCII_conversion(myRaster, outASCII)
arcpy.AddMessage('Created %s' %outASCII)

output is ASCII file. Open it in Excel, remove 1st 6 lines and convert text to table using space as separator

FelixIP
  • 22,922
  • 3
  • 29
  • 61
0

Thank you Vince, the pivot table tool does exactly what I want!

I'm sure the other alternatives mentioned might work as well, but since my scripting skills are nearly zero, this was easier.

There was however one small issue with the Pivot tool, that it didn't go beyond 999 columns. In this case I could easily remove 86 columns so that fixed the problem. But I still don't know what the issue is.

And is point polygons not the correct term? Polypoints then?

Thank you all

Jip Claassens
  • 21
  • 3