Efficient k-NN Trajectory Queries on Mobility Databases

Abstract

The rapid adoption of GPS-enabled mobile devices has produced massive trajectory datasets that drive modern applications in traffic prediction, logistics, and spatio-temporal analytics. Yet traditional database management systems (DBMSs) still lack native operators to process such data efficiently. To overcome this limitation, we introduce a set of k-nearest neighbor (k-NN) user-defined aggregates (UDAs) that embed k-NN processing directly within the PostgreSQL engine. By integrating computation into the database core, our approach minimizes data transfer and latency while maintaining low storage overhead. Experiments on benchmarked BerlinMOD-derived datasets demonstrate that the proposed UDAs reduce query execution time by 6–23%, depending on dataset size and query complexity.

1. Introduction

The widespread adoption of GPS-enabled mobile devices has generated massive streams of moving-object data that support applications ranging from urban mobility analysis to intelligent transportation systems [1]. These data now play a central role in traffic prediction, logistics optimization, and spatio-temporal analytics. However, traditional database management systems (DBMSs) still struggle to manage such data efficiently [2,3]. Despite this rapid growth, conventional DBMSs still lack native mechanisms for efficiently processing spatio-temporal queries. As the positions of moving objects are continuously changing, current systems must handle dynamic and evolving datasets [1,4,5,6]. Consequently, there is a growing demand for database extensions that can natively support trajectory-based operations, such as k-nearest neighbor (k-NN) and trajectory join queries.

Although trajectory data can be stored in conventional DBMSs, few systems can efficiently query both spatial and temporal dimensions simultaneously. These specialized systems, often termed mobility databases, integrate spatial and temporal features within a unified framework. Research in this area spans multiple aspects of database technology, including storage, query languages, privacy preservation, and indexing methods [7,8,9,10,11]. The complexity of managing such data continues to grow, as does its potential for comprehensive spatio-temporal analysis.

Among the many query types, the k-nearest neighbor (k-NN) query is particularly valuable because it retrieves the most similar trajectories or locations based on spatial proximity. While index-based techniques (e.g., R-tree, Generalized Search Tree (GiST) [12]) accelerate point-based k-NN searches, they offer limited benefits for trajectory join k-NN queries, where both query and data objects are dynamic [13]. This study addresses this gap by developing efficient k-NN query processing methods for trajectory joins within PostgreSQL-based mobility databases [14].

To address this challenge, we exploit PostgreSQL’s extensibility to embed k-NN processing within the database engine through User-Defined Aggregates (UDAs). Embedding the k-NN computation in UDAs minimizes data transfer and latency, providing an integrated and efficient query framework compared with external procedures. This integration also enables flexible analytical operations—for example, computing the top-k closest delivery routes in real time or summarizing movement patterns across large-scale GPS trajectories.

MobilityDB [15] is a DBMS designed for managing geo-spatial trajectories, such as those generated by GPS. It extends PostgreSQL and PostGIS with temporal and spatio-temporal object support. PostGeoMedia [16,17,18] is another system for managing trajectories and moving objects, also built on PostgreSQL/PostGIS. In this paper, we address the k-NN trajectory query problem by integrating UDAs into PostgreSQL and leveraging PostGeoMedia’s trajectory types and spatial operators. We propose a novel use of SQL aggregation functions to implement k-NN trajectory queries and evaluate the performance of our approach through a comparison with MobilityDB, demonstrating measurable improvements in query efficiency. Building on PostGeoMedia’s trajectory types and distance operators, we define a UDA that maintains a k-priority queue as its internal state and incrementally returns the k nearest trajectories.

This paper is organized as follows. Section 2 reviews existing research on trajectory-based nearest neighbor search in PostgreSQL. Section 3 introduces the trajectory storage strategy, describes trajectory representation, and presents sample queries along with relevant functions. Section 4 details three k-NN algorithms, beginning with a naive baseline and addressing its limitations. Section 5 and its subsections propose enhanced solutions. Section 6 presents the experimental evaluation and comparisons with existing approaches. Section 7 discusses the results and implications. Finally, Section 8 concludes the paper and outlines directions for future research.

2. Related Work

This section reviews nearest neighbor (NN) search techniques with a focus on PostgreSQL-based extensions and their applicability to spatio-temporal and trajectory data management.

2.1. Nearest Neighbor Search on PostgreSQL

PostgreSQL [19] supports user-defined extensions for custom data types, functions, and indexes, making it highly extensible compared with conventional relational systems. Notably, PostgreSQL is among the earliest open-source systems to support k-NN queries natively through its extensible indexing framework [12,19].

Informal technical discussions (e.g., [20]) describe several practical k-NN strategies in PostgreSQL without relying on extensions. These strategies include brute-force table scans with a limit on k results, adding B-tree indexes on latitude and longitude for faster filtering, using federated or compound indexes, and applying clustering-based optimizations. The most advanced form leverages the GiST [12] indexing framework and, more recently, btree_gist, which enables composite indexes combining traditional and geometric data types.

