1

I have 28 fairly large point data sets (1/6 degree global grid), all of different extents but with the same point location (previously extracted as centroids from raster pixel).

Example of three point layers with points at same location but varying spatial extent

Each point layer has a unique value (grid_code in example below) per point but as the spatial extents for each point file vary, FID and pointid do not match, i.e. there is no common value to join on.

Using ArcGIS Desktop (10.5) I'd like to combine all 28 files to one by location as the Spatial Join tool does (a reverse one-to-many), but create one file that contains the full spatial extent of all files combined and the respective values attributes (preferably with the origin layer names) per point:

attribute table with combined values

I have tried all varieties of merge, join, relate and spatial join but they either do not combine the attributes, do not join by location or there is no possibility to do it as a batch function.

  • What have you tried? Personally, I would create a master polygon table, then use Identity to capture the master ID on all the tables, then join at will. – Vince Feb 7 '18 at 16:18
  • I tried all varieties of merge, join, relate and spatial join but they either do not combine the attributes, do not join by location or there is no possibility to do it as a batch function. As described, I am looking for a way to spatially join a large number of points from 28 datasets, preferably in one go. I'll look into the master polygon table, thanks for the idea – Kristina Feb 7 '18 at 17:28
  • Please edit the question in response to requests for clarification. It's not fair to those who would answer to need to mine the comments for critical information. I regularly do tens of millions identities through millions of polygons in PostgreSQL, and it takes just a minute or so to complete. – Vince Feb 8 '18 at 0:16
  • I added the information as asked though I don't regard methods that didn't work as critical information but nevertheless valid point about assemblying all information in the question. I am glad to hear you can do it quickly in PostgreSQL, however, I don't know how to work with it so that unfortunately doesn't help unless you can explain it to me in a way a total PostgreSQL beginner can understand. In which case I'd be very grateful. – Kristina Feb 8 '18 at 13:53
2

You can do this using some Python code and the pandas module which is included in 10.5. All input Point feature classes need to be in one database (and no other features). They all need to have a field called grid_code. A field will be added to each of the feature classes named gcode+feature class name, for example gcode_Point1. This is then given the value of grid_code. Then all fcs are merged together, exported to pandas, grouped by xy coordinates (which need to be exactly the same for overlapping points for it to work. But this can be adjusted by rounding the coordinates) so overlapping Points are combined and all non-overlapping Points kept. This is then exported to a new feature class.

Backup your data Before you try it! The code can be executed in the Python window after you change Point_database.

import arcpy
import pandas as pd

point_database = r'C:\Test.gdb' #All input feature classes here, nothing else. Change to match the name of your database
output_fc = 'MegaMerge'
arcpy.env.workspace = point_database

all_features = arcpy.ListFeatureClasses()

for feature in all_features:
    fname = 'gcode_{0}'.format(feature)
    arcpy.AddField_management(in_table=feature, field_name=fname, field_type='DOUBLE')
    with arcpy.da.UpdateCursor(feature,['grid_code',fname]) as cursor:
        for row in cursor:
            row[1]=row[0]
            cursor.updateRow(row)

arcpy.Merge_management(inputs=all_features, output='all_points')
fieldlist = [f.name for f in arcpy.ListFields('all_points') if f.name.startswith('gcode')]
df = pd.DataFrame.from_records(data=arcpy.da.SearchCursor('all_points',['SHAPE@XY']+fieldlist), columns=['SHAPE@XY']+fieldlist)
df_grouped = df.groupby('SHAPE@XY').sum()

arcpy.CreateFeatureclass_management(out_path=arcpy.env.workspace, out_name=output_fc, 
                                   geometry_type='POINT', 
                                   spatial_reference=arcpy.Describe(all_features[0]).spatialReference)

for name in list(df_grouped.columns.values):
    arcpy.AddField_management(in_table=output_fc, field_name=name, field_type='DOUBLE')

icur = arcpy.da.InsertCursor(output_fc, ['SHAPE@XY']+fieldlist)
for row in df_grouped.itertuples(index=True, name=None):
    icur.insertRow(row)
del icur

enter image description here

0

Sounds like you need a combination of one or more of these:

Combine data sets that are thematically similar (they map similar variables) into a single data set of the full extents with one of these (merge is probably best, though not sure without actually seeing your data)

UNION

MERGE

APPEND

Then when you have multiple merged datasets (maybe one of land cover, one of temperature, one of soil type, etc), each storing the full extents of a given variable, you combine them all iteratively with spatial join. Since spatial join is done one to one, you will need to do spatial join on A + B to give you new layer C, then spatial join C + D to give you new layer E, then join E + F to giver you G, etc. when done, you should have a single dataset with all attributes retained.

SPATIAL JOIN

  • Thanks for the detailed answer - I am aware I can do a step-by-step spatial join but I'd like to avoid this as the datasets are very big (>2 mio rows each) and there are 28 and this would take ages. I was hoping there might be a tool doing a reverse 1:M spatial join or some other batch method – Kristina Feb 7 '18 at 16:58
0

Did you find the way? whether your further analysis have to be done in vector or you can do it in raster? if the second option works, i suggest you to go back to your raster dataset, it is far easier and quicker if you do it in raster.

First you have to make your raster datasets were surely spatially registered each other. Then you can use COMBINE all your raster datasets to maintain attribute of each raster dataset. After all, you can do your further analysis using this combined dataset and export/convert your end results to point.

Hope this will help....

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.