GPS coordinates coming from vehicle connect to streets coordinates

Question

I have two tables:

"Vehicle" includes GPS coordinates from the moving vehicles and has around 3.000.000 rows
"streetCoordinates" includes GPS coordinates of the streets, where the vehicles were possibly driving. Its length is around 1.800.000 rows

I want to check, whether the vehicles were on the roads or not. I wrote a query but the thing is, it always fails because it is calculating Cartesian Product - it is checking if there is a match for every point from one table to every point in another table.
Is there any other way to come to a conclusion, which GPS coordinates fall on streets and which do not? Is there a way to do this in QGIS or any other software? Is it possible to this graphically? Up until now, I was using BigQuery and Python for data manipulation and QGIS.

Louis Cottereau · Accepted Answer

Import both tables as layers in Qgis (either throught a csv extract
or data base import)
save both layer as ESRI Shapefile (Right Click on layer -> Export -> Save Features as) with a CRS in meters (find one that can contain every point you have), hopefully the shapefiles won't be too huge
load those shapefiles into qgis, go into the properties of both layers and generate a spatial index for both layers (Layer Properties > Source > Geometry and Coordinate Reference System > Create Spatial Index
In the "Processing Toolbox" select "Buffer" tool and set it up like the picture under and click run

go into the properties of the created "Buffered" layer and generate a spatial index for this layer also
In the "Processing Toolbox" select "Extract by location" and set it up like
the picture under and click run

You should now have a layer with the points that are 6 meters away from your street points or less

GPS coordinates coming from vehicle connect to streets coordinates

One Answer

Add your own answers!

Ask a Question