Table 1. PostgreSQL extensions supporting nearest-neighbor queries.

Name	Year	Description
ImgSmlr	2017	image-scene retrieval
Cube	2006	high-dimensional vector data retrieval
Freddy	2017	approximate-NN retrieval
Pase	2020	high-dimensional and approximate-NN search
MobilityDB	2020	supports spatio-temporal queries
Our work	2025	supports spatio-temporal queries and UDAs

summarizes representative PostgreSQL-based systems that support nearest neighbor queries.

Existing PostgreSQL k-NN Strategies

Prior research explores three directions: (1) native strategies using built-in operators, (2) high-dimensional vector extensions, and (3) spatio-temporal trajectory extensions.

Several PostgreSQL extensions focus on high-dimensional k-NN search, including ImgSmlr [21], Cube [22], and Freddy [23]. ImgSmlr implements similarity-based image search by generating floating-point vectors from average values of image patches. It supports multiple image formats and allows user-defined operators. Cube, also based on GiST indexing, facilitates high-dimensional vector operations such as clustering, filtering, and distance calculations. However, Cube demonstrates inferior performance to ImgSmlr due to its use of double precision (float8) instead of single precision (float4) floating-point numbers. While Cube supports up to 100 dimensions, ImgSmlr is optimized for 16. Freddy extends PostgreSQL with custom functions to support approximate nearest neighbor queries, though it lacks internal PostgreSQL optimization and therefore suffers from performance limitations.

Pase [24], an industrial-grade PostgreSQL extension, supports high-dimensional approximate NN search using IVFFlat and HNSW algorithms. However, Pase is not open source and cannot be integrated with PostGIS for spatio-temporal queries. More recently, MobilityDB [15] has emerged as an open-source spatio-temporal database based on PostgreSQL/PostGIS, with support for managing moving-object trajectories. Unlike MobilityDB, our approach embeds k-NN aggregation inside the database engine and does not require external pluggable modules, enabling tighter coupling with SQL execution and reduced I/O overhead.

Building on PostgreSQL’s GiST infrastructure, subsequent work [25] introduced the Space-Partitioned Generalized Search Tree (SP-GiST), a flexible indexing architecture that supports space-partitioning structures such as tries and kd-trees. SP-GiST delivers faster exact-match and prefix or regular-expression searches than ${\mathrm{B}}^{+}$-trees when point predicates dominate the workload. For pure point-matching queries, kd-trees outperform R-trees in query efficiency, although SP-GiST generally incurs higher index-build and insertion costs than conventional R-tree and ${\mathrm{B}}^{+}$-tree indexes.

A more recent implementation [26] integrated the Metric-tree (M-tree) [27] into PostgreSQL by means of GiST. M-trees excel at range and k-NN queries in metric spaces, provided that node-splitting heuristics are chosen carefully to sustain performance and avoid excessive search-space expansion.

Beyond these PostgreSQL-native indexing frameworks, broader research efforts have also investigated alternative index organizations outside the core GiST/SP-GiST family. In addition to tree-based indexing frameworks, prior studies have examined hybridized spatial index structures and distributed strip-based partitioning methods to improve the efficiency of moving-object and location-based query processing [28,29]. Hybrid indexing approaches combine spatial and motion characteristics to limit search-space expansion in moving-object databases, while distributed strip partitioning reduces imbalance and unnecessary region scans in spatial–textual retrieval. These techniques demonstrate that carefully optimized index organizations can significantly accelerate nearest-neighbor–based search, although such methods generally operate outside PostgreSQL’s native execution engine.

Recent PostgreSQL extensions, such as pgvector [30], have introduced high-dimensional vector search capabilities using Hierarchical Navigable Small World (HNSW) graphs and FAISS-like indexing structures. These enable efficient approximate k-NN search directly inside PostgreSQL but remain limited to static embeddings and cannot handle evolving spatio-temporal trajectories. Despite these advances, trajectory data pose additional temporal and geometric challenges that require specialized query processing mechanisms, as discussed in the following section.

2.2. Nearest Neighbor for Trajectories

Over the past two decades, trajectory-based k-nearest neighbor (k-NN) research has been organized according to whether the query and data objects are static or dynamic. This distinction determines the type of spatial index, search strategy, and pruning heuristics used for efficient query execution.

When both query and data objects are static, most solutions traverse an R-tree [31] with either depth-first search (DFS) or breadth-first search (BFS). The earliest algorithm employed branch-and-bound DFS on an R-tree and relied on two distance bounds, MINDIST and MINMAXDIST; a node is pruned as soon as the smaller bound exceeds the current k-th nearest distance [31]. Later work replaced the two-bound test with a sole MINDIST-based ordering, eliminating the need to compute MINMAXDIST [32]. Subsequent research further reduced distance calculations by partitioning the data space into four quadrants whose origin is the centroid of the minimum bounding rectangle (MBR) [33]. To avoid excessive node visits inherent to DFS, an incremental BFS scheme was proposed that orders nodes in a priority queue and reports neighbors in increasing distance [34]; its main drawback is the potential growth of the queue on large datasets.

