Forem: GreenFlux

May the Nodes Be with You

GreenFlux — Sun, 04 May 2025 13:37:47 +0000

Knowledge graphs are powerful tools to visualize and explore your data, and can help uncover new insights and patterns in how your data is related. They are easy to search and navigate, but getting your data into the graph database in the right format can be challenging. And the resulting graph is only as good as the data--- or as they say, garbage in, garbage out. So it's important to define a good schema, or ontology, that describes the entities and relationships you want to extract from your data.

With unstructured text, you can use an LLM to extract entities and relationships, and then generate a Cypher query using the model (guide). This can take a lot of the work out of generating the Cypher queries but it comes with the risk of hallucinations, and the security and privacy concerns of sending your data to a 3rd party.

When working with structured data though, you can just write a function to map over the data and build the Cypher query programmatically, without the risk of hallucinations, or sharing your data with a 3rd party. With just a few lines of JavaScript, you can map over API responses and bulk insert data into a knowledge graph to use for exploring data visually, or building a RAG pipeline.

In this guide, we'll be using the Star Wars API to fetch data about an episode, and all of the planets, star ships, vehicles and species mentioned in it. Then, we'll map over that data to build a Cypher query, and run it to build a new graph.

Topics Covered:

Using the Star Wars API
Merging data from multiple endpoints with JavaScript
Mapping the results to a Cypher query
Running the query via Neo4j's REST API

Let's get to it!

Using the Star Wars API

The original Star Wars API (swapi.co) was a free API with data on Star Wars characters, planets, ships, etc. It is no longer maintained, but a few newer API providers offer a similar service. For this guide, I'll be using swapi.info. Just like the original API, swapi.info works without a login and is completely free to use.

Take a look at the JSON structure for a record from the /films endpoint.

There's a title, and a few other data points, and then arrays for characters, planets, starships, vehicles, and species. Each one of those array entries link to another endpoint, and record, with more details. We can use this data to build a knowledge graph by mapping over it and building a Cypher query to insert the data into Neo4j.

Local Neo4j and Appsmith

For this guide, I'll be using Neo4j as the graph database, and Appsmith to run the API calls and write the JavaScript. Check out this tutorial on how to deploy both in Docker, or you can use the cloud versions of Appsmith and Neo4j/AuraDB.

Alright, let's graph the planets and species in the first 3 movies and see how they are related. Notice how in the JSON above, each related record is just a link, and doesn't include the title or name of the entity. This means we'll have to loop through all the related records to lookup the name or title before the graph can be built.

Normally I would use the REST API query in Appsmith, but in this case the looping can be handled nicely with fetch(), and a dynamic URL.

Merging data from multiple endpoints with JavaScript

In Appsmith, create a new JSObject named SWAPI and paste in the following:

export default {
  filmData: [],

  async loadFilms() {
    const base = 'https://swapi.info/api';
    const fetchJson = async url => {
      const res = await fetch(url);
      if (!res.ok) throw new Error(`Failed to fetch ${url}`);
      return res.json();
    };

    const rawFilms = await Promise.all(
      [1, 2, 3].map(id => fetchJson(`${base}/films/${id}`))
    );
    const planetUrls = [...new Set(rawFilms.flatMap(f => f.planets))];
    const speciesUrls = [...new Set(rawFilms.flatMap(f => f.species))];

    const [planetObjs, speciesObjs] = await Promise.all([
      Promise.all(planetUrls.map(fetchJson)),
      Promise.all(speciesUrls.map(fetchJson))
    ]);
    const planetMap = new Map(planetObjs.map(p => [p.url, p.name]));
    const speciesMap = new Map(speciesObjs.map(s => [s.url, s.name]));

    this.filmData = rawFilms.map(f => ({
      episode_id: f.episode_id,
      title:      f.title,
      planets:    f.planets.map(url => planetMap.get(url)),
      species:    f.species.map(url => speciesMap.get(url))
    }));
    return this.filmData;
  }
}

This loops over each related endpoint and returns a new films object with the actual names/titles of the related entities, instead of the URL.

Ok, now that we have all the names, we can loop over all the child arrays and generate a Cypher query.

Mapping the results to a Cypher query

The most important step in building a knowledge graph is planning out an ontology, or schema for the entities and relationships you want to represent. For this example, I'll be representing the first 3 films (episodes 4-6), and a portion of the planets and species, to limit the graph size to 20 nodes.

