Using Cesium for display of remote parquet.

parquet

spatial

recipe

This page renders points from an iSamples parquet file on cesium using point primitives.

Cesium.Ion.defaultAccessToken = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJqdGkiOiIwNzk3NjkyMy1iNGI1LTRkN2UtODRiMy04OTYwYWE0N2M3ZTkiLCJpZCI6Njk1MTcsImlhdCI6MTYzMzU0MTQ3N30.e70dpNzOCDRLDGxRguQCC-tRzGzA-23Xgno5lNgCeB4';

viewof parquet_path = Inputs.text({label:"Source", value:"https://storage.googleapis.com/opencontext-parquet/oc_isamples_pqg.parquet", width:"100%", submit:true});

Heads up: first interaction may be slow

The first click or query can take a few seconds while the in‑browser database engine initializes and the remote Parquet file is fetched and indexed. Subsequent interactions are much faster because both the browser and DuckDB cache metadata and column chunks, so later queries reuse what was already loaded.

Code

db = {
  const instance = await DuckDBClient.of();
  await instance.query(`create view nodes as select * from read_parquet('${parquet_path}')`)
  return instance;
}


async function loadData(query, params = [], waiting_id = null, key = "default") {
    // latest-only guard per key
    loadData._latest = loadData._latest || new Map();
    const requestToken = Symbol();
    loadData._latest.set(key, requestToken);

    // Get loading indicator
    const waiter = waiting_id ? document.getElementById(waiting_id) : null;
    if (waiter) waiter.hidden = false;

    try {
        // Run the (slow) query
        const _results = await db.query(query, params);
        // Ignore stale responses
        if (loadData._latest.get(key) !== requestToken) return null;
        return _results;
    } catch (error) {
        if (waiter && loadData._latest.get(key) === requestToken) {
            waiter.innerHTML = `<pre>${error}</pre>`;
        }
        return null;
    } finally {
        // Hide the waiter (if there is one) only if latest
        if (waiter && loadData._latest.get(key) === requestToken) {
            waiter.hidden = true;
        }
    }
}

locations = {
    // get the content form the parquet file
    const query = `SELECT pid, latitude, longitude FROM nodes WHERE otype='GeospatialCoordLocation'`;
    const data = await loadData(query, [], "loading_1", "locations");

    // Clear the existing PointPrimitiveCollection
    content.points.removeAll();
    //content.points = new Cesium.PointPrimitiveCollection();

    // create point primitives for cesium display
    const scalar = new Cesium.NearFarScalar(1.5e2, 2, 8.0e6, 0.2);
    const color = Cesium.Color.PINK;
    const point_size = 4;
    for (const row of data) {
        content.points.add({
            id: row.pid,
            // https://cesium.com/learn/cesiumjs/ref-doc/Cartesian3.html#.fromDegrees
            position: Cesium.Cartesian3.fromDegrees(
                row.longitude,  //longitude
                row.latitude,   //latitude
                0,//randomCoordinateJitter(10.0, 10.0), //elevation, m
            ),
            pixelSize: point_size,
            color: color,
            scaleByDistance: scalar,
        });
    }
    content.enableTracking();
    return data;
}


function createShowPrimitive(viewer) {
    return function(movement) {
        // Get the point at the mouse end position
        const selectPoint = viewer.viewer.scene.pick(movement.endPosition);        

        // Clear the current selection, if there is one and it is different to the selectPoint
        if (viewer.currentSelection !== null) {
            //console.log(`selected.p ${viewer.currentSelection}`)
            if (Cesium.defined(selectPoint) && selectPoint !== viewer.currentSelection) {
                console.log(`selected.p 2 ${viewer.currentSelection}`)
                viewer.currentSelection.primitive.pixelSize = 4;
                viewer.currentSelection.primitive.outlineColor = Cesium.Color.TRANSPARENT;
                viewer.currentSelection.outlineWidth = 0;
                viewer.currentSelection = null;
            }
        }

        // If selectPoint is valid and no currently selected point
        if (Cesium.defined(selectPoint) && selectPoint.hasOwnProperty("primitive")) {
            //console.log(`showPrimitiveId ${selectPoint.id}`);
            //const carto = Cesium.Cartographic.fromCartesian(selectPoint.primitive.position)
            viewer.pointLabel.position = selectPoint.primitive.position;
            viewer.pointLabel.label.show = true;
            //viewer.pointLabel.label.text = `id:${selectPoint.id}, ${carto}`;
            viewer.pointLabel.label.text = `${selectPoint.id}`;
            selectPoint.primitive.pixelSize = 20;
            selectPoint.primitive.outlineColor = Cesium.Color.YELLOW;
            selectPoint.primitive.outlineWidth = 3;
            viewer.currentSelection = selectPoint;
        } else {
            viewer.pointLabel.label.show = false;
        }            
    }
}