When the query object is moving while the data objects are static, continuous-NN queries periodically sample the query trajectory and perform point NN searches at each sample to approximate the result [35]. Fewer samples improve speed but risk error; more samples have the opposite effect. An adaptive-cell approach indexes space with an R-tree-like structure to accelerate trajectory retrieval [36]. A generalized formulation, TCkNN (Trajectory-based continuous k-nearest neighbor), handles both static and moving queries by approximating each trajectory with a line segment, indexing the segments in an R-tree, and pruning subtrees whose minimum possible distance already exceeds the current k-th smallest distance [37]. During traversal, a nearest buffer is maintained; if its minimum bound surpasses the current k-th smallest distance, the corresponding subtree is skipped.

Beyond these paradigms, various specialized variants have appeared, including constrained NN [38], group NN [39], surface k-NN [40], reverse k-NN [41], and all-NN queries [42]. Although these variants enrich the semantics of nearest-neighbor search, they remain computationally intensive when applied to high-volume spatio-temporal trajectory data. These limitations motivate the need for database-integrated methods capable of executing trajectory k-NN queries efficiently within the DBMS engine.

Recently, deep-learning–based approaches have emerged to enhance trajectory representation and to capture semantic or behavioral similarity that goes beyond geometric proximity. Methods such as Trajectory Similarity Measurement [43], Activity Semantics Embedding [44], and graph-based contrastive learning frameworks [45] focus on identifying high-level resemblance in movement patterns rather than evaluating spatial closeness.

Whereas semantic similarity models assess trajectories based on their behavioral resemblance, distance-based k-NN queries quantify their geometric proximity in space. For this reason, our study concentrates on optimizing geometric k-NN computation in PostgreSQL via UDAs rather than addressing semantic similarity.

2.3. Trajectory Joins

Although trajectory joins have received relatively less attention than point-based nearest neighbor (NN) queries, a growing body of research has begun to address this problem [37]. Existing approaches fall into two broad groups: (1) methods that construct specialized index structures for moving-object trajectories [46], and (2) techniques that rely on general-purpose spatial indexes such as the Generalized Search Tree (GiST) [12] or its R-tree variants.

Specialized approaches typically evaluate every pair of trajectories drawn from two data sets and consider two trajectories comparable over an interval when all synchronized points lie within a distance threshold [46]. To reduce dimensionality, each trajectory is converted into a symbolic string, collapsing the original three-dimensional representation into a one-dimensional sequence. A lower-bounding distance metric is then used to prune the search space, and the resulting keys are stored in a compact B-tree index. While this symbolic encoding yields faster pairwise comparisons, it sacrifices geometric fidelity and is sensitive to noise and temporal misalignment between trajectories.

A complementary line of research focuses on the Closest Point of Approach (CPA) join [47] between two trajectories. The CPA join identifies every pair of trajectory segments whose minimum distance falls below a predefined threshold by mapping segments into $(d+1)$-dimensional space and indexing them with a packed R-tree [31]. It then evaluates segment–segment distances using three alternative strategies: (1) a packed R-tree join that reuses the standard R-tree join algorithm [31], (2) a plane-sweep algorithm along the temporal axis, and (3) an adaptive hybrid that dynamically selects the most efficient join method based on data characteristics. Although CPA joins improve efficiency for small datasets, their computational cost grows quadratically with trajectory count, limiting scalability for large spatio-temporal databases.

Collectively, these studies have laid the foundation for efficient trajectory-join processing but leave open critical challenges, particularly in balancing indexing overhead with query scalability. In contrast to these external approaches, our work natively integrates k-NN trajectory join operators into PostgreSQL through UDAs, eliminating the need for external computation frameworks and enabling fully in-database processing.

3. Preliminaries

This section introduces the foundational components of our in-database framework for trajectory k-nearest neighbor (k-NN) processing. We first describe the PostgreSQL storage model and trajectory representation used in our implementation, including the structure of moving-object tables and segment indexing. Next, we formally define the three types of k-NN queries supported in this study—spatial, geometry–trajectory, and trajectory–trajectory queries—along with the notation and assumptions used throughout the paper. These definitions establish the conceptual basis for the algorithmic design presented in Section 4.

3.1. PostgreSQL Storage Model and Type Extensibility

Modern database management systems (DBMSs) support user-defined extensions that allow developers to embed customized logic directly into the database kernel. Among these, PostgreSQL is particularly notable for its dynamic extensibility: new data types, operators, and indexing methods can be registered at runtime without recompilation [19]. This capability is crucial for our work, as it allows the creation of user-defined aggregates (UDAs) and trajectory types that can be seamlessly integrated into query execution.