Add a new function to the JSObject to map the filmData to a Cypher query.

  generate20NodeGraphRequest(data = this.filmData) {
    const films      = data;
    const allPlanets = [...new Set(films.flatMap(f => f.planets))];
    const allSpecies = [...new Set(films.flatMap(f => f.species))];

    // allocate 20 nodes: 3 films + X planets + Y species = 20
    let remaining = 20 - films.length;       // 17
    const planets = allPlanets.slice(0, remaining);
    remaining   -= planets.length;           // leftover for species
    const species = allSpecies.slice(0, remaining);

    const cypher = `
UNWIND $films AS filmObj
WITH filmObj

MERGE (f:Film {episode_id: filmObj.episode_id, title: filmObj.title})

WITH filmObj, f

CALL {
  WITH filmObj, f
  UNWIND filmObj.planets AS pname
  WITH f, pname
  WHERE pname IN $planets
  MERGE (p:Planet {name: pname})
  MERGE (p)-[:FEATURED_IN]->(f)
  RETURN COUNT(*) AS planetCount
}

CALL {
  WITH filmObj, f
  UNWIND filmObj.species AS spname
  WITH f, spname
  WHERE spname IN $species
  MERGE (s:Species {name: spname})
  MERGE (s)-[:FEATURED_IN]->(f)
  RETURN COUNT(*) AS speciesCount
}

RETURN f
`.trim();

    return {
      statements: [{
        statement:  cypher,
        parameters: { films, planets, species }
      }]
    };
  }

This uses the MERGE command to create-or-update the entities, and avoid duplicates. Each name or title is mapped into an array, and then turned into multiple records using UNWIND in the Cypher query.

Running the query via Neo4j's REST API

Ok, we have a Cypher query built from the Star Wars API data. Next, let's add an API to insert the data into Neo4j.

Once you have Neo4j running locally in Docker, create a new API in Appsmith to run a Cypher query.

Name	RunCypher
Method	POST
URL	host.docker.internal/7474/db/neo4j/tx/commit
Body type	JSON
Body	{{ SWAPI.generate20NodeGraphRequest.data }}

Then click the Save URL button to save the credentials to an Appsmith datasource.

Choose Basic for the Authentication type, and then enter your Neo4j user name and password, then click Save.

This will keep the credentials on the Appsmith server instead of in the app itself.

Since we're using Docker to run both Neo4j and Appsmith locally, the localhost URLs are replaced with host.docker.internal.

Run the query in Appsmith, and then open the Neo4j dashboard.

In Neo4j, select all the nodes and relationships by running the query:

MATCH p=()-[]->() RETURN p

Nice! Now we can easily see how all the planets and characters relate back to each movie. Or we can query the graph to answer questions like:

Which species appeared in all 3 films?

MATCH (s:Species)-[:FEATURED_IN]->(f:Film)
WHERE f.episode_id IN [4,5,6]
WITH s, count(DISTINCT f) AS appearances
WHERE appearances = 3
RETURN s.name AS Species

From here, you can use the Neo4j browser to explore the data, or query Neo4j via the API has part of a RAG pipeline.

Conclusion

It can be tempting to use LLMs to generate Cypher queries to insert graph data, but that comes with inherent risks of hallucination, and potential security and privacy issues. When working with structured data like JSON or SQL, you can map over the data using JavaScript to build the Cypher query programmatically, avoiding the risks of using an LLM to generate the query.

This allows you to build knowledge graphs with clearly defined relationships and a more repeatable structure than using LLMs to generate Cypher queries. With a little planning and a few lines of code, you can easily convert structured data into interactive knowledge graphs

Building an Interactive XY Image Plot with Google Apps Script and Leaflet.js

GreenFlux — Sun, 08 Sep 2024 11:14:31 +0000

Google Maps has a ton of features for plotting points on a map, but what if you want to plot points on an image? These XY Image Plot maps are commonly used for floor maps, job site inspections, and even games.

In this guide, I'll show you how to create an interactive map with draggable points using Leaflet.js and Google Apps Script. We'll cover everything from setting up the map to integrating data from Google Sheets, and deploying it as a web app.

This guide will cover:

Setting up Leaflet.js in a Google Apps Script HTML Service
Displaying Markers using data from Google Sheets
Updating Sheets row when a Marker is moved
Creating new Markers from the map and saving to Sheets
Deleting a marker from the web app

Setting up Leaflet.js in a Google Apps Script HTML Service

