Make nicer Cesium map point click events with more meaningful data

[ekansa]

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:

• Sample PID (ideally resolvable as working hyperlink)
• Sample type, sample material
• Sample data source, collection
• Perhaps keywords

In the Open Context case, we also provide thumbnails of samples in our iSamples parquet output. Displaying those thumbnails would be great for demos!

[rdhyee]

@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

[NatalieRaia]

@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.

[rdhyee]

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:

1. Direct Event Location Path (direct_event_location):

   • MaterialSampleRecord → produced_by → SamplingEvent → sample_location → GeospatialCoordLocation
   • Finds samples whose sampling events directly reference the clicked location

2. 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

[ekansa]

@rdhyee here are some queries that should help get you started. You will see that I'm including an otype as a filter in many WHERE clauses and JOINs. That seems to improve performance a great deal (I guess it means smaller row-scans).

1. Gets samples from a given GeospatialCoordLocation identified 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
     ;

2. Gets general data about a MaterialSampleRecord identified 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'
   ;

3. Gets sample type, sample materials, and keywords for a MaterialSampleRecord identified 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'
    ;

4. Gets Agent information associated with a MaterialSampleRecord identified 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