Figure 1 illustrates the logical schema used by our in-database framework, which comprises a main Trip table and an associated segment table. The schema provides a unified relational structure for storing and querying trajectory data. In practice, real-world mobility datasets differ in how waypoints and trajectories are represented—ranging from raw GPS logs to pre-aggregated or sensor-derived paths. To handle such heterogeneity, users perform a lightweight pre-processing step that converts raw data into the normalized schema shown in Figure 1. This normalization not only ensures schema-level consistency across datasets but also enables uniform query processing using PostgreSQL’s extensible type system. Because new trajectory types (e.g., mpoint, mtraj) and their operators can be dynamically registered, the same schema supports future extensions without structural modification.

Definition 1.

Moving Object Table. Given a table M, it contains the following attributes:

$$M=(mpid,segid,mbr,timerange,trajectory)$$

(1)

Here, mpid is the trajectory identifier, segid denotes the segment identifier, mbr is the minimum bounding rectangle (MBR) of the segment, timerange specifies the temporal validity interval, and trajectory stores the geometric representation of the moving object.

Moving objects are stored in two related tables: a main object table and a segment table. The mpid column is added to the main table using the addmgeometrycolumn function, while the coordinates of each trajectory segment are stored in the segment table, which is indexed using a GiST index. Beyond structural uniformity, PostgreSQL’s extensible type system allows new trajectory types and associated operators to be reused across all UDA functions—such as distance computations and k-NN queries—without altering the schema, making the design inherently extensible.

   CREATE TABLE Trip(

     CarId integer primary key,

     TripId integer

   );

   SELECT addmgeometrycolumn(’Trip’,’mpid’,’mpoint’,50);

For example, the table Trip contains attributes CarId and TripId, with CarId serving as the primary key. The addmgeometrycolumn function adds a new column that stores trajectory data. This function takes four parameters: the table name, column name, geometry type, and an optional segmentation count. The default segmentation count is set to 50, meaning that each trajectory is divided into segments of 50 points. This threshold is empirically derived from PostgreSQL’s typical page size to ensure that long trajectories are stored efficiently within a page. Trajectory definitions are implemented using PL∖pgSQL, and several supporting functions are developed using PL∖JAVA.

3.2. Problem Statement

In the following, we consider trajectories in three-dimensional space for clarity, although the definitions can naturally be extended to higher dimensions.

Definition 2.

Trajectory. A trajectory is defined as an ordered sequence of moving points (mpoints):

$$Trajectory=(mpoin{t}_1,mpoin{t}_2,...,mpoin{t}_n)$$

(2)

where each moving point is represented as:

$$mpoint=(x,y,t)$$

(3)

Here, $(x,y)$ denotes spatial coordinates and t is the timestamp. The sequence is strictly ordered by time.

We define three types of k-NN queries on trajectory datasets. Given a query object O (a point, geometry, or trajectory) and a dataset T, a k-NN query aims to find the k closest trajectories to O.

Definition 3.

k-NN Spatial Query. Given a trajectory dataset T, a query point p, and a positive integer k, the function $m\_knn(T,p,k)$ returns the k nearest trajectories in T to the point p.

$$m\_knn(T,p,k)$$

(4)

Definition 4.

k-NN Geometry–Trajectory Join Query. Given a trajectory dataset T and a geometry dataset G, and a positive integer k, this query returns pairs of $(mpid,geometry,d)$ such that a trajectory from T is among the k nearest neighbors of an element in G, where d denotes the computed distance.

$$m\_knn(T,G,k)$$

(5)

Definition 5.

k-NN Trajectory Join Query. Given two trajectory datasets T₁ and T₂, and a positive integer k, this query returns pairs of $({mpid}_1,{mpid}_2,d)$ such that a trajectory from T₂ is among the k nearest neighbors of a trajectory from T₁, where d is the trajectory distance.

$$m\_knn(T_1,T_2,k)$$

(6)

These query types constitute the core analytical operations for trajectory-based k-NN search and establish the foundation for the algorithmic framework developed in the subsequent sections. The following section details how these query types are implemented within PostgreSQL using UDAs.

4. Methodology

Building on the definitions introduced in Section 3, this section describes the in-database methodology for executing trajectory k-nearest-neighbor (k-NN) queries using PostgreSQL user-defined aggregates (UDAs). Section 4.1 presents the design and SQL structure of the three k-NN query types, while Section 4.2 details the baseline UDA implementation that serves as the foundation for the optimized algorithms developed in Section 5.

4.1. k-NN Query Design

This subsection explains how the three types of trajectory k-NN queries—spatial, geometry–join, and trajectory–trajectory join—are formulated within PostgreSQL using a unified function interface.

Table 2. k-NN queries in PostGeoMedia vs. MobilityDB (examples with $k=5$).

