Knowledge Base

Introduction to NQL and Sedona

Narrative Query Language (NQL) is a powerful SQL-like query language designed specifically for the Narrative platform. It allows users to execute intricate SQL queries on Datasets or Narrative's attribute library.

Sedona is a spatial data processing engine that extends the capabilities of NQL to handle geospatial data. It provides a range of functions to process and analyze geographic information, enabling the extraction of valuable insights from location-based data.

Sedona Functions Overview

Sedona offers several functions to handle geospatial data. Here are some key functions:

• ST_INTERSECTS(geometry1, geometry2): Checks if two geometries intersect.
• ST_SAFEPOINT(longitude, latitude): Creates a point geometry from longitude and latitude values, ensuring the coordinates are valid.
• ST_CIRCLE(longitude, latitude, radius in meters): Creates a circular geometry with a specified radius around a point defined by longitude and latitude.

Preparing Your Dataset

To use Sedona functions effectively, users should create a dataset with the underlying location Points of Interest (POIs) in one of the following formats:

• WKT (Well-Known Text): A text markup language for representing vector geometry objects.
• GeoJSON: A format for encoding a variety of geographic data structures using JavaScript Object Notation (JSON).
• Latitude/Longitude Points: Standard geographical coordinates that can be converted into point radius polygons.

These formats enable the creation of geometries that can be used in Sedona functions such as ST_SAFEPOINT and ST_CIRCLE. Here's how each format can be utilized:

• WKT Example: POINT(30 10)
• GeoJSON Example: { "type": "Point", "coordinates": [30, 10] }
• Latitude/Longitude Example: (30, 10) which can be converted using ST_SAFEPOINT.

Sample Query Breakdown

Below is a detailed breakdown of a sample query that demonstrates how to use Sedona functions to purchase geo location data:

SELECT
  company_data."12345"."mobile_id_unique_identifier"."value" AS "maid",
  company_data."12345"."event_timestamp" AS "event_timestamp",
  company_data."11111"."poi_id" AS "poi_id"
FROM
  company_data."12345"
  INNER JOIN company_data."11111" ON ST_INTERSECTS (
    ST_SAFEPOINT (
      "12345"."geographic_location"."longitude",
      "12345"."geographic_location"."latitude"
    ),
    ST_CIRCLE ("11111"."Longitude", "11111"."Latitude", 15)
  ) QUALIFY (
    ROW_NUMBER() OVER (
      PARTITION BY
        poi_id,
        event_timestamp,
        maid
      ORDER BY
        event_timestamp DESC
    )
  ) = 1

Query Explanation

1. SELECT Clause:\ company_data."12345"."mobile_id_unique_identifier"."value" AS "maid": Selects the unique identifier for the mobile device (MAID) from dataset 12345.
   • company_data."12345"."event_timestamp" AS "event_timestamp": Selects the event timestamp from dataset 12345.
   • company_data."11111"."poi_id" AS "poi_id": Selects the poi_id from dataset 11111.
2. FROM Clause: Specifies the primary dataset company_data."12345".
3. INNER JOIN Clause: Joins company_data."12345" with company_data."11111" using the ST_INTERSECTS function to check if the geographic location from 12345 intersects with a 15-meter radius circle around the geographic location from 11111.
   • ST_SAFEPOINT("12345"."geographic_location"."longitude", "12345"."geographic_location"."latitude"): Creates a safe point geometry from the longitude and latitude of dataset 12345.
   • ST_CIRCLE("11111"."Longitude", "11111"."Latitude", 15): Creates a circle with a 15-meter radius around the longitude and latitude of dataset 11111.
4. QUALIFY Clause: Filters the results to retain only the most recent event for each combination of poi_id, event_timestamp, and MAID. ROW_NUMBER() OVER (PARTITION BY poi_id, event_timestamp, maid ORDER BY event_timestamp DESC) = 1: Assigns a row number to each record partitioned by poi_id, event_timestamp, and MAID, and ordered by event_timestamp in descending order, keeping only the first record in each partition.

Conclusion

This query leverages the power of NQL and Sedona to process and purchase geo location data efficiently. By understanding and utilizing functions like ST_INTERSECTS, ST_SAFEPOINT, and ST_CIRCLE, users can perform complex geospatial analyses and derive valuable insights from location-based datasets within the Narrative platform.