Leaflet.js is one of the most popular open-source mapping libraries. It's light-weight, easy to use, and had great documentation. They support a ton of different map types, including "CRS.Simple", or Coordinate Reference System, which allows you to supply a background image.

Google Sheets Set Up

Start out by creating a sheet named map_pin with the following structure:

id	title	x	y
1	test1	10	30
2	test2	50	80

Then open Apps Script from the Extensions menu.

Creating HTML File

First, we'll start with the basic example from the Leaflet docs, just to get the library working. You can see the full example in their quick start guide, here.

Add a new HTML File named Index, and set the content to:

<!DOCTYPE html>
<html>
<head>
  <title>Quick Start - Leaflet</title>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.3/dist/leaflet.css" />
  <style>
    #map {
      height: 400px;
    }
  </style>
</head>
<body>
  <div id="map"></div>

  <script src="https://unpkg.com/leaflet@1.9.3/dist/leaflet.js"></script>
  <script>
    var map = L.map('map').setView([40.73, -73.99], 13);

    L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
      maxZoom: 19,
      attribution: '© OpenStreetMap'
    }).addTo(map);

    var marker = L.marker([40.73, -73.99]).addTo(map)
      .bindPopup('Test Popup Message')
      .openPopup();
  </script>
</body>
</html>

Then update the Code.gs file with:

function doGet() {
  const html = HtmlService.createHtmlOutputFromFile('Index')
    .setTitle('Map with Draggable Points')
    .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
  return html;
}

Save, and then click Deploy, and publish as a web app. Then open the link for the new deployment and you should see Leaflet.js displaying a map on New York.

Ok, that's the regular map example using Leaflet. Now on to the CRS.Simple map type, which allows supplying a background image.

Update the HTML with this example from the Leaflet Tutorials.

<!DOCTYPE html>
<html>
<head>
  <title>CRS Simple Example - Leaflet</title>
  <meta charset="utf-8" />
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.3/dist/leaflet.css" />
  <style>
    #map {
      height: 400px;
      width: 600px;
    }
    body {
      margin: 0;
      padding: 0;
    }
  </style>
</head>
<body>
  <div id="map"></div>

  <script src="https://unpkg.com/leaflet@1.9.3/dist/leaflet.js"></script>
  <script>
    // Set up the map with a simple CRS (no geographic projection)
    var map = L.map('map', {
      crs: L.CRS.Simple,
      minZoom: -1,
      maxZoom: 4
    });

    // Define the dimensions of the image
    var bounds = [[0, 0], [1000, 1000]];
    var image = L.imageOverlay('https://leafletjs.com/examples/crs-simple/uqm_map_full.png', bounds).addTo(map);

    // Set the initial view of the map to show the whole image
    map.fitBounds(bounds);

    // Optional: Add a marker or other elements to the map
    var marker = L.marker([500, 500]).addTo(map)
      .bindPopup('Center of the image')
      .openPopup();
  </script>
</body>
</html>

Here we are supplying an image of 1000 x 1000 pixels, then setting the center marker at 500, 500.

Click Save, then Deploy>Test Deployments, to see the new map type. You should now have a map with a background image and a marker plotted in the center.

Initializing a Map with Data from Google Sheets

Next, we'll use data from the sheet to populate a set of markers on the map.

First, add a function to the Code.gs file to get the marker locations:

function getPinData(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('map_pin');
  const data = sh.getDataRange().getValues();
  const json = arrayToJSON(data);
  //Logger.log(json);
  return json
}

function arrayToJSON(data=getPinData()){
  const headers = data[0];
  const rows = data.slice(1);
  let jsonData = [];
  for(row of rows){
    const obj = {};
    headers.forEach((h,i)=>obj[h] = row[i]);
    jsonData.push(obj)
  }
  //Logger.log(jsonData)
  return jsonData
}

Here I'm returning the pins as JSON so they're easier to work with in the HTML in the next section.

Now add a function to the HTML to loop over this JSON and create the map pins after the map has loaded.

// Add map pins from sheet data
    google.script.run.withSuccessHandler(addMarkers).getPinData();

    function addMarkers(mapPinData) {
      mapPinData.forEach(pin => {
        const marker = L.marker([pin.x, pin.y], {
          draggable: true
        }).addTo(map);

        marker.bindPopup(`<b>${pin.title}`).openPopup();

        marker.on('dragend', function(e) {
          const latLng = e.target.getLatLng();
          console.log(`Marker ${pin.title} moved to: ${latLng.lat}, ${latLng.lng}`);
        });
      });
    }

