Repository navigation
Make nicer Cesium map point click events with more meaningful data #18
Description
Activity
@ekansa I just started into this issue and realized that I need a much better understanding of the data structure of the geoparquet file. I started down the road of figuring it out but having you point me to any documentation (or to the code that you used to generate the file) would be really helpful.
Take a look at 5475b5b#diff-6824b570cc82d5da7cc437e6bec3277b8065754ce264dbc3ed5bbf9c7d148b82 and what's currently at https://isamples.org/tutorials/parquet_cesium.html
@ekansa, I totally agree that this is worthwhile (and agree with the specific metadata you propose to include). I really like the context text at top of the tutorial, as well.
Update: Interactive Cesium Visualization with Dynamic Parquet Queries
I've made significant progress on enhancing the parquet_cesium.qmd tutorial to demonstrate interactive geospatial visualization capabilities. The implementation now showcases how to dynamically query parquet data based on user interactions with the Cesium map.
Key Achievements
Interactive Map Selection
- Users can now click on any point on the Cesium map to explore related sample data
- The clicked location's PID is used to dynamically query related samples from the parquet file
- Real-time data fetching with proper loading states and error handling
Two Query Paths Implemented
Following the graph structure in the iSamples data model, I've implemented two distinct paths for finding samples related to a geospatial location:-
Direct Event Location Path (
direct_event_location):- MaterialSampleRecord → produced_by → SamplingEvent → sample_location → GeospatialCoordLocation
- Finds samples whose sampling events directly reference the clicked location
-
Via Site Location Path (
via_site_location):- MaterialSampleRecord → produced_by → SamplingEvent → sampling_site → SamplingSite → site_location → GeospatialCoordLocation
- Finds samples whose sampling events reference sites that are at the clicked location
Technical Improvements
- Implemented async data loading with request tokens to prevent race conditions during rapid clicking
- Added comprehensive loading indicators for better user experience
- Used DuckDB's graph traversal capabilities to efficiently query relationships in the parquet data
- Maintained stateless operation - all queries run directly against the remote parquet file
Live Demo
The enhanced tutorial is available in PR #25 and demonstrates:
- Loading ~14,000 geospatial locations from OpenContext's iSamples parquet export
- Interactive exploration of the data relationships
- Real-time querying without any server-side infrastructure
This work shows how parquet + DuckDB-WASM enables sophisticated geospatial data exploration directly in the browser, supporting the iSamples goal of low-cost, sustainable data access patterns.
Related PR: #25
@rdhyee here are some queries that should help get you started. You will see that I'm including an
otypeas a filter in many WHERE clauses and JOINs. That seems to improve performance a great deal (I guess it means smaller row-scans).- Gets samples from a given
GeospatialCoordLocationidentified by a pid:
SELECT geo_pqg.latitude, geo_pqg.longitude, site_pqg.label AS sample_site_label, site_pqg.pid AS sample_site_pid, samp_pqg.pid AS sample_pid, samp_pqg.alternate_identifiers AS sample_alternate_identifiers, samp_pqg.label AS sample_label, samp_pqg.description AS sample_description, samp_pqg.thumbnail_url AS sample_thumbnail_url, samp_pqg.thumbnail_url is NOT NULL as has_thumbnail FROM pqg AS geo_pqg JOIN pqg AS rel_se_pqg ON ( rel_se_pqg.p = 'sample_location' AND contains(rel_se_pqg.o, geo_pqg.row_id) ) JOIN pqg AS se_pqg ON ( rel_se_pqg.s = se_pqg.row_id AND se_pqg.otype = 'SamplingEvent' ) JOIN pqg AS rel_site_pqg ON ( se_pqg.row_id = rel_site_pqg.s AND rel_site_pqg.p = 'sampling_site' ) JOIN pqg AS site_pqg ON ( list_extract(rel_site_pqg.o, 1) = site_pqg.row_id AND site_pqg.otype = 'SamplingSite' ) JOIN pqg AS rel_samp_pqg ON ( rel_samp_pqg.p = 'produced_by' AND contains(rel_samp_pqg.o, se_pqg.row_id) ) JOIN pqg AS samp_pqg ON ( rel_samp_pqg.s = samp_pqg.row_id AND samp_pqg.otype = 'MaterialSampleRecord' ) WHERE geo_pqg.pid = '{geo_loc_pid}' AND geo_pqg.otype = 'GeospatialCoordLocation' ORDER BY has_thumbnail DESC ;
- Gets general data about a
MaterialSampleRecordidentified by a pid:
SELECT samp_pqg.row_id, samp_pqg.pid AS sample_pid, samp_pqg.alternate_identifiers AS sample_alternate_identifiers, samp_pqg.label AS sample_label, samp_pqg.description AS sample_description, samp_pqg.thumbnail_url AS sample_thumbnail_url, samp_pqg.thumbnail_url is NOT NULL as has_thumbnail, geo_pqg.latitude, geo_pqg.longitude, site_pqg.label AS sample_site_label, site_pqg.pid AS sample_site_pid, FROM pqg AS samp_pqg JOIN pqg AS samp_rel_se_pqg ON ( samp_rel_se_pqg.s = samp_pqg.row_id AND samp_rel_se_pqg.p = 'produced_by' ) JOIN pqg AS se_pqg ON ( list_extract(samp_rel_se_pqg.o, 1) = se_pqg.row_id AND se_pqg.otype = 'SamplingEvent' ) JOIN pqg AS geo_rel_se_pqg ON ( geo_rel_se_pqg.s = se_pqg.row_id AND geo_rel_se_pqg.p = 'sample_location' ) JOIN pqg AS geo_pqg ON ( list_extract(geo_rel_se_pqg.o, 1) = geo_pqg.row_id AND geo_pqg.otype = 'GeospatialCoordLocation' ) JOIN pqg AS site_rel_se_pqg ON ( site_rel_se_pqg.s = se_pqg.row_id AND site_rel_se_pqg.p = 'sampling_site' ) JOIN pqg AS site_pqg ON ( list_extract(site_rel_se_pqg.o, 1) = site_pqg.row_id AND site_pqg.otype = 'SamplingSite' ) WHERE samp_pqg.pid = '{sample_pid}' AND samp_pqg.otype = 'MaterialSampleRecord' ;
- Gets sample type, sample materials, and keywords for a
MaterialSampleRecordidentified by a pid:
SELECT samp_pqg.row_id, samp_pqg.pid AS sample_pid, samp_pqg.alternate_identifiers AS sample_alternate_identifiers, samp_pqg.label AS sample_label, kw_rel_se_pqg.p AS predicate, kw_pqg.pid AS keyword_pid, kw_pqg.label AS keyword FROM pqg AS samp_pqg JOIN pqg AS kw_rel_se_pqg ON ( kw_rel_se_pqg.s = samp_pqg.row_id AND list_contains(['keywords', 'has_sample_object_type', 'has_material_category'], kw_rel_se_pqg.p) ) JOIN pqg AS kw_pqg ON ( kw_pqg.row_id = ANY(kw_rel_se_pqg.o) AND kw_pqg.otype = 'IdentifiedConcept' ) WHERE samp_pqg.pid = '{sample_pid}' AND samp_pqg.otype = 'MaterialSampleRecord' ;
- Gets
Agentinformation associated with aMaterialSampleRecordidentified by a pid:
SELECT samp_pqg.row_id, samp_pqg.pid AS sample_pid, samp_pqg.alternate_identifiers AS sample_alternate_identifiers, samp_pqg.label AS sample_label, samp_pqg.description AS sample_description, samp_pqg.thumbnail_url AS sample_thumbnail_url, samp_pqg.thumbnail_url is NOT NULL as has_thumbnail, agent_rel_se_pqg.p AS predicate, agent_pqg.pid AS agent_pid, agent_pqg.name AS agent_name, agent_pqg.alternate_identifiers AS agent_alternate_identifiers FROM pqg AS samp_pqg JOIN pqg AS samp_rel_se_pqg ON ( samp_rel_se_pqg.s = samp_pqg.row_id AND samp_rel_se_pqg.p = 'produced_by' ) JOIN pqg AS se_pqg ON ( list_extract(samp_rel_se_pqg.o, 1) = se_pqg.row_id AND se_pqg.otype = 'SamplingEvent' ) JOIN pqg AS agent_rel_se_pqg ON ( agent_rel_se_pqg.s = se_pqg.row_id AND list_contains(['responsibility', 'registrant'], agent_rel_se_pqg.p) ) JOIN pqg AS agent_pqg ON ( agent_pqg.row_id = ANY(agent_rel_se_pqg.o) AND agent_pqg.otype = 'Agent' ) WHERE samp_pqg.pid = '{sample_pid}' AND samp_pqg.otype = 'MaterialSampleRecord' ;
I've got these queries implemented in Python and DuckDB here for testing:
https://github.com/ekansa/open-context-py/blob/staging/opencontext_py/apps/all_items/isamples/isamples_explore.py- Gets samples from a given
Implemented in
progressive_globe.qmd(the Interactive Explorer). A click on a sample now shows:- Sample PID as a working n2t.net hyperlink
- Source badge (color-coded)
- Label, place name, and date
- Description (lazy-loaded from wide parquet)
- Nearby samples list
See the click handler in progressive_globe.qmd. Thumbnail display for OpenContext samples would be a follow-up — open a new issue if still wanted.
Currently in the Cesium / parquet demos, the map point click events return results that are very cryptic for an outside user.
It would be good for the Cesium / Parquet demos to return more meaningful results when a user clicks a map point. We shouldn't return what a geo location record looks like, and we should focus on returning sample records (because samples are the main topic of iSamples). The following should be displayed:
Example sample records from that point, with certain metadata that include:
In the Open Context case, we also provide thumbnails of samples in our iSamples parquet output. Displaying those thumbnails would be great for demos!