I have a CSV file of around 230 million rows, which contains a latitude column, a longitude column, a Date stamp, and an ID column. It look like this (I added the header in this example to help explain, the file does not have a header):
ID, Date, Lon, lat
46d4089a713082d85452a2af64571644, 2016-11-30T12:57:11.000Z, 53.4529, -2.287
46d4089a713082d85452a2af64571644, 2016-11-30T12:57:26.000Z, 53.4521, -2.2859
46d4089a713082d85452a2af64571644, 2016-11-30T12:57:59.000Z, 53.4522, -2.2878
46d4089a713082d85452a2af64571644, 2016-11-30T12:59:01.000Z, 53.4547, -2.284
a6af7b30dc3ffa0ee7ecea02a2981b7d, 2016-11-30T13:03:01.000Z, 53.4457, -2.2774
693316c8b95e9fb07207400414714180, 2016-11-30T21:18:16.913Z, 53.2887, -2.6687
I also have two ESRI polygon shapefiles I created. What I want is a list of all the IDs that have at least one coordinate in both of the shapefiles.
Since the file is so large and could not be loaded into memory, my approach was to break the CSV file into 117 CSVs of 2 million rows. I then planned on using geopandas to read the smaller CSVs as a GeodataFrame, and use sjoin to find all the waypoints in shapefile 1.
I would then take the ID column of the waypoints within the shapefile as a list, take all waypoints in the large csv with an ID in the list, and do the same again finding the points in shapefile 2.
I tried to use spatial indexing to speed up the process, as it is a large file
I tried to test this using the first CSV of 2 million rows, using this code:
import pandas as pd
import geopandas
from geopandas.tools import sjoin
from shapely.geometry import *
waypoints = pd.read_csv(r'largefile_1.csv',sep=',', names=['TripId','lat','lon'],usecols=[0,2,3])
waypoints['geometry'] = waypoints.apply(lambda x: Point((float(x.lon), float(x.lat))), axis=1)
point = geopandas.GeoDataFrame(waypoints, geometry='geometry')
polygon = geopandas.GeoDataFrame.from_file(r'Shapefile1.shp')
point.crs = polygon.crs
spatial_index = point.sindex
possible_matches_index = list(spatial_index.intersection(polygon.bounds))
possible_matches = point.iloc[possible_matches_index]
precise_matches = possible_matches[possible_matches.intersects(polygon)]
Is there a more efficient way of doing this, in another program or with different plugins?