Currently, I’m using a map visualization(points on map) to track my devices. I have multiple devices that send data (latitude, longitude, and other parameters) at regular intervals (every few seconds/minutes).
Using parameters, I can select a specific device and, based on a time range, view where that device has been.
However, when I try to display the locations of all devices together, the map becomes cluttered (as shown below). I would like to display only the latest location of each device—just one point per device.
Additionally, for a single device, is there a way to connect the points based on time, or visualize the path to understand how the device moved from one location to another?
Also, are there any available documents or guides on how to display geofences or trackers (location service) maps in Amazon QuickSight along with the location points ( FYI: data points are from Amazon Athena dataset )?
This is a very interesting use case! To handle the visual logic to only show the latest location per device will have to be done through a calculated field. I do not know what fields you have within your dataset, but the logic of the calculated field could look something like this:
and then you will have to apply a filter on your visual to only show values that equal ‘Yes’. Ideally speaking this should only show the latest points within your visual.
For your question about visualizing movement paths, Unfortunately, the Points on Map visual doesn’t have a way to draw lines between points. Your best bet is the Layer Map and it lets you upload GeoJSON shape layers. If you pre-generate ‘LineString’ features for each device’s path (ordered by timestamp), you can overlay those routes right on the map. If you want something quicker and don’t need actual lines, try putting a sequential color gradient on the timestamp in the color well. I do not believe it will connect the dots, but I think the light-to-dark progression may give a decent sense of direction.
Lastly for your geofences or tracker question, there is no direct integration between Location Service geofences and QuickSight yet, but the Layer Map works here too. Just export your geofence collection as GeoJSON polygons, upload it as a shape layer, and you’ll see your boundaries alongside your device points. Upon researching this topic, I did find a blog post that talks about combining Athena data with Location Service on a map that might be useful.
Regarding the last known location point, I’m now able to retrieve the latest point using a calculated field—thanks for your guidance on that.
As an extension, I’m working on single-device location tracking using a layer map. I already have a .geojson file for geofencing. However, while adding latitude and longitude, I don’t see a direct field available.
Based on your suggestion, I added a new dataset field with the Shape file key field set to none and the Dataset key field set as geojson_linestring. Below is the expression I used in the view creation (QuickSight dataset): CONCAT( ‘{“type”:“LineString”,“coordinates”:[’, array_join( array_agg( CASE WHEN lat IS NOT NULL AND lon IS NOT NULL THEN CONCAT( ‘[’, CAST(lon AS VARCHAR), ‘,’, – GeoJSON = [lon, lat] CAST(lat AS VARCHAR), ‘]’ ) END ) OVER ( PARTITION BY device_id, date_trunc(‘minute’, from_unixtime((CAST(ts AS BIGINT) / 1000))) ORDER BY from_unixtime((CAST(ts AS BIGINT) / 1000)) ), ‘,’ ), ‘]}’ ) AS geojson_linestring
Glad the calculated field worked for the latest location!
I owe you a correction on the path visualization. After going back through the docs, QuickSight’s Layer Map only supports polygon shapes; therefore, I read this as LineString and Point geometries are not supported (docs). So the approach you’re trying, generating GeoJSON LineStrings from your Athena view, won’t render in QuickSight regardless of how the GeoJSON is formatted. That’s my mistake for suggesting it so I apologize for that.
As it stands, there isn’t a native way to draw connected paths between points in QuickSight. Your realistic options are:
Within QuickSight: You can use the Points on Map visual and drag your timestamp field into the Color well as a dimension. This won’t draw lines, but each point will be colored by its timestamp value, giving readers a rough visual sense of sequence. When you drop a date field in the Color well, you can change the granularity (hour, minute, etc.) using the field well dropdown. That way this can help keep the legend manageable instead of showing every individual timestamp.
Outside QuickSight: If you need actual connected lines and path visualization, you could build a lightweight web app using a mapping library like Mapbox GL or Leaflet that queries the same Athena data source, then embed it into your QuickSight dashboard using the Custom Visual Content chart type (which iframes any HTTPS webpage into a visual). You can also use QuickSight parameters in the URL so that dashboard filters pass through to the embedded map. It’s a fair bit of work to set up, but it gives you full control over polylines, direction arrows, animation, etc.
Again, sorry for the earlier misdirection on LineString support and hope this helps!
No problem at all—it’s actually helping me learn a lot. Glad we could connect.
Thanks for the quick response as well; I really appreciate it.
I had one last question: I tried using the “Within QuickSight” option to track location. When I select Basic points and set the color to event_time (seconds), the points show up in random colors. If I switch to a single color, then all points appear the same.
Also, when I try using the Heatmap, I’m unable to set the color field or choose the time field.
Is there any way to use Basic points with a gradient (two-color scale) so that the points change gradually based on time?
Nice to connect with you as well! This has been a very interesting use case to work through. I do not believe the Points on the Map visual supports gradient color scale so we may have to indirectly create it. My thought on this is to create a calculated field that will take place in the color field well that takes all the values in your event_time field and somehow groups them in a way so that you can better manage the colors within the visual. I came up with this calculated field for a potential workaround:
The idea (and hope) is that it will still plot all the dots in the visual and apply a bit of grouping logic so the output is only 5 colors rather than assigning a random color to every single value. This way you can indirectly create a gradient color scale by assigning a specific value to each group. Depending on the data type of your event_time field, I can see you running into some problems with the percentileOver() function, but hopefully this helps get you in the right direction. Try it out and let me know!