Description	Our Query	MobilityDB
k-NN spatial query	SELECT m_knn(t.traj,’POINT (10 10)’,5) FROM Trip t	SELECT T.CarId, trajectory(T.Trip <—>’POINT (10 10)’) AS MinDistance FROM Trip T ORDER BY MinDistance LIMIT 5
k-NN geometry join query	SELECT m_knn(t.traj,p.geo,5) FROM Trip t, POI p	WITH TripsTraj AS (       SELECT *, Trajectory(Trip) AS Trajectory FROM Trip ) SELECT T.CarId, P1.PointId, P1.Distance FROM TripsTraj T CROSS JOIN LATERAL(       SELECT P.PointId, T.Trajectory <—>P.Geom AS Distance       FROM Points P       ORDER BY Distance LIMIT 5 ) AS P1 ORDER BY T.TripId, T.CarId, P1.Distance;
k-NN trajectory join query	SELECT m_knn(t1.traj,t2.traj,5) FROM Trip t1, Trip t2	SELECT T1.CarId AS CarId1, C2.CarId AS CarId2, C2.Distance FROM Trip T1 CROSS JOIN LATERAL(       SELECT T2.CarId, minValue(T1.Trip <—>T2.Trip) AS Distance       FROM Trip T2       WHERE T1.CarId < T2.CarId       ORDER BY Distance LIMIT 5 ) AS C2 ORDER BY T1.CarId, C2.CarId;

Note: The asterisk (*) in SQL denotes selecting all columns (i.e., SELECT ∗).

compares our implementation in PostGeoMedia with the corresponding SQL expressions in MobilityDB. All examples are illustrated with $k=5$ for consistency across figures and query examples.

PostGeoMedia introduces a unified function m_knn() that accepts three arguments: (1) the trajectory column, (2) the query object (a point, geometry, or trajectory), and (3) the integer k. Internally, m_knn() invokes an aggregate function that computes and returns the exact k nearest neighbors from the Trip table. By contrast, MobilityDB requires nested subqueries or common-table expressions to achieve equivalent functionality.

• Query 1 (Spatial): For every trajectory in Trip, return the five trajectories closest to the point POINT(10 10):

    SELECT m_knn(t.traj, ’POINT (10 10)’, 5)

    FROM Trip t;

• Query 2 (Geometry Join): For every trajectory in Trip, return the five points in POI that are nearest to that trajectory:

    SELECT m_knn(t.traj, p.geo, 5)

    FROM Trip t, POI p;

• Query 3 (Trajectory Join): For every trajectory in Trip, return the five other trajectories that are closest to it:

    SELECT m_knn(t1.traj, t2.traj, 5)

    FROM Trip t1, Trip t2;

The unified formulation simplifies the expression of all three query types within PostgreSQL and enables seamless integration with the UDA framework described below.

4.2. Naive UDA Implementation

A UDA in PostgreSQL consists of two primary routines: Accumulate (state-transition function) and Terminate (final function). They are registered through the CREATE AGGREGATE command using the clauses SFUNC, STYPE, INITCOND, and optionally FINALFUNC [14,19].

The naive k-NN implementation defines a bounded priority queue (kPQ) as the internal state and updates it incrementally for each input row (Figure 2).

Definition 6.

k-Priority Queue (kPQ). A bounded priority queue that stores at most k ordered pairs:

$$\mathit{kPQ}=[\langle mpi{d}_1,d_1\rangle ,\dots ,\langle mpi{d}_k,d_k\rangle ],$$

(7)

sorted in descending order of distance so that the head always contains the largest $d_i$.

The aggregation relies solely on the Accumulate routine. For each input row, the corresponding trajectory is retrieved, its distance to the query geometry is computed, and the pair $\langle mpid,d\rangle$ is conditionally retained in the bounded queue. This baseline implementation provides the conceptual foundation for the optimized deferred and materialized algorithms described in Section 5.

5. Efficient k-NN Algorithms

This section introduces two optimized UDA implementations—deferred and materialized—that build upon the baseline method in Section 4. The deferred approach minimizes per-row overhead by postponing distance computation, while the materialized approach reduces search space through selective filtering and reuse of intermediate results.

5.1. Deferred k-NN Approach

The deferred approach postpones distance computations until the Terminate phase of the aggregation. Unlike the naive method (Algorithm 1), which calculates distances for every row during Accumulate, the deferred implementation stores only trajectory identifiers and performs the actual geometric computations once at the end. This reduces repeated function calls and I/O cost, improving performance particularly for large datasets.

Algorithm 2 outputs a k-priority queue containing the nearest trajectories. Because distance evaluation is deferred to the end, the total number of geometric operations equals the number of distinct trajectories processed, not the number of table rows, yielding a measurable reduction in runtime.

Algorithm 1 Naive k-NN (kPQ, mpid, geo, k)

1: Accumulate(kPQ, mpid, geo, k) 2: if kPQ = nil then 3:      kPQ ← createKPQ(k) 4: end if 5: $t\leftarrow$getTrajectory(mpid) 6: $d\leftarrow$calculateDistance(t, geo) 7: if kPQ.size() < k or $d<\mathit{kPQ}.\mathit{peek}\left(\right).\mathit{d}$ then 8:      kPQ.offer($\langle \mathit{mpid},d\rangle$) 9:      if kPQ.size() > k then 10:          kPQ.poll() 11:     end if 12: end if

