Using Cesium for display of remote parquet.

parquet
spatial
recipe

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

In this case, the Open Context source has been split into separate nodes and edges parquet files, with the edges table containing only s, p, and un-nested o for _edge_ rows, and the same content removed from the nodes table.

The resulting files sizes are smaller at about 58 and 236 mb for edges and nodes respectively.

This page loads the entire edges file, but keeps the nodes resource remote only. The initial query when clicking on a point is a bit slow, but performance should improve significantly for subsequent interactions.

Code 
db = {
  const instance = await DuckDBClient.of();
  await instance.query(`create view nodes as select * from read_parquet('${nodes_path}')`);
  instance.query(`create table edges as select s,p,o from read_parquet('${edges_path}')`);
  return instance;
}


async function loadData(query, params=[], waiting_id=null) {
    // Get loading indicator
    const waiter = document.getElementById(waiting_id);
    if (waiter) {
        waiter.hidden = false;
    }
    try {
        // Run the (slow) query
        const _results = await db.query(query, ...params);
        return _results;
    } catch (error) {
        if (waiter) {
            waiter.innerHtml = `<pre>${error}</pre>`;
        }
        return null;
    } finally {
        // Hide the waiter (if there is one)
        if (waiter) {
            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");

    // 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 result = await db.queryRow(q, [pid]);
    return result;
}

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

async function samplesAtLocation(rowid) {
    if (rowid === undefined || rowid === null) {
        return [];
    }
    const q = `select pid, label, description from nodes where row_id in (
    with recursive efor(s,p,o) as (
        select s,p,o from edges where o=? 
        union all
        select e.s, e.p, e.o from edges as e, efor as ef where ef.s = e.o
    ) select s from efor where p='produced_by');`;
    return db.query(q, [rowid]);
}

mutable clickedPointId = "unset";
selectedGeoRecord = await getGeoRecord(clickedPointId);

md`Retrieved ${pointdata.length} locations from ${nodes_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 “”.

Inputs.table(locationUsedBy(selectedGeoRecord.row_id));
viewof usedby = {
    const table = Inputs.table(samplesAtLocation(selectedGeoRecord.row_id));
    return table;
}