class CView {
    constructor(target) {
        this.viewer = new Cesium.Viewer(
            target, {
                timeline: false,
                animation: false,
                baseLayerPicker: false,
                fullscreenElement: target,
                terrain: Cesium.Terrain.fromWorldTerrain()
            });
        this.currentSelection = null;
        this.point_size = 1;
        this.n_points = 0;
        // https://cesium.com/learn/cesiumjs/ref-doc/PointPrimitiveCollection.html
        this.points = new Cesium.PointPrimitiveCollection();
        this.viewer.scene.primitives.add(this.points);
     
        this.pointLabel = this.viewer.entities.add({
            label: {
            show: false,
            showBackground: true,
            font: "14px monospace",
            horizontalOrigin: Cesium.HorizontalOrigin.LEFT,
            verticalOrigin: Cesium.VerticalOrigin.BOTTOM,
            pixelOffset: new Cesium.Cartesian2(15, 0),
            // this attribute will prevent this entity clipped by the terrain
            disableDepthTestDistance: Number.POSITIVE_INFINITY,
            text:"",
            },
        });

        this.pickHandler = new Cesium.ScreenSpaceEventHandler(this.viewer.scene.canvas);
        // Can also do this rather than wait for the points to be generated
        //this.pickHandler.setInputAction(createShowPrimitive(this), Cesium.ScreenSpaceEventType.MOUSE_MOVE);

        this.selectHandler = new Cesium.ScreenSpaceEventHandler(this.viewer.scene.canvas);
        this.selectHandler.setInputAction((e) => {
            const selectPoint = this.viewer.scene.pick(e.position);
            if (Cesium.defined(selectPoint) && selectPoint.hasOwnProperty("primitive")) {
                mutable clickedPointId = selectPoint.id;
            }
        },Cesium.ScreenSpaceEventType.LEFT_CLICK);

    }

    enableTracking() {
        this.pickHandler.setInputAction(createShowPrimitive(this), Cesium.ScreenSpaceEventType.MOUSE_MOVE);
    }
}

content = new CView("cesiumContainer");

async function getGeoRecord(pid) {
    if (pid === null || pid ==="" || pid == "unset") {
        return "unset";
    }
    const q = `SELECT row_id, pid, otype, latitude, longitude FROM nodes WHERE otype='GeospatialCoordLocation' AND pid=?`;
    const rows = await loadData(q, [pid], "loading_geo", "geo");
    return rows && rows.length ? rows[0] : null;
}

async function get_samples_1(pid) {
    if (pid === null || pid ==="" || pid == "unset") {
        return [];
    }
    const q = `
        SELECT DISTINCT
            s.pid as sample_id,
            s.label as sample_label,
            s.name as sample_name,
            event.pid as event_id,
            event.label as event_label,
            'direct_event_location' as location_path
        FROM nodes s
        JOIN nodes e1 ON s.row_id = e1.s AND e1.p = 'produced_by'
        JOIN nodes event ON e1.o[1] = event.row_id
        JOIN nodes e2 ON event.row_id = e2.s AND e2.p = 'sample_location'
        JOIN nodes g ON e2.o[1] = g.row_id
        WHERE s.otype = 'MaterialSampleRecord'
          AND event.otype = 'SamplingEvent'
          AND g.otype = 'GeospatialCoordLocation'
          AND g.pid = ?
    `;
    const result = await loadData(q, [pid], "loading_s1", "samples_1");
    return result ?? [];
}