Algorithm 2 Deferred k-NN $\left(\mathit{Q},\mathit{mpid},\mathit{geo},\mathit{k}\right)$

1: Accumulate(Q, mpid) 2:         Q.add(mpid)   3: Terminate(Q, geo, k) 4:        $kPQ\leftarrow$createKPQ(k) 5: for all mpid ∈ Qdo 6:        $t\leftarrow$ getTrajectory(mpid) 7:        $d\leftarrow$ calculateDistance(t, geo) 8:        if kPQ.size() < k or $d<$ kPQ.peek().d then 9:              kPQ.offer($\langle \mathit{mpid},d\rangle$) 10:            if kPQ.size() > k then 11:                  kPQ.poll() 12:            end if 13:       end if 14: end for 15: return kPQ

5.2. Materialized k-NN Approach

The materialized approach further improves efficiency by pre-filtering candidate trajectories using minimum-bounding rectangles (MBRs) and reusing partial results across queries. PostgreSQL’s current_query() [19] function is leveraged to detect WHERE clauses dynamically; when present, a temporary materialized view $\mathcal{M}$ is created to include only trajectories whose MBRs intersect the query region.

Algorithm 3 presents the materialized k-NN algorithm, which incorporates spatial filtering and iterative expansion to construct the candidate set.

The overall workflow of the materialized k-NN approach is summarized in Figure 3. The figure illustrates how the aggregate functions (M_KNN_SFUNC and M_KNN_FFUNC) cooperate to build and reuse a temporary materialized view. When the query contains a WHERE clause, the system first defines an initial radius $\alpha$ to generate candidate trajectories whose MBRs intersect the query region. If the number of candidates is fewer than k, $\alpha$ is iteratively expanded until at least k trajectories are materialized. The final view is then passed to the aggregation function, which computes the k nearest trajectories and returns the results.

Definition 7.

Materialized View ($\mathcal{M}$). A temporary relation storing the identifiers (mpid) of trajectories preselected as k-NN candidates after spatial filtering.

• Relational-Algebra Analysis. Figure 4, Figure 5 and Figure 6 illustrate the relational-algebra execution plans for spatial, geometry–trajectory, and trajectory–trajectory k-NN queries. Each figure compares the MobilityDB baseline, the naive UDA, and the materialized approach.

Algorithm 3 Materialized k-NN (spatial)

1: Accumulate(kPQ, mpid, geo, k) 2: if first_call then 3:       q ← current_query() 4:       if is_cost_materialized(q) then 5:             $M\leftarrow$ getMoTable(mpid) 6:             $\mathrm{MBR}\leftarrow$ create_mbr(M, geo) 7:             $\mathcal{M}\leftarrow$ create_materialized(M, $\mathrm{MBR}$) 8:             while $\left|\mathcal{M}\right|<k$ do 9:                   $\mathrm{MBR}\leftarrow$ update_mbr($\mathrm{MBR}$) 10:                  $\mathcal{M}\leftarrow$ update_materialized($\mathcal{M}$, $\mathrm{MBR}$) 11:             end while 12:       else 13:             $\mathcal{M}\leftarrow$ all_trajectories() 14:       end if 15: end if 16: for mpid $\in \mathcal{M}$ do 17:       $t\leftarrow$ getTrajectory(mpid) 18:       $d\leftarrow$ calculateDistance(t, geo) 19:       if kPQ.size() < k or $d<$ kPQ.peek().d then 20:             kPQ.offer$(\langle \mathit{mpid},d\rangle )$ 21:             if kPQ.size() > k then 22:                   kPQ.poll() 23:             end if 24:       end if 25: end for 26: return kPQ

In the MobilityDB plan (Figure 4a, Figure 5a and Figure 6a), each query performs a full scan followed by a distance sort and LIMIT k. The naive UDA (Figure 4b, Figure 5b and Figure 6b) eliminates explicit sorting by maintaining a bounded queue during aggregation. Finally, the materialized approach (Figure 4c, Figure 5c and Figure 6c) adds an MBR-based selection step before aggregation, significantly reducing input cardinality and computational cost.

• Cost Model: The overall cost of each approach can be approximated using the following expressions, where T denotes the number of tuples scanned, N the number of pages accessed during the table scan, and $s_{\mathrm{MBR}}$ the selectivity factor of the spatial filter (i.e., the fraction of tuples whose MBRs intersect the query window). Each constant represents a specific component of the query execution cost: $c_{\mathrm{seq}}$ denotes the I/O cost per sequential page read, $c_{\mathrm{dist}}$ the CPU cost of computing one trajectory–geometry distance, $c_{\mathrm{kpq}}$ the cost of maintaining the k-priority queue, $c_{\mathrm{acc}}$ the per-row accumulator overhead in the UDA, and $c_{\mathrm{MBR}}$ the cost of testing intersection between two MBRs [48,49].

Naive:

