# Efficiently calculating length-weighted mean speeds per grid cell in GeoPandas

I wish I had code to share but I simply don't know where to even start. I have a 1km square grid and what I want to do find the total length of a line segment within that cell and multiply it by the lines speed variable to get a value to populate into a column in the grid dataset. Using the example below Grid 1 should be equal (0.54x5)+(0.65x8)= 7.9 and Cell 2 should equal 0.28x2=0.56 and so on for each cell in the grid.

I have considered looping through the grid, clipping the lines into a geodataframe, then doing the math, then append it to the column but I have a huge grid and the loop takes a very long time.

I also considered interpolating points along the line based on the speed; the point would represent where the object was at a given hour of its journey. Then using the spatial join count function I would get a rough metric of object time within each grid. Interpolating points based on a column value for that row is turning out to be tricky so I thought I would ask if there was a means of doing it as I outlined above.

• How many polygons and lines are there?
– BERA
Aug 6 at 16:42

This can work if your datasets arent to big.

Cross join the data frames, calculate their intersection line length and speed.

``````import geopandas as gpd
import numpy as np

polyid = np.arange(1, poly.shape[0]+1)
poly = poly[['polyid','geometry']] #Drop all columns not needed

line = line[['geometry']]

both = poly.merge(line, how='cross', suffixes=['_poly','_line']) #If this doesnt work try the three lines below
# poly['key'] = 0
# line['key'] = 0
# both = poly.merge(line, on='key', how='outer', suffixes=['_poly','_line'])

both['intersects'] = [p.intersects(l) for p, l in zip(both['geometry_poly'], both['geometry_line'])]
both = both.loc[both['intersects']==True]
both['linelength'] = [p.intersection(l).length for p, l in zip(both['geometry_poly'], both['geometry_line'])]
both['avgspeed'] = both['linelength']/both['speed']

both['speedlength'] = both['avgspeed']*both['linelength']
speed = both.groupby('polyid')['speedlength'].sum()

outpoly = poly.merge(speed.rename('speed'), left_on='polyid', right_index=True)
``````
• This returns KeyError: 'crosses'... I tried changing how to predicate and cross to crosses as well as tried using sjoin (which gave the no attribute sjoin error). 2 days ago
• Maybe you've got a different version. I've added another way of cross joining, see edit
– BERA
2 days ago
• Now it is showing a memory error; the trace back is showing a number of errors. MemoryError: Unable to allocate 4.88 GiB for an array with shape (2, 327216221) and data type int64. Not sure why it is not able to allocate 4.88Gb I have way more ram and virtual memory than that. 2 days ago
• Try dropping the columns you dont need, see update.
– BERA
2 days ago