async function get_samples_2(pid) {
    if (pid === null || pid ==="" || pid == "unset") {
        return [];
    }
    const q = `
        SELECT DISTINCT
            s.pid as sample_id,
            s.label as sample_label,
            s.name as sample_name,
            event.pid as event_id,
            event.label as event_label,
            site.label as site_name,
            'via_site_location' as location_path
        FROM nodes s
        JOIN nodes e1 ON s.row_id = e1.s AND e1.p = 'produced_by'
        JOIN nodes event ON e1.o[1] = event.row_id
        JOIN nodes e2 ON event.row_id = e2.s AND e2.p = 'sampling_site'
        JOIN nodes site ON e2.o[1] = site.row_id
        JOIN nodes e3 ON site.row_id = e3.s AND e3.p = 'site_location'
        JOIN nodes g ON e3.o[1] = g.row_id
        WHERE s.otype = 'MaterialSampleRecord'
          AND event.otype = 'SamplingEvent'
          AND site.otype = 'SamplingSite'
          AND g.otype = 'GeospatialCoordLocation'
          AND g.pid = ?
    `;
    const result = await loadData(q, [pid], "loading_s2", "samples_2");
    return result ?? []; 
}

async function locationUsedBy(rowid){
    if (rowid === undefined || rowid === null) {
        return [];
    }
    const q = `select pid, otype from nodes where row_id in (select nodes.s from nodes where list_contains(nodes.o, ?));`;
    return db.query(q, [rowid]);
}

mutable clickedPointId = "unset";
// Loading flags to control UI clearing while fetching
mutable geoLoading = false;
mutable s1Loading = false;
mutable s2Loading = false;

// Precompute selection-driven data with loading flags
selectedGeoRecord = {
    mutable geoLoading = true;
    try {
        return await getGeoRecord(clickedPointId);
    } finally {
        mutable geoLoading = false;
    }
}

selectedSamples1 = {
    mutable s1Loading = true;
    try {
        return await get_samples_1(clickedPointId);
    } finally {
        mutable s1Loading = false;
    }
}

selectedSamples2 = {
    mutable s2Loading = true;
    try {
        return await get_samples_2(clickedPointId);
    } finally {
        mutable s2Loading = false;
    }
}

md`Retrieved ${pointdata.length} locations from ${parquet_path}.`;

Loading…

Code

viewof pointdata = {
    const data_table = Inputs.table(locations, {
        header: {
            row_id:"Row ID",
            pid: "PID",
            latitude: "Latitude",
            longitude: "Longitude"
        },
    });
    return data_table;
}

The click point ID is “”.

geoLoading ? md`(loading…)` : md`\`\`\`
${JSON.stringify(selectedGeoRecord, null, 2)}
\`\`\`
`

1 getGeoRecord (selected)

Code

pid = clickedPointId
testrecord = selectedGeoRecord;

md`\`\`\`
${JSON.stringify(testrecord, null, 2)}
\`\`\`
`

2 Related Sample Path 1 (selected)

Path 1 (direct_event_location): find MaterialSampleRecord items whose producing SamplingEvent has a direct sample_location pointing to the clicked GeospatialCoordLocation (pid).

Chain: MaterialSampleRecord → produced_by → SamplingEvent → sample_location → GeospatialCoordLocation (clicked pid)
This matches the “direct_samples” concept in the Python notebook and is labeled as location_path = 'direct_event_location' in the query.

samples_1 = selectedSamples1
s1Loading ? md`(loading…)` : md`\`\`\`
${JSON.stringify(samples_1, null, 2)}
\`\`\`
`

3 Related Sample Path 2 (selected)

Path 2 (via_site_location): find MaterialSampleRecord items whose producing SamplingEvent references a SamplingSite, and that site’s site_location points to the clicked GeospatialCoordLocation (pid).

Chain: MaterialSampleRecord → produced_by → SamplingEvent → sampling_site → SamplingSite → site_location → GeospatialCoordLocation (clicked pid)
This matches the “samples_via_sites” concept in the Python notebook and is labeled as location_path = 'via_site_location' in the query.

samples_2 = selectedSamples2
s2Loading ? md`(loading…)` : md`\`\`\`
${JSON.stringify(samples_2, null, 2)}
\`\`\`
`