topic Re: Spatial join with HUGE datasets in Geoprocessing Questions

Spatial join with HUGE datasets

ManuelFrias — Thu, 16 Jul 2015 11:34:09 GMT

How to make a spatial join with ArcGIS with these HUGE datasets?

Target: almost 3 million squares 500x500m size (the attribute table is basically empty)

Join feature: 12000 polylines

Operation: one to many

Match: intersect

All my attempts have failed:

From the Spatial Join tool in the Toolbox: after 15 min the output is a file where no join has been made (Join_Count field is 0 in all rows)

From the dialog box: It seems to work but my estimate is that it takes 4 days to complete - without any guarantee the output will be correct.

It works when I do a Spatial Join of a small area.

Any ideas on how to do this in a proper way?

I am using file geodatabase, ArcGIS 10.2, background processing, 23 GB RAM

(The idea is to overlap those polylines to the grid (the 500x500m squares). I will then make a dissolve to find out how many lines are in each square. Finally, I will make a density map.)

Re: Spatial join with HUGE datasets

XanderBakker — Thu, 16 Jul 2015 12:02:24 GMT

Would Line Density—Help | ArcGIS for Desktop be a feasible alternative if you use a cell size of 500m?

Re: Spatial join with HUGE datasets

ManuelFrias — Thu, 16 Jul 2015 12:12:46 GMT

That would be an option, thanks!

The problem is that I HAVE to use the 500x500m grid file.

Re: Spatial join with HUGE datasets

GabrielUpchurch1 — Thu, 16 Jul 2015 15:06:01 GMT

Hi Manuel,

To verify, do you have the 64-bit background geoprocessing patch installed? If you don't, you are still running in 32-bit and will have limited access to RAM (4 GB max I think).