Save, and then open the test deployment. You should now have markers generated from your sheet data!

Each pin has a popup with the title from that row. The pins are draggable at this point, but we still need a function to save the new position.

Saving Marker Position When Dragged

To save the new position, we need two functions: one in the HTML to capture the event on the client side, and one to save the new position on the server side, in the Code.gs file.

Update the HTML with:

    function addMarkers(mapPinData) {
      mapPinData.forEach(pin => {
        const { id, title, x, y } = pin;
        const marker = L.marker([x, y], {
          draggable: true
        }).addTo(map);

        marker.bindPopup(`<b>${title}</b>`).openPopup();

        marker.on('dragend', function(e) {
          const latLng = e.target.getLatLng();
          console.log(`Marker ${title} moved to: ${latLng.lat}, ${latLng.lng}`);
          saveMarkerPosition({ id, title, lat: latLng.lat, lng: latLng.lng });
        });
      });
    }

    function saveMarkerPosition({ id, title, lat, lng }) {
      google.script.run.saveMarkerPosition({ id, title, lat, lng });
    }

And then add a function to the Code.gs file to save the location:

function saveMarkerPosition({ id, lat, lng }) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('map_pin');
  const data = sh.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === id) {  // ID column (index 0)
      sh.getRange(i + 1, 3).setValue(lat);  // latitude column
      sh.getRange(i + 1, 4).setValue(lng);  // longitude column
      break;
    }
  }
}

Save, and refresh the test deployment. You should now see the sheet update when a marker is dragged!

Adding New Points

We can now move the existing points, but what about adding new ones? Again, we'll need two functions, one in the HTML, and one in the Code.gs file.

First, add a function to the HTML to open a prompt when the user clicks an empty spot on the map, and pass the value to a server function.

    // Function to add a new pin
    map.on('click', function(e) {
      const latLng = e.latlng;
      const title = prompt('Enter a title for the new pin:');
      if (title) {
        google.script.run.withSuccessHandler(function(id) {
          addNewMarker({ id, title, lat: latLng.lat, lng: latLng.lng });
        }).addNewPin({ title, lat: latLng.lat, lng: latLng.lng });
      }
    });

    function addNewMarker({ id, title, lat, lng }) {
      const marker = L.marker([lat, lng], {
        draggable: true
      }).addTo(map);

      marker.bindPopup(`<b>${title}</b>`).openPopup();

      marker.on('dragend', function(e) {
        const latLng = e.target.getLatLng();
        saveMarkerPosition({ id, title, lat: latLng.lat, lng: latLng.lng });
      });
    }

Then add the function to the Code.gs to save the new row.

function addNewPin({ title, lat, lng }) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('map_pin');

  // Check if there are any rows present, if not initialize ID
  const lastRow = sh.getLastRow();
  let newId = 1;

  if (lastRow > 0) {
    const lastId = sh.getRange(lastRow, 1).getValue();
    newId = lastId + 1;
  }

  sh.appendRow([newId, title, lat, lng]);

  return newId;
}

Save once more and refresh the test deployment. Now when you click an empty spot, you can enter a title and save a new marker!

Deleting A Marker

Lastly, we should add a way to delete markers, giving us a full CRUD app in map view.

Update the add marker function to give the popup a delete button:


      const popupContent = `<b>${title}</b><br><button onclick="deleteMarker(${id})">Delete Marker</button>`;
      marker.bindPopup(popupContent).openPopup();

And then add a function for deleting from the client side:

// Function to delete a marker
  function deleteMarker(id) {
    const confirmed = confirm('Are you sure you want to delete this marker?');
    if (confirmed) {
      google.script.run.withSuccessHandler(() => {
        // Refresh the markers after deletion
        google.script.run.withSuccessHandler(addMarkers).getPinData();
      }).deleteMarker(id);
    }
  }

Then add the matching function to the Code.gs file:

function deleteMarker(id) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName('map_pin');
  const data = sh.getDataRange().getValues();

  for (let i = 1; i < data.length; i++) {
    if (data[i][0] === id) {  // ID column (index 0)
      sh.deleteRow(i + 1);  // Delete the row
      break;
    }
  }
}

What's Next?

There's a ton more you could do from here, like adding other data points to each marker, dynamic background images, or other click and drag interactions. You could even make a game! Got an idea for a use case? Drop a comment below!