I have a SQLite database with a table with 2.2 million rows of GPS data. The (simplified) columns are id, timestamp, lat, lon. There are about 2000 IDs, so each id tracks a GPS movement over time. I would like to know the best way to plot this on a map with observable. Specifically:
the points from each ID must be grouped into a linestring (ie don’t want points plotted, but the trajectory)
ideally I would still be able to keep the timestamp so I can have that appear as a hover
the map will be interactive and performant
I know how to do it in R or Python, but haven’t figured out a nice and scalable way to do this for observable (which I’m trying to learn). All advice and ideas welcome, thanks!
This is certainly too much for SVG (a browser might or might not be able to render a path with 2 million points, and certainly won’t be performant if it does), so you’ll probably want to render this dataset to a canvas. You can use d3 to project the coordinates onto a map.
For the hover component, I’d recommend using a quadtree of the (projected) points for fast lookups.
Let us know you can share a notebook with the dataset.
The first one uses the line mark; it is slow to respond to interactions because it is a very heavy SVG.
The second chart uses the raster mark, which results in a canvas for the main visual part, enabling fast interactions. However, the dots are not connected.
The third chart creates connected lines on a canvas, and responds very quickly to interactions.