$${\mathrm{Cost}}_{\mathrm{N}}=N·c_{\mathrm{seq}}+T·(c_{\mathrm{dist}}+c_{\mathrm{kpq}}+c_{\mathrm{acc}}).$$

(8)

The naive algorithm performs a sequential scan of the entire trajectory relation. For each tuple, the trajectory distance is computed and the k-priority queue is updated, leading to a total cost that grows linearly with both N and T.

Deferred:

$${\mathrm{Cost}}_{\mathrm{D}}=N·c_{\mathrm{seq}}+T·(c_{\mathrm{dist}}+c_{\mathrm{kpq}}+c_{\mathrm{acc}}^{\downarrow }),c_{\mathrm{acc}}^{\downarrow }<c_{\mathrm{acc}}.$$

(9)

In the deferred algorithm, distance computations are postponed until the Terminate phase, so the per-tuple accumulation overhead $c_{\mathrm{acc}}^{\downarrow }$ is reduced compared with the naive case. The total number of distance evaluations remains proportional to T, but fewer function calls are invoked during aggregation, yielding a smaller constant factor.

Materialized:

$${\mathrm{Cost}}_{\mathrm{M}}=\underset{\mathrm{index}\mathrm{traversal}\mathrm{cost}}{\underbrace{\left({\displaystyle \frac{1}{m-1}}\right)(1-m^{1-h})·m·c_{\mathrm{MBR}}}}+N^{\prime }·c_{\mathrm{seq}}+(s_{\mathrm{MBR}}·T)·(c_{\mathrm{dist}}+c_{\mathrm{kpq}}+c_{\mathrm{acc}}).$$

(10)

where m and h denote the fan-out and height of the R-tree index, respectively, and $N^{\prime }$ represents the number of data pages accessed after filtering through the index. The first term estimates the CPU cost of traversing the R-tree to locate candidate entries, while the second and third terms correspond to the sequential scan and computation on the reduced set of $s_{\mathrm{MBR}}·T$ tuples. Because only a subset of trajectories passes the MBR filter, the total runtime decreases approximately in proportion to the selectivity factor $s_{\mathrm{MBR}}$ [31].

The above equations provide a heuristic cost model that highlights the relative computational complexity of each UDA strategy rather than exact planner-level estimates. These formulations serve as the analytical foundation for assessing runtime efficiency in the subsequent performance evaluation.

6. Experiments

In this section, we conducted experiments to assess the performance of the proposed algorithms and compare them to MobilityDB. The experimental evaluations discussed in this section are based on the k-NN query types summarized in Table 2. While Table 2 uses $k=5$ for illustrative purposes, our experiments are conducted with $k=3$. Each figure corresponds to one of these query patterns, enabling a comparative analysis of latency and efficiency. We evaluate our approach using the following four representative k-NN query types: (1) Retrieving the three trajectories closest to a given point for each trajectory in the dataset. (2) Retrieving the three trajectories closest to a given area for each trajectory. (3) Retrieving the three nearest POI points for each trajectory in Trip. (4) Retrieving the three closest trajectories to each trajectory in Trip.

6.1. Experimental Setup

A synthetic benchmark generated four trajectory datasets centered on Berlin by means of BerlinMOD [50].

Table 3. Details of the BerlinMOD datasets.

Scale Factor	Days	Vehicles	Trajectories	Unit (GB)
0.005	2	141	1.8 K	0.026 GB
0.05	6	447	15 K	0.26 GB
0.2	13	894	62.5 K	0.99 GB
1.0	28	2000	292.9 K	4.9 GB

reports the main statistics of each dataset: overall scale, temporal extent (Days), number of distinct vehicles, number of trajectories, and spatial unit. The Days attribute enables temporal predicates, whereas the Vehicles attribute is relevant to aggregation and range predicates. Three k-NN implementations were evaluated—naive, deferred, and materialized—and their runtimes were compared with MobilityDB’s native k-NN queries. All implementations were written in PL∖pgSQL and executed on CentOS 7 running on an Intel Core i9-10900 (2.80 GHz, 10 cores) with 16 GB of RAM.

6.2. k-NN Spatial Query

The first experiment examined Spatial with $k=3$. Datasets were scaled from $0.005$ to $1.0$ of the original BerlinMOD size. Figure 7 reports the execution time required to retrieve the three trajectories nearest to a fixed point. Execution time grows approximately linearly for all three proposed algorithms, with the materialized variant outperforming the naive and deferred variants across the entire range of scale factors. MobilityDB exhibits consistently higher runtimes than the proposed algorithms except at scale factor $0.2$; at the largest scale ($1.0$) the disparity widens markedly.

A second experiment assessed execution time when the query object is a large geometry (e.g., linestring or ring). As the geometric object grows, execution time increases for every method, mirroring the trend observed in the first experiment. Figure 8 shows that the materialized algorithm again achieves the lowest runtime for all scale factors except 0.2.

The slowdown observed for the proposed methods at scale factor $0.2$ stems from the additional range-search cost incurred when the data volume first becomes substantial; beyond this point, the materialized filter compensates for the larger input.