Theoretically, you should be able to use all 23 GB RAM when processing in 64-bit (at least what isn't being used by other processes). Of course, theory and reality are two different things and even with access to huge amounts of RAM, it doesn't necessarily mean that a process will succeed or be any faster. Other factors related to the processor, software, etc. will contribute to performance and the ability of a process to be completed successfully. I am being intentionally vague here because even after assisting many users with geoprocessing performance issues and questions, I still don't fully understand everything that occurs behind the scenes when a process is executed.

If you can run the process on a sample of the data but not on the entire dataset, I usually suggest subsetting the dataset, running the process on each subset, and then merging the results back together. In your case, you could subset the grid into manageable pieces and probably leave the lines alone. Many users will use ModelBuilder or Python to semi-automate the procedure. It isn't ideal but as long is it won't somehow influence the final results, you know it will get the job done and is a lot safer than waiting 4 days. I once experienced a power outage on the third day of a geoprocessing operation and didn't have any type of battery backup to keep my machine alive.

Re: Spatial join with HUGE datasets

VinceAngelo — Thu, 16 Jul 2015 15:17:27 GMT

This question was also asked on GIS Stackexchange.

Please specify the license level of ArcGIS Desktop available (Advanced/Standard/Basic), since it significantly changes the difficulty of generating a result. Please also mention whether you have Spatial Analyst available, since Kernel Density takes just seconds.

- V

Re: Spatial join with HUGE datasets

ManuelFrias — Fri, 17 Jul 2015 07:40:55 GMT

Thanks Xander Bakker, Gabriel Upchurch and Vince Angelo for your advice! I am experimenting with your ideas.

I have ArcGIS Desktop Advanced with Spatial Analyst (and all other extensions) available.

I am not sure the 64-bit background geoprocessing patch is installed. I run a tool called TrackBuilder that uses a 64bit Python script. But when a check the python version in my ArcGIS it says 32bit.

To clarify, I must use the 500x500m polygon grid so I I don't understand how I can use the Kernel tool so that the result fits in that grid.

Re: Spatial join with HUGE datasets

ManuelFrias — Fri, 17 Jul 2015 07:47:42 GMT

By the way I run yesterday the Intersect tool with the whole dataset (remember, 3 million polygons). It worked with a small dataset but now it's running for 19 hours!

Is there any way to know it's actually doing something? The CPU shows 0% and very little RAM is being used. According to the Geoprocessing result window ArcGIS is "Assembling features"...

EDIT: it ended after 19 hours. It was doing something after all!

Re: Spatial join with HUGE datasets

DanPatterson_Retired — Fri, 17 Jul 2015 08:22:11 GMT

To summarize your inputs

you have 3,000,00 cells of 500mx500m size
it the number of lines in each cell that you are interest in
you will product a density map from the results of the counts per cell for the study area

Which gives rise to these questions:

is the study area in projected coordinates?
why is it the count per cell and not the length of the linear features per cell that is of interest?
if the data are not in projected coordinates, how do you interpret density in real world units? whether it be counts or length?
what is it that you need density for? sometimes the object under investigation will give rise to important questions of the utility of the measure or alternate approaches to guage/investigate its pattern

Assuming your inputs and my questions, consider the scenario that one is interested in drainage density as it relates to surficial hydrology. Now from a purely practical perspective, we can consider the total length of the rivers/streams within a specific area (ie your cell) giving us a length/are dimension. Ideally, we should have the surface area of the streams per area, giving us a dimensionless unit which makes sense... Counts per area in this context makes no sense since we could have 1 stream intersecting a 500x500 m^2 cell, but the coverage in terms of length or area could be anything (ie any length/area or the cell could be completely covered by the stream)

So in short, Xander's solution scales this down on a cell by cell basis, but other suggestions depend on what is being examined and whether count/area is the key or other measures as length/area or area/area are of interest.

Re: Spatial join with HUGE datasets

ManuelFrias — Fri, 17 Jul 2015 09:01:06 GMT

Very relevant questions you pose, Dan Patterson,

We are doing shipping density maps. The final result will be a map showing the number of lines per cell,i.e., the number of ships that have crossed each cell.

We started out with AIS (Automated Identification System) point data that was converted to lines with a tool called TrackBuilder. We could have used ArcGIS but this tool gives the route for each ship with start and end time (sort of, it has its problems though).

We are using a grid which is the EEA standard (European Environment Agency). The idea is to overlap this grid with other analysis we'll make in the future. Traditionally, we have used raster functions like kernel or point density. The problem with this approach is that it doesn't give the number of LINES per cell and the EEA grid can not be used.

The main challenge we are facing, as I see it, is SIZE. This wouldn't be a problem if the size of the cells were bigger or the study area was smaller. It's, by the way, the Baltic Sea. For non-Europeans this is a comparison with USA:

Re: Spatial join with HUGE datasets

DanPatterson_Retired — Fri, 17 Jul 2015 11:40:03 GMT

Manual...interesting...which raises the question about another question... You mentioned that it worked fine on a small data set. When you did this

did you select both the lines AND the cells to form a selection prior to doing the intersection, or
did you just select the lines OR the cells to form the selection
what proportion of one or the other was made during that run... 1/10 a 1/4 ?

Those conditions mean that:

you could tile by repeating spatial selections prior to doing the intersect, with option (1) being the best choice.
By limiting the possible candidates in the intersect process, you have a real time estimate of how long it will take to finish the remainder.
The results of each selection and intersection can then be merged prior to doing your final density calculations.

My feeling is that by trying to do everything all at once scales the problem up particularly if method (2) was used. If you have some thoughts on this that would be interesting. This is beginning to sound like parallel processing (not parallel processors, but multiple instances of ArcMap) would be nice.

Re: Spatial join with HUGE datasets

GabrielUpchurch1 — Fri, 17 Jul 2015 12:17:45 GMT

To check to see if the 64-bit patch is installed, go to the Help dropdown menu in ArcMap (or ArcCatalog) and select "About ArcMap." If it is installed, you should see something listed similar to "Background Geoprocessing (64-bit)...".

Re: Spatial join with HUGE datasets

VinceAngelo — Fri, 17 Jul 2015 12:42:42 GMT

If you have Spatial Analyst available, then you're wasting your time doing this as a vector modelling exercise. In fact, raster is a much better way to model the quality of AIS (though 500m increments are far too small for reality -- 5km would be better).

It would take much less than 19 hours to learn how to generate a raster with the same origin as your polygon set (I had never done so before from the UI, and it took seconds), and minutes to run dozens of model variants until you found a Kernel Density parameter set that produced a useful raster. From there, all you need to do is reclassify the zeros to NODATA, scale the results to integer, and convert to polygon with Raster to Polygon.

- Vince

Re: Spatial join with HUGE datasets

ManuelFrias — Tue, 21 Jul 2015 13:57:44 GMT

Hi.

I think I'm going to follow Dan Patterson's idea. Vince Angelo' strategy sounds good but I still need to work with 500x500m cell size. I don't know anyway how I'd fit the raster to the polygon grid.

I created another grid with 9 big cells which cover the whole area. The idea is to do the spatial join in each big cell and then merge everything.

This model works like a charm for one big cell:

It works fine for one rectangle (only 24 min!) but how to iterate to do the same in the rest of the cells? I exported it from Model builder (attached) and tested a for loop but to no avail.

Any ideas?

Re: Spatial join with HUGE datasets

GabrielUpchurch1 — Tue, 21 Jul 2015 14:07:59 GMT

Since you are already using ModelBuilder, I would just insert a "Feature Selection" iterator prior to the Make Feature Layer step and use the 9 cell grid feature class as the input. The output from the iterator is then used as input to the Make Feature Layer tool.

Some documentation on iterators: A quick tour of using iterators—Help | ArcGIS for Desktop

You will also need to use "inline variable substitution" to properly name the outputs from each iteration. Here is some documentation: A quick tour of using inline variable substitution—Help | ArcGIS for Desktop

Re: Spatial join with HUGE datasets

VinceAngelo — Tue, 21 Jul 2015 15:16:49 GMT

The Environments... settings of the Kernel Density tool (and Line Density, and all other tools) provide a way to specify the origin of the Processing Extent:

You may need to enter the top/right values before the left/bottom, since the UI enforces a valid envelope (right > left, top > bottom) at all times.

I can't recommend using vector to take days to perform an analysis that raster provides in seconds.

- V

Re: Spatial join with HUGE datasets

ManuelFrias — Wed, 22 Jul 2015 07:56:37 GMT

Hi Vince, thanks for your interest,

I guess the main problem is not fitting the raster to a 500x500m grid but to answer the original problem: How many lines are in each cell?

Kernel and line density tools don't solve this. At least the tests I made don't directly answer the above question.

Result:

The Intersecting tool that has been mentioned earlier was a good idea but it took 19 hours to finish the process.

Re: Spatial join with HUGE datasets

VinceAngelo — Wed, 22 Jul 2015 14:28:08 GMT

To limit values to actual line crossings, your search radius should be between sqrt(2) and 1.5 times the pixel radius (354-375) -- a value equivalent to the pixel size increases the density in neighboring cells.

You'll need to divide the density pixel by the average value generated by one pixel to generate a scaled feature density, and "Plus" 0.5 and "Int" to get approximate counts. Since it only takes two seconds to generate a ~4M pixel grid, there's plenty of time to try various search radius values.

Or you can use the EXPECTED_COUNTS option at 10.3, which will pre-scale the results [just need int(val+0.5)]

Re: Spatial join with HUGE datasets

ManuelFrias — Thu, 23 Jul 2015 11:22:09 GMT

Hi Vince Angelo

I tried in AG 10.3 but it crashes.

Back in AG 10.2 I tried different search radius but I fail to get the exact amount of lines crossing each cell. Usually the resulting cell after the kernel process gives more lines than there actually are. It's close but not exact.

I got confused with your second paragraph. I guess you're talking about the raster calculator but I don't really get how you can do it.

I am in the desperate phase when nothing seems to work. Vince's suggestion seems the more rational if i get it to work. Meanwhile, my attempts to do it with a polygon grid have failed so far. The suggestion by Gabriel Upchurch was great.

The problem is that Spatial Join is unable to join the polygons and the lines of the first part, That is about 600 000 polygons and only 4 500 lines. Out of memory!

This happens if I use Intersect for Select Layer by Location (2): the lines and the GridDivision.

If I use within the process goes until grid cell 7. The problem is that with withing not all lines get selected.

I read that even if I am on an 64-bit OS I can only operate with 32-bit. Is the model builder using 64 or 32? ( I do have Background Geoprocessing (64-bit))

Re: Spatial join with HUGE datasets

GabrielUpchurch1 — Thu, 23 Jul 2015 12:10:55 GMT

To answer your last question first, the 64-bit Background Geoprocessing applies only to geoprocessing tools being executed in the background. It can be used whether the tools are executed directly or in ModelBuilder or python. ArcGIS for Desktop is a 32-bit application but when you install the patch and execute the tools in the background, they run in 64-bit.

When you run the model, is the progress dialog opening? If so, then the model is still running in the foreground. How are you executing the model? If you are executing it from the ModelBuilder dialog, I think it always runs in the foreground so you need to execute it as you would any geoprocessing tool. If the model is running in the foreground, then do the following:

1. Right-click on the model and open the "Properties" dialog.

2. Under the "General" tab, uncheck the setting to "Always run in the foreground" and apply the change.

3. Now execute the model by double-clicking on it (not by opening the ModelBuilder window).

If the model is already running in the background and you are still getting "out of memory" errors, then you have two main choices:

1. Process the data in smaller subsets (this should be straightforward since you already have the model); or

2. Try to get Vince's suggested approach to work.