6.3. k-NN Geometry–Join Query

The third experiment assessed Geometry Join with $k=3$, a k-NN join between the trajectory table and the point-of-interest (POI) table; temporal information was ignored. As Figure 9 indicates, execution time increases almost linearly with data scale for all three algorithms. The materialized variant consistently delivers the shortest runtimes because the precomputed materialized view limits the number of distance computations. MobilityDB is included for reference and is outperformed by the materialized algorithm over the entire range of scale factors.

6.4. k-NN Trajectory Join Query

The final experiment focused on Trajectory Join with $k=3$, a k-NN join between two trajectory tables, again ignoring the temporal dimension. Figure 10 compares execution times. The absolute differences between methods remain nearly constant across scale factors. The materialized algorithm offers a slight advantage over MobilityDB, underscoring the benefit of pre-filtering candidate pairs—even for the more demanding trajectory–trajectory join scenario, which remains relatively unexplored in spatio-temporal database research.

7. Discussion

The experimental results demonstrate that the proposed UDA-based operators consistently outperform MobilityDB across all workloads, although the extent of improvement varies by query type. The runtime reduction is substantial for spatial point queries (Figure 7 and Figure 8), remains notable for geometry–trajectory joins (Figure 9), and is moderate but steady for trajectory–trajectory joins (Figure 10). These patterns indicate that early aggregation and candidate reduction are particularly effective when intermediate join relations would otherwise become large.

The performance gains can be attributed to two complementary design choices. First, the User-Defined Aggregate (UDA) allows partial results to be accumulated during query execution, mitigating the overhead of materializing large intermediate relations commonly produced by traditional join plans. Second, the pre-filtered materialized view restricts the set of candidate pairs before the final distance evaluation, reducing redundant comparisons and improving stability as data size increases. Together, these design elements address a key limitation identified in the Introduction: the lack of native, in-database mechanisms for efficient trajectory-based k-NN processing in PostgreSQL and MobilityDB.

Beyond raw performance, the results suggest broader implications for spatial query processing. Integrating analytical operators directly into the database engine narrows the gap between query logic and application-level computation, thereby reducing the need for external processing frameworks. This contrasts with existing spatio-temporal systems such as MobilityDB and PostGIS, which frequently depend on specialized data types, auxiliary indexing structures, or coupling to external modules. By maintaining strong SQL compatibility and minimizing architectural dependencies, the proposed approach enhances transparency, maintainability, and practical adoption in production environments.

Despite these advantages, several limitations remain. The scalability of the materialized view depends on the efficiency of index traversal, and performance may degrade when evaluating extremely large or highly skewed trajectory datasets. Additionally, the pruning effectiveness of the k-bounded priority queue is influenced by the spatial distribution and segmentation characteristics of trajectories. These factors suggest opportunities for further refinement.

Limitations

A further limitation of this study is that all experiments were conducted with a single k value ($k=3$). While this setting is sufficient to highlight the relative performance differences among the proposed algorithms and the baseline system, relying on a single parameter configuration restricts the empirical breadth of the evaluation. Since the computational behavior of k-NN processing may vary under different values of k and under different data characteristics, a more exhaustive evaluation—including sensitivity tests across multiple k settings—would provide a more comprehensive validation of the proposed approach. Expanding the experiments to incorporate a wider range of parameter configurations is an important direction for future work.

Overall, the findings highlight the potential of UDA-driven aggregation combined with pre-filtered materialization to enhance in-database trajectory k-NN processing. These observations motivate further exploration of scalable implementations and broader experimental conditions, which we address in the following conclusion.

8. Conclusions

This paper presented a new in-database algorithm for k-nearest-neighbor search on static trajectory datasets. By combining a User-Defined Aggregate (UDA) with a pre-filtered materialized view, the proposed framework performs k-NN trajectory joins without relying on post hoc LIMIT filtering and consistently achieves lower runtimes than MobilityDB across all evaluated workloads. These results demonstrate that early aggregation and candidate reduction can substantially enhance the efficiency of trajectory-based spatial analysis within PostgreSQL.

The contributions of this work extend beyond performance improvement. From a systems perspective, the proposed operators illustrate how analytical functionality can be embedded natively within a relational database engine while maintaining full SQL compatibility. This design reduces dependence on external modules and offers a portable solution for practitioners who require efficient trajectory querying in production environments. For researchers, the findings highlight the potential of UDA-driven optimization as a general strategy for accelerating complex spatio-temporal queries.

Future work will focus on further increasing scalability and generality. Planned extensions include incorporating index-aware pruning techniques, implementing core distance computations in PL\C for lower-level performance optimization, and exploring adaptive caching to improve robustness under large or dynamically evolving datasets. We also aim to develop cost-based query planning strategies to broaden the applicability of the proposed framework to a wider range of spatial and temporal workloads. Together, these directions will strengthen the foundation for efficient, database-native trajectory analytics.