{"id":1314,"date":"2026-10-22T07:19:26","date_gmt":"2026-10-22T07:19:26","guid":{"rendered":"https:\/\/helpwithmygisassignment.com\/blog\/?p=1314"},"modified":"2026-10-22T07:19:26","modified_gmt":"2026-10-22T07:19:26","slug":"spatial-window-functions-recursive-ctes-postgis","status":"publish","type":"post","link":"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/","title":{"rendered":"Spatial Window Functions and Recursive CTEs in PostGIS"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#Spatial_Window_Functions_and_Recursive_CTEs_in_PostGIS\" >Spatial Window Functions and Recursive CTEs in PostGIS<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#Introduction\" >Introduction<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#1_Spatial_Window_Functions_Recursive_CTEs_PostGIS_for_Movement_Trajectory_Analysis\" >1. Spatial Window Functions Recursive CTEs PostGIS for Movement &amp; Trajectory Analysis<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#11_Understanding_the_spatial_window\" >1.1 Understanding the spatial window<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#12_Calculating_step-by-step_distance\" >1.2 Calculating step-by-step distance<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#13_Using_geography_for_geodesic_distance\" >1.3 Using geography for geodesic distance<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#2_Calculating_Movement_Speed\" >2. Calculating Movement Speed<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#3_Calculating_Heading_Angles\" >3. Calculating Heading Angles<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#4_Comparing_Consecutive_Headings\" >4. Comparing Consecutive Headings<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#5_Using_LEAD_for_Forward-Looking_Analysis\" >5. Using LEAD() for Forward-Looking Analysis<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#6_Creating_Trajectory_Segments\" >6. Creating Trajectory Segments<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#7_Recursive_CTEs_for_Hierarchical_Spatial_Boundaries\" >7. Recursive CTEs for Hierarchical Spatial Boundaries<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#8_Basic_Recursive_CTE\" >8. Basic Recursive CTE<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-14\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#9_Building_a_Hierarchical_Spatial_Path\" >9. Building a Hierarchical Spatial Path<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-15\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#10_Recursive_Spatial_Processing\" >10. Recursive Spatial Processing<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-16\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#11_Example_Nested_Watersheds\" >11. Example: Nested Watersheds<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-17\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#12_Combining_Recursive_CTEs_With_Spatial_Aggregation\" >12. Combining Recursive CTEs With Spatial Aggregation<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-18\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#13_Hierarchical_Spatial_Queries_With_Containment\" >13. Hierarchical Spatial Queries With Containment<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-19\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#14_Combining_Window_Functions_and_Recursive_CTEs\" >14. Combining Window Functions and Recursive CTEs<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-20\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#15_Performance_Considerations\" >15. Performance Considerations<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-21\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#151_Spatial_indexes\" >15.1 Spatial indexes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-22\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#152_Index_the_recursive_relationship\" >15.2 Index the recursive relationship<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-23\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#16_Indexing_Window-Function_Ordering\" >16. Indexing Window-Function Ordering<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-24\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#17_Avoid_Repeated_Coordinate_Transformations\" >17. Avoid Repeated Coordinate Transformations<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-25\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#18_Be_Careful_With_CTE_Materialization\" >18. Be Careful With CTE Materialization<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-26\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#19_Partitioning_Window_Queries_Correctly\" >19. Partitioning Window Queries Correctly<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-27\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#20_Validate_Temporal_Ordering\" >20. Validate Temporal Ordering<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-28\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#21_Recursive_CTE_Safety\" >21. Recursive CTE Safety<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-29\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/#Summary\" >Summary<\/a><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"Spatial_Window_Functions_and_Recursive_CTEs_in_PostGIS\"><\/span>Spatial Window Functions and Recursive CTEs in PostGIS<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<h2><span class=\"ez-toc-section\" id=\"Introduction\"><\/span>Introduction<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Complex spatial analysis in PostgreSQL rarely requires abandoning SQL for procedural code. By combining PostgreSQL&#8217;s analytical SQL features with<a href=\"https:\/\/helpwithmygisassignment.com\/blog\/building-real-time-spatial-dashboards-in-qgis-with-geonode-and-postgis\/\"> PostGIS<\/a> geometry functions, it is possible to build sophisticated workflows for trajectory analysis, spatial hierarchies, network-like traversals, and sequential geographic processes entirely inside the database.<\/p>\n<p>Two SQL capabilities are particularly powerful:<\/p>\n<ul>\n<li><strong>Window functions<\/strong> such as <code dir=\"ltr\">LAG()<\/code>, <code dir=\"ltr\">LEAD()<\/code>, <code dir=\"ltr\">ROW_NUMBER()<\/code>, and <code dir=\"ltr\">SUM() OVER()<\/code> for analyzing ordered spatial observations.<\/li>\n<li><strong>Common Table Expressions (CTEs)<\/strong>, including recursive CTEs, for breaking complicated spatial workflows into logical stages and traversing parent-child relationships.<\/li>\n<\/ul>\n<p>For example, a GPS trajectory can be represented as an ordered sequence of points:<\/p>\n<div>\n<pre dir=\"ltr\"><code>P1 \u2192 P2 \u2192 P3 \u2192 P4 \u2192 P5<\/code><\/pre>\n<\/div>\n<p>A window query can compare each point with the preceding or following point without performing an explicit self-join.<a href=\"https:\/\/www.postgresql.org\/\" target=\"_blank\" rel=\"noopener\"> PostGIS<\/a> then calculates the spatial relationship between those points:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Distance(previous_point, current_point)<\/code><\/pre>\n<\/div>\n<p>Similarly, administrative boundaries or nested watersheds can be represented as hierarchical relationships:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Country\r\n\u2514\u2500\u2500 Region\r\n    \u2514\u2500\u2500 District\r\n        \u2514\u2500\u2500 Subdivision<\/code><\/pre>\n<\/div>\n<p>A recursive CTE can traverse this hierarchy until the desired spatial level is reached.<\/p>\n<p>The combination is especially useful because <strong>PostGIS handles spatial computation while PostgreSQL handles relational ordering, partitioning, and recursion<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1462\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_da8anda8anda8and-1024x572.jpg\" alt=\"Spatial Window Functions Recursive CTEs PostGIS workflow diagram illustrating database spatial analysis\" width=\"1024\" height=\"572\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_da8anda8anda8and-1024x572.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_da8anda8anda8and-300x167.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_da8anda8anda8and-768x429.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_da8anda8anda8and.jpg 1376w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"1_Spatial_Window_Functions_Recursive_CTEs_PostGIS_for_Movement_Trajectory_Analysis\"><\/span>1. Spatial Window Functions Recursive CTEs PostGIS for Movement &amp; Trajectory Analysis<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<h2><span class=\"ez-toc-section\" id=\"11_Understanding_the_spatial_window\"><\/span>1.1 Understanding the spatial window<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A normal SQL query might calculate the distance between two known geometries:<\/p>\n<div>\n<pre dir=\"ltr\"><code>SELECT\r\n    ST_Distance(a.geom, b.geom)\r\nFROM points a\r\nJOIN points b\r\n    ON a.id = b.id;<\/code><\/pre>\n<\/div>\n<p>The difficulty appears when the relationship is sequential.<\/p>\n<p>Suppose a GPS table contains:<\/p>\n<div>\n<pre dir=\"ltr\"><code>track_id | timestamp | geom\r\n---------+-----------+----------------\r\n101      | 08:00     | Point 1\r\n101      | 08:01     | Point 2\r\n101      | 08:02     | Point 3\r\n101      | 08:03     | Point 4<\/code><\/pre>\n<\/div>\n<p>We want:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Point 1 \u2192 Point 2\r\nPoint 2 \u2192 Point 3\r\nPoint 3 \u2192 Point 4<\/code><\/pre>\n<\/div>\n<p>Rather than repeatedly joining the table against itself, <code dir=\"ltr\">LAG()<\/code> can expose the previous geometry.<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(geom) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>This produces a virtual previous-point column for every observation.<\/p>\n<hr \/>\n<h2><span class=\"ez-toc-section\" id=\"12_Calculating_step-by-step_distance\"><\/span>1.2 Calculating step-by-step distance<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Assume a table called <code dir=\"ltr\">gps_points<\/code>:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE TABLE gps_points (\r\n    id          BIGSERIAL PRIMARY KEY,\r\n    track_id    INTEGER NOT NULL,\r\n    recorded_at TIMESTAMPTZ NOT NULL,\r\n    geom        geometry(Point, 4326) NOT NULL\r\n);<\/code><\/pre>\n<\/div>\n<p>The first analytical step is retrieving the previous point.<\/p>\n<div>\n<pre dir=\"ltr\"><code>SELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n    geom,\r\n\r\n    LAG(geom) OVER (\r\n        PARTITION BY track_id\r\n        ORDER BY recorded_at\r\n    ) AS previous_geom\r\n\r\nFROM gps_points;<\/code><\/pre>\n<\/div>\n<p>The result conceptually becomes:<\/p>\n<div>\n<div>\n<div>\n<table>\n<thead>\n<tr>\n<th>Point<\/th>\n<th>Previous Point<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>P1<\/td>\n<td>NULL<\/td>\n<\/tr>\n<tr>\n<td>P2<\/td>\n<td>P1<\/td>\n<\/tr>\n<tr>\n<td>P3<\/td>\n<td>P2<\/td>\n<\/tr>\n<tr>\n<td>P4<\/td>\n<td>P3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<p>We can then calculate the distance.<\/p>\n<p>For geographic coordinates, <code dir=\"ltr\">ST_Distance<\/code> should generally be used carefully because geometry coordinates in EPSG:4326 are expressed in degrees.<\/p>\n<p>One robust approach is transforming the points into an appropriate projected coordinate system.<\/p>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom\r\n\r\n    FROM gps_points\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n\r\n    ST_Distance(\r\n        ST_Transform(previous_geom, 32737),\r\n        ST_Transform(geom, 32737)\r\n    ) AS step_distance_m\r\n\r\nFROM ordered_points\r\nWHERE previous_geom IS NOT NULL;<\/code><\/pre>\n<\/div>\n<p>Here, <code dir=\"ltr\">32737<\/code> represents WGS 84 \/ UTM zone 37S, which is appropriate for much of Kenya.<\/p>\n<p>For a production system covering a large geographic region, the projected CRS should instead be selected according to the actual spatial extent of the data.<\/p>\n<hr \/>\n<h2><span class=\"ez-toc-section\" id=\"13_Using_geography_for_geodesic_distance\"><\/span>1.3 Using geography for geodesic distance<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Another option is to cast geometries to <code dir=\"ltr\">geography<\/code>.<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Distance(\r\n    previous_geom::geography,\r\n    geom::geography\r\n)<\/code><\/pre>\n<\/div>\n<p>This calculates distance on the Earth&#8217;s surface in meters.<\/p>\n<p>A complete query becomes:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom\r\n\r\n    FROM gps_points\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n\r\n    ST_Distance(\r\n        previous_geom::geography,\r\n        geom::geography\r\n    ) AS step_distance_m\r\n\r\nFROM ordered_points\r\nWHERE previous_geom IS NOT NULL;<\/code><\/pre>\n<\/div>\n<p>This approach is convenient for GPS data stored in WGS 84.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"2_Calculating_Movement_Speed\"><\/span>2. Calculating Movement Speed<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Once step distance has been calculated, velocity can be derived from the elapsed time between observations.<\/p>\n<p>Use another <code dir=\"ltr\">LAG()<\/code> expression:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(recorded_at) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>A production-oriented query can calculate both distance and elapsed time.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom,\r\n\r\n        LAG(recorded_at) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_time\r\n\r\n    FROM gps_points\r\n),\r\n\r\nmovement AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n\r\n        ST_Distance(\r\n            previous_geom::geography,\r\n            geom::geography\r\n        ) AS distance_m,\r\n\r\n        EXTRACT(\r\n            EPOCH FROM (\r\n                recorded_at - previous_time\r\n            )\r\n        ) AS elapsed_seconds\r\n\r\n    FROM ordered_points\r\n    WHERE previous_geom IS NOT NULL\r\n      AND previous_time IS NOT NULL\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n    distance_m,\r\n    elapsed_seconds,\r\n\r\n    CASE\r\n        WHEN elapsed_seconds &gt; 0\r\n        THEN distance_m \/ elapsed_seconds\r\n        ELSE NULL\r\n    END AS speed_mps,\r\n\r\n    CASE\r\n        WHEN elapsed_seconds &gt; 0\r\n        THEN (distance_m \/ elapsed_seconds) * 3.6\r\n        ELSE NULL\r\n    END AS speed_kmh\r\n\r\nFROM movement;<\/code><\/pre>\n<\/div>\n<p>The important design principle is to <strong>separate the workflow into CTE stages<\/strong>:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Raw GPS points\r\n      \u2193\r\nOrdered points\r\n      \u2193\r\nPrevious point\/time\r\n      \u2193\r\nDistance + elapsed time\r\n      \u2193\r\nSpeed<\/code><\/pre>\n<\/div>\n<p>This makes the SQL much easier to test and extend.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"3_Calculating_Heading_Angles\"><\/span>3. Calculating Heading Angles<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Distance alone does not describe movement direction.<\/p>\n<p>For trajectory analysis, we may also need the heading or bearing between consecutive GPS points.<\/p>\n<p>PostGIS provides:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Azimuth()<\/code><\/pre>\n<\/div>\n<p>For geometry coordinates, the function returns the angle in radians measured clockwise from north.<\/p>\n<p>We can convert the result to degrees:<\/p>\n<div>\n<pre dir=\"ltr\"><code>DEGREES(\r\n    ST_Azimuth(previous_geom, geom)\r\n)<\/code><\/pre>\n<\/div>\n<p>A complete trajectory query is:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom\r\n\r\n    FROM gps_points\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n\r\n    ST_Distance(\r\n        previous_geom::geography,\r\n        geom::geography\r\n    ) AS distance_m,\r\n\r\n    CASE\r\n        WHEN previous_geom IS NOT NULL\r\n        THEN DEGREES(\r\n            ST_Azimuth(\r\n                previous_geom::geography,\r\n                geom::geography\r\n            )\r\n        )\r\n        ELSE NULL\r\n    END AS heading_degrees\r\n\r\nFROM ordered_points;<\/code><\/pre>\n<\/div>\n<p>A heading of approximately:<\/p>\n<div>\n<pre dir=\"ltr\"><code>0\u00b0   = North\r\n90\u00b0  = East\r\n180\u00b0 = South\r\n270\u00b0 = West<\/code><\/pre>\n<\/div>\n<p>The result can therefore support analyses such as:<\/p>\n<ul>\n<li>vehicle movement direction,<\/li>\n<li>pedestrian trajectories,<\/li>\n<li>animal tracking,<\/li>\n<li>maritime movement,<\/li>\n<li>delivery routes,<\/li>\n<li>mobility studies.<\/li>\n<\/ul>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"4_Comparing_Consecutive_Headings\"><\/span>4. Comparing Consecutive Headings<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Window functions become even more useful when several sequential calculations are combined.<\/p>\n<p>Suppose we want to detect sharp turns.<\/p>\n<p>First calculate each segment&#8217;s heading:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom\r\n\r\n    FROM gps_points\r\n),\r\n\r\nheadings AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n\r\n        DEGREES(\r\n            ST_Azimuth(\r\n                previous_geom::geography,\r\n                geom::geography\r\n            )\r\n        ) AS heading\r\n\r\n    FROM ordered_points\r\n    WHERE previous_geom IS NOT NULL\r\n)\r\n\r\nSELECT\r\n    *,\r\n    LAG(heading) OVER (\r\n        PARTITION BY track_id\r\n        ORDER BY recorded_at\r\n    ) AS previous_heading\r\n\r\nFROM headings;<\/code><\/pre>\n<\/div>\n<p>A subsequent calculation can determine heading change.<\/p>\n<p>However, angles wrap around at 360\u00b0.<\/p>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Previous = 350\u00b0\r\nCurrent  = 10\u00b0<\/code><\/pre>\n<\/div>\n<p>The na\u00efve difference is:<\/p>\n<div>\n<pre dir=\"ltr\"><code>10 - 350 = -340\u00b0<\/code><\/pre>\n<\/div>\n<p>But the actual turn is only:<\/p>\n<div>\n<pre dir=\"ltr\"><code>+20\u00b0<\/code><\/pre>\n<\/div>\n<p>A normalized angular difference can be calculated as:<\/p>\n<div>\n<pre dir=\"ltr\"><code>((current_heading - previous_heading + 540) % 360) - 180<\/code><\/pre>\n<\/div>\n<p>This produces a range from:<\/p>\n<div>\n<pre dir=\"ltr\"><code>-180\u00b0 to +180\u00b0<\/code><\/pre>\n<\/div>\n<p>A trajectory system can then classify large angular changes as potential turns.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"5_Using_LEAD_for_Forward-Looking_Analysis\"><\/span>5. Using LEAD() for Forward-Looking Analysis<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p><code dir=\"ltr\">LAG()<\/code> looks backward.<\/p>\n<p><code dir=\"ltr\">LEAD()<\/code> looks forward.<\/p>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LEAD(geom) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>can identify the next GPS point.<\/p>\n<p>This is useful when the analytical question is:<\/p>\n<blockquote><p>What happens after this observation?<\/p><\/blockquote>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>SELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n    geom,\r\n\r\n    LEAD(geom) OVER (\r\n        PARTITION BY track_id\r\n        ORDER BY recorded_at\r\n    ) AS next_geom\r\n\r\nFROM gps_points;<\/code><\/pre>\n<\/div>\n<p>You can then calculate the distance from the current point to the next point.<\/p>\n<p>This is particularly useful for generating segment-level representations:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Point 1 \u2500\u2500\u2500\u2500\u2500 Point 2\r\nPoint 2 \u2500\u2500\u2500\u2500\u2500 Point 3\r\nPoint 3 \u2500\u2500\u2500\u2500\u2500 Point 4<\/code><\/pre>\n<\/div>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"6_Creating_Trajectory_Segments\"><\/span>6. Creating Trajectory Segments<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>A useful production pattern is converting consecutive GPS points into line segments.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS (\r\n    SELECT\r\n        id,\r\n        track_id,\r\n        recorded_at,\r\n        geom,\r\n\r\n        LEAD(geom) OVER (\r\n            PARTITION BY track_id\r\n            ORDER BY recorded_at\r\n        ) AS next_geom\r\n\r\n    FROM gps_points\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    recorded_at,\r\n\r\n    ST_MakeLine(\r\n        geom,\r\n        next_geom\r\n    ) AS segment_geom\r\n\r\nFROM ordered_points\r\n\r\nWHERE next_geom IS NOT NULL;<\/code><\/pre>\n<\/div>\n<p>This transforms point observations into movement segments.<\/p>\n<p>The resulting table can be used for:<\/p>\n<ul>\n<li>route visualization,<\/li>\n<li>segment speed analysis,<\/li>\n<li>road matching,<\/li>\n<li>trajectory simplification,<\/li>\n<li>movement heatmaps.<\/li>\n<\/ul>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"7_Recursive_CTEs_for_Hierarchical_Spatial_Boundaries\"><\/span>7. Recursive CTEs for Hierarchical Spatial Boundaries<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Window functions are excellent for sequential analysis, but they do not solve hierarchical traversal.<\/p>\n<p>Consider a database containing nested spatial units:<\/p>\n<div>\n<pre dir=\"ltr\"><code>National Boundary\r\n\u2502\r\n\u251c\u2500\u2500 County\r\n\u2502   \u251c\u2500\u2500 Subcounty\r\n\u2502   \u2502   \u251c\u2500\u2500 Ward\r\n\u2502   \u2502   \u2514\u2500\u2500 Ward\r\n\u2502   \u2514\u2500\u2500 Subcounty\r\n\u2502\r\n\u2514\u2500\u2500 County<\/code><\/pre>\n<\/div>\n<p>The hierarchy can be represented relationally:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE TABLE spatial_units (\r\n    id          BIGSERIAL PRIMARY KEY,\r\n    name        TEXT NOT NULL,\r\n    parent_id   BIGINT REFERENCES spatial_units(id),\r\n    unit_type   TEXT NOT NULL,\r\n    geom        geometry(MultiPolygon, 4326)\r\n);<\/code><\/pre>\n<\/div>\n<p>The important relationship is:<\/p>\n<div>\n<pre dir=\"ltr\"><code>parent_id \u2192 id<\/code><\/pre>\n<\/div>\n<p>This means the spatial hierarchy is not necessarily determined by geometry alone. It is an explicit relational tree.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"8_Basic_Recursive_CTE\"><\/span>8. Basic Recursive CTE<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Suppose we want to retrieve an entire hierarchy starting from a particular root.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE hierarchy AS (\r\n\r\n    -- Anchor member: start with the root\r\n    SELECT\r\n        id,\r\n        name,\r\n        parent_id,\r\n        unit_type,\r\n        geom,\r\n        0 AS depth\r\n\r\n    FROM spatial_units\r\n\r\n    WHERE id = 1\r\n\r\n    UNION ALL\r\n\r\n    -- Recursive member: find children\r\n    SELECT\r\n        child.id,\r\n        child.name,\r\n        child.parent_id,\r\n        child.unit_type,\r\n        child.geom,\r\n        parent.depth + 1\r\n\r\n    FROM spatial_units AS child\r\n\r\n    JOIN hierarchy AS parent\r\n        ON child.parent_id = parent.id\r\n)\r\n\r\nSELECT *\r\nFROM hierarchy\r\nORDER BY depth, id;<\/code><\/pre>\n<\/div>\n<p>The recursive process is:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Root\r\n \u2193\r\nChildren\r\n \u2193\r\nGrandchildren\r\n \u2193\r\nGreat-grandchildren\r\n \u2193\r\n...<\/code><\/pre>\n<\/div>\n<p>The <code dir=\"ltr\">depth<\/code> column records how far each record is from the root.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"9_Building_a_Hierarchical_Spatial_Path\"><\/span>9. Building a Hierarchical Spatial Path<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>A powerful extension is maintaining the complete ancestry path.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE hierarchy AS (\r\n\r\n    SELECT\r\n        id,\r\n        name,\r\n        parent_id,\r\n        unit_type,\r\n        geom,\r\n\r\n        0 AS depth,\r\n\r\n        ARRAY[id] AS path\r\n\r\n    FROM spatial_units\r\n\r\n    WHERE id = 1\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        child.id,\r\n        child.name,\r\n        child.parent_id,\r\n        child.unit_type,\r\n        child.geom,\r\n\r\n        parent.depth + 1,\r\n\r\n        parent.path || child.id\r\n\r\n    FROM spatial_units AS child\r\n\r\n    JOIN hierarchy AS parent\r\n        ON child.parent_id = parent.id\r\n)\r\n\r\nSELECT\r\n    id,\r\n    name,\r\n    unit_type,\r\n    depth,\r\n    path\r\n\r\nFROM hierarchy\r\n\r\nORDER BY path;<\/code><\/pre>\n<\/div>\n<p>A path might look like:<\/p>\n<div>\n<pre dir=\"ltr\"><code>{1}\r\n{1,4}\r\n{1,4,12}\r\n{1,4,12,27}<\/code><\/pre>\n<\/div>\n<p>This makes the hierarchy explicitly queryable.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"10_Recursive_Spatial_Processing\"><\/span>10. Recursive Spatial Processing<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>The recursive CTE becomes particularly interesting when geometry itself participates in the analysis.<\/p>\n<p>For example, suppose subdivisions are stored as children and we want to determine their relationship with the parent geometry.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE hierarchy AS (\r\n\r\n    SELECT\r\n        id,\r\n        name,\r\n        parent_id,\r\n        unit_type,\r\n        geom,\r\n        0 AS depth\r\n\r\n    FROM spatial_units\r\n\r\n    WHERE parent_id IS NULL\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        child.id,\r\n        child.name,\r\n        child.parent_id,\r\n        child.unit_type,\r\n        child.geom,\r\n        parent.depth + 1\r\n\r\n    FROM spatial_units child\r\n\r\n    JOIN hierarchy parent\r\n        ON child.parent_id = parent.id\r\n\r\n    WHERE ST_Within(\r\n        child.geom,\r\n        parent.geom\r\n    )\r\n)\r\n\r\nSELECT\r\n    id,\r\n    name,\r\n    unit_type,\r\n    depth\r\n\r\nFROM hierarchy;<\/code><\/pre>\n<\/div>\n<p>The important distinction is that recursion determines <strong>which records are traversed<\/strong>, while PostGIS functions can validate or analyze their spatial relationships.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"11_Example_Nested_Watersheds\"><\/span>11. Example: Nested Watersheds<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Consider a watershed database:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Large Catchment\r\n   \u2193\r\nSub-Catchment\r\n   \u2193\r\nMicro-Catchment<\/code><\/pre>\n<\/div>\n<p>The table could contain:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE TABLE watersheds (\r\n    watershed_id BIGSERIAL PRIMARY KEY,\r\n    parent_id    BIGINT,\r\n    name         TEXT,\r\n    geom         geometry(MultiPolygon, 4326)\r\n);<\/code><\/pre>\n<\/div>\n<p>A recursive query can identify all descendants of a selected watershed:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE watershed_tree AS (\r\n\r\n    SELECT\r\n        watershed_id,\r\n        parent_id,\r\n        name,\r\n        geom,\r\n        0 AS level\r\n\r\n    FROM watersheds\r\n\r\n    WHERE watershed_id = 100\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        w.watershed_id,\r\n        w.parent_id,\r\n        w.name,\r\n        w.geom,\r\n        wt.level + 1\r\n\r\n    FROM watersheds w\r\n\r\n    JOIN watershed_tree wt\r\n        ON w.parent_id = wt.watershed_id\r\n)\r\n\r\nSELECT\r\n    watershed_id,\r\n    name,\r\n    level,\r\n    ST_Area(geom::geography) AS area_m2\r\n\r\nFROM watershed_tree\r\n\r\nORDER BY level, watershed_id;<\/code><\/pre>\n<\/div>\n<p>Now the recursion produces the hierarchy while PostGIS calculates the physical area of every spatial unit.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"12_Combining_Recursive_CTEs_With_Spatial_Aggregation\"><\/span>12. Combining Recursive CTEs With Spatial Aggregation<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>The output of a recursive query can be used as a spatial analytical dataset.<\/p>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE watershed_tree AS (\r\n\r\n    SELECT\r\n        watershed_id,\r\n        parent_id,\r\n        name,\r\n        geom,\r\n        0 AS level\r\n\r\n    FROM watersheds\r\n\r\n    WHERE watershed_id = 100\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        child.watershed_id,\r\n        child.parent_id,\r\n        child.name,\r\n        child.geom,\r\n        parent.level + 1\r\n\r\n    FROM watersheds child\r\n\r\n    JOIN watershed_tree parent\r\n        ON child.parent_id = parent.watershed_id\r\n)\r\n\r\nSELECT\r\n    level,\r\n\r\n    COUNT(*) AS watershed_count,\r\n\r\n    SUM(\r\n        ST_Area(geom::geography)\r\n    ) AS total_area_m2\r\n\r\nFROM watershed_tree\r\n\r\nGROUP BY level\r\n\r\nORDER BY level;<\/code><\/pre>\n<\/div>\n<p>This creates a hierarchical summary:<\/p>\n<div>\n<div>\n<div>\n<table>\n<thead>\n<tr>\n<th>Level<\/th>\n<th>Meaning<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>0<\/td>\n<td>Root watershed<\/td>\n<\/tr>\n<tr>\n<td>1<\/td>\n<td>Direct subdivisions<\/td>\n<\/tr>\n<tr>\n<td>2<\/td>\n<td>Nested subdivisions<\/td>\n<\/tr>\n<tr>\n<td>3<\/td>\n<td>Deeper subdivisions<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<\/div>\n<\/div>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"13_Hierarchical_Spatial_Queries_With_Containment\"><\/span>13. Hierarchical Spatial Queries With Containment<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Not every spatial hierarchy has an explicit <code dir=\"ltr\">parent_id<\/code>.<\/p>\n<p>Sometimes the hierarchy must be derived from geometry.<\/p>\n<p>For example, one layer may contain administrative boundaries at different levels:<\/p>\n<div>\n<pre dir=\"ltr\"><code>County\r\nSubcounty\r\nWard<\/code><\/pre>\n<\/div>\n<p>A spatial relationship can establish the parent-child relationship:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Contains(parent.geom, child.geom)<\/code><\/pre>\n<\/div>\n<p>However, blindly using this inside a recursive query can become computationally expensive.<\/p>\n<p>A better architecture is usually to <strong>materialize or persist the discovered parent relationship<\/strong>:<\/p>\n<div>\n<pre dir=\"ltr\"><code>child_id | parent_id\r\n---------+----------\r\n10       | 2\r\n11       | 2\r\n12       | 5<\/code><\/pre>\n<\/div>\n<p>Then recursion operates on the indexed relational relationship.<\/p>\n<p>This separates two expensive problems:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Spatial containment\r\n        \u2193\r\nParent-child relationship\r\n        \u2193\r\nRecursive hierarchy traversal<\/code><\/pre>\n<\/div>\n<p>That design is generally easier to maintain and optimize.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"14_Combining_Window_Functions_and_Recursive_CTEs\"><\/span>14. Combining Window Functions and Recursive CTEs<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>The two techniques do not have to be isolated.<\/p>\n<p>A sophisticated spatial workflow can use:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Recursive CTE\r\n      \u2193\r\nGenerate hierarchical subset\r\n      \u2193\r\nWindow function\r\n      \u2193\r\nAnalyze observations within each spatial unit\r\n      \u2193\r\nPostGIS geometry calculations<\/code><\/pre>\n<\/div>\n<p>For example, suppose GPS observations belong to nested operational zones.<\/p>\n<p>A recursive CTE can retrieve all zones beneath a selected parent, after which a window function can analyze movement observations within each zone.<\/p>\n<p>Conceptually:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE zones AS (\r\n\r\n    SELECT\r\n        id,\r\n        parent_id,\r\n        name,\r\n        0 AS depth\r\n\r\n    FROM operational_zones\r\n\r\n    WHERE id = 100\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        child.id,\r\n        child.parent_id,\r\n        child.name,\r\n        parent.depth + 1\r\n\r\n    FROM operational_zones child\r\n\r\n    JOIN zones parent\r\n        ON child.parent_id = parent.id\r\n),\r\n\r\nzone_points AS (\r\n\r\n    SELECT\r\n        p.id,\r\n        p.track_id,\r\n        p.recorded_at,\r\n        z.id AS zone_id,\r\n        p.geom\r\n\r\n    FROM gps_points p\r\n\r\n    JOIN zones z\r\n        ON ST_Within(p.geom, z.geom)\r\n),\r\n\r\nordered_points AS (\r\n\r\n    SELECT\r\n        *,\r\n        LAG(geom) OVER (\r\n            PARTITION BY track_id, zone_id\r\n            ORDER BY recorded_at\r\n        ) AS previous_geom\r\n\r\n    FROM zone_points\r\n)\r\n\r\nSELECT\r\n    id,\r\n    track_id,\r\n    zone_id,\r\n    recorded_at,\r\n\r\n    ST_Distance(\r\n        previous_geom::geography,\r\n        geom::geography\r\n    ) AS distance_m\r\n\r\nFROM ordered_points\r\n\r\nWHERE previous_geom IS NOT NULL;<\/code><\/pre>\n<\/div>\n<p>This illustrates the larger principle:<\/p>\n<blockquote><p><strong>CTEs define analytical stages; window functions define sequence; PostGIS defines spatial relationships.<\/strong><\/p><\/blockquote>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"15_Performance_Considerations\"><\/span>15. Performance Considerations<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Spatial window queries can become expensive when millions of observations are involved.<\/p>\n<p>Performance should therefore be designed into the schema and query architecture rather than added afterward.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"151_Spatial_indexes\"><\/span>15.1 Spatial indexes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A GiST index should normally be created on geometry columns used in spatial filtering.<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE INDEX idx_gps_points_geom\r\nON gps_points\r\nUSING GIST (geom);<\/code><\/pre>\n<\/div>\n<p>For hierarchy tables:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE INDEX idx_spatial_units_geom\r\nON spatial_units\r\nUSING GIST (geom);<\/code><\/pre>\n<\/div>\n<p>These indexes are particularly important for predicates such as:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Within()\r\nST_Intersects()\r\nST_Contains()\r\nST_DWithin()<\/code><\/pre>\n<\/div>\n<hr \/>\n<h2><span class=\"ez-toc-section\" id=\"152_Index_the_recursive_relationship\"><\/span>15.2 Index the recursive relationship<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>A recursive hierarchy frequently joins through:<\/p>\n<div>\n<pre dir=\"ltr\"><code>child.parent_id = parent.id<\/code><\/pre>\n<\/div>\n<p>Therefore:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE INDEX idx_spatial_units_parent\r\nON spatial_units(parent_id);<\/code><\/pre>\n<\/div>\n<p>This is often more important to recursive performance than the spatial index itself.<\/p>\n<p>The database is performing a relational traversal:<\/p>\n<div>\n<pre dir=\"ltr\"><code>parent_id \u2192 child records<\/code><\/pre>\n<\/div>\n<p>so PostgreSQL needs an efficient way to find those children.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"16_Indexing_Window-Function_Ordering\"><\/span>16. Indexing Window-Function Ordering<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Window functions such as:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(geom) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>require PostgreSQL to establish ordering within each track.<\/p>\n<p>A composite index can help:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE INDEX idx_gps_track_time\r\nON gps_points (\r\n    track_id,\r\n    recorded_at\r\n);<\/code><\/pre>\n<\/div>\n<p>This is particularly appropriate when the table is frequently queried by trajectory.<\/p>\n<p>The index corresponds directly to the analytical structure:<\/p>\n<div>\n<pre dir=\"ltr\"><code>track_id\r\n   \u2193\r\nrecorded_at\r\n   \u2193\r\nsequential observations<\/code><\/pre>\n<\/div>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"17_Avoid_Repeated_Coordinate_Transformations\"><\/span>17. Avoid Repeated Coordinate Transformations<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>A common performance mistake is repeatedly transforming the same geometry:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ST_Transform(geom, 32737)<\/code><\/pre>\n<\/div>\n<p>inside multiple expressions.<\/p>\n<p>For large datasets, consider storing an appropriate projected representation when justified by the application&#8217;s spatial requirements.<\/p>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ALTER TABLE gps_points\r\nADD COLUMN geom_utm geometry(Point, 32737);<\/code><\/pre>\n<\/div>\n<p>Then populate it:<\/p>\n<div>\n<pre dir=\"ltr\"><code>UPDATE gps_points\r\nSET geom_utm = ST_Transform(geom, 32737);<\/code><\/pre>\n<\/div>\n<p>Create an index if spatial queries will use the projected geometry:<\/p>\n<div>\n<pre dir=\"ltr\"><code>CREATE INDEX idx_gps_points_geom_utm\r\nON gps_points\r\nUSING GIST (geom_utm);<\/code><\/pre>\n<\/div>\n<p>This should be evaluated carefully because maintaining duplicate geometry columns introduces storage and data-maintenance costs.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"18_Be_Careful_With_CTE_Materialization\"><\/span>18. Be Careful With CTE Materialization<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>CTEs are logical query-building tools, but their execution behavior matters.<\/p>\n<p>For expensive workflows, inspect the execution plan:<\/p>\n<div>\n<pre dir=\"ltr\"><code>EXPLAIN (ANALYZE, BUFFERS)\r\nWITH ordered_points AS (\r\n    ...\r\n)\r\nSELECT ...<\/code><\/pre>\n<\/div>\n<p>Modern PostgreSQL can inline suitable CTEs, while explicit materialization can be requested when appropriate:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS MATERIALIZED (\r\n    ...\r\n)<\/code><\/pre>\n<\/div>\n<p>or:<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH ordered_points AS NOT MATERIALIZED (\r\n    ...\r\n)<\/code><\/pre>\n<\/div>\n<p>Materialization can be useful when the intermediate result is expensive to generate but reused multiple times.<\/p>\n<p>It can also hurt performance when it forces unnecessary storage and prevents useful optimization.<\/p>\n<p>Therefore, <strong>do not assume that a CTE is automatically a performance optimization<\/strong>.<\/p>\n<p>Treat it primarily as a query-organization mechanism and verify performance with <code dir=\"ltr\">EXPLAIN ANALYZE<\/code>.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"19_Partitioning_Window_Queries_Correctly\"><\/span>19. Partitioning Window Queries Correctly<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>The following query:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(geom) OVER (\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>would compare observations across every track.<\/p>\n<p>That is usually incorrect.<\/p>\n<p>Instead:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(geom) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at\r\n)<\/code><\/pre>\n<\/div>\n<p>ensures that the previous point belongs to the same trajectory.<\/p>\n<p>This distinction is critical.<\/p>\n<p>Without <code dir=\"ltr\">PARTITION BY<\/code>:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Vehicle A \u2192 Vehicle B<\/code><\/pre>\n<\/div>\n<p>could accidentally become a valid &#8220;movement segment.&#8221;<\/p>\n<p>With partitioning:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Vehicle A:\r\nP1 \u2192 P2 \u2192 P3\r\n\r\nVehicle B:\r\nP1 \u2192 P2 \u2192 P3<\/code><\/pre>\n<\/div>\n<p>each trajectory remains independent.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"20_Validate_Temporal_Ordering\"><\/span>20. Validate Temporal Ordering<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>GPS data can contain duplicate or inconsistent timestamps.<\/p>\n<p>This:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ORDER BY recorded_at<\/code><\/pre>\n<\/div>\n<p>may therefore produce nondeterministic ordering when two observations have identical timestamps.<\/p>\n<p>A safer pattern is:<\/p>\n<div>\n<pre dir=\"ltr\"><code>ORDER BY recorded_at, id<\/code><\/pre>\n<\/div>\n<p>For example:<\/p>\n<div>\n<pre dir=\"ltr\"><code>LAG(geom) OVER (\r\n    PARTITION BY track_id\r\n    ORDER BY recorded_at, id\r\n)<\/code><\/pre>\n<\/div>\n<p>The unique <code dir=\"ltr\">id<\/code> acts as a deterministic tie-breaker.<\/p>\n<p>For production trajectory processing, this small detail can prevent subtle analytical errors.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"21_Recursive_CTE_Safety\"><\/span>21. Recursive CTE Safety<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>Recursive queries should always have a clearly defined termination condition.<\/p>\n<p>In a properly modeled tree, recursion naturally stops when no additional children exist.<\/p>\n<p>However, malformed data can contain cycles:<\/p>\n<div>\n<pre dir=\"ltr\"><code>A \u2192 B \u2192 C \u2192 A<\/code><\/pre>\n<\/div>\n<p>A defensive implementation can maintain a path and prevent revisiting nodes.<\/p>\n<div>\n<pre dir=\"ltr\"><code>WITH RECURSIVE hierarchy AS (\r\n\r\n    SELECT\r\n        id,\r\n        parent_id,\r\n        name,\r\n        ARRAY[id] AS path\r\n\r\n    FROM spatial_units\r\n\r\n    WHERE id = 1\r\n\r\n    UNION ALL\r\n\r\n    SELECT\r\n        child.id,\r\n        child.parent_id,\r\n        child.name,\r\n        parent.path || child.id\r\n\r\n    FROM spatial_units child\r\n\r\n    JOIN hierarchy parent\r\n        ON child.parent_id = parent.id\r\n\r\n    WHERE NOT child.id = ANY(parent.path)\r\n)\r\n\r\nSELECT *\r\nFROM hierarchy;<\/code><\/pre>\n<\/div>\n<p>The condition:<\/p>\n<div>\n<pre dir=\"ltr\"><code>NOT child.id = ANY(parent.path)<\/code><\/pre>\n<\/div>\n<p>prevents a node already encountered in the current path from being traversed again.<\/p>\n<p>This is especially important when hierarchical data originates from multiple external systems.<\/p>\n<hr \/>\n<h1><span class=\"ez-toc-section\" id=\"Summary\"><\/span>Summary<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-1463\" src=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_tjrhcztjrhcztjrh-1024x559.jpg\" alt=\"Spatial Window Functions Recursive CTEs PostGIS architecture showing trajectory and hierarchy workflows\" width=\"1024\" height=\"559\" srcset=\"https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_tjrhcztjrhcztjrh-1024x559.jpg 1024w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_tjrhcztjrhcztjrh-300x164.jpg 300w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_tjrhcztjrhcztjrh-768x419.jpg 768w, https:\/\/helpwithmygisassignment.com\/blog\/wp-content\/uploads\/2026\/10\/Gemini_Generated_Image_tjrhcztjrhcztjrh.jpg 1408w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/p>\n<p><strong>Spatial Window Functions Recursive CTEs PostGIS<\/strong> combine three powerful capabilities:<\/p>\n<div>\n<pre dir=\"ltr\"><code>PostgreSQL Window Functions\r\n        +\r\nRecursive CTEs\r\n        +\r\nPostGIS Spatial Functions<\/code><\/pre>\n<\/div>\n<p>Window functions are particularly effective for <strong>sequential trajectory analysis<\/strong>. <code dir=\"ltr\">LAG()<\/code> and <code dir=\"ltr\">LEAD()<\/code> expose neighboring observations without cumbersome self-joins, allowing PostGIS to calculate:<\/p>\n<ul>\n<li>step distances,<\/li>\n<li>elapsed time,<\/li>\n<li>speed,<\/li>\n<li>heading,<\/li>\n<li>turning angles,<\/li>\n<li>trajectory segments.<\/li>\n<\/ul>\n<p>Recursive CTEs solve a different class of problems: <strong>hierarchical spatial queries<\/strong>. They can traverse nested administrative units, watersheds, property subdivisions, organizational spatial zones, and other parent-child structures.<\/p>\n<p>The strongest production architecture combines them:<\/p>\n<div>\n<pre dir=\"ltr\"><code>Raw spatial data\r\n      \u2502\r\n      \u25bc\r\nRecursive CTE\r\n      \u2502\r\n      \u251c\u2500\u2500 Identify spatial hierarchy\r\n      \u2502\r\n      \u25bc\r\nFiltered spatial dataset\r\n      \u2502\r\n      \u25bc\r\nWindow functions\r\n      \u2502\r\n      \u251c\u2500\u2500 LAG()\r\n      \u251c\u2500\u2500 LEAD()\r\n      \u251c\u2500\u2500 PARTITION BY\r\n      \u2514\u2500\u2500 ORDER BY\r\n      \u2502\r\n      \u25bc\r\nPostGIS calculations\r\n      \u2502\r\n      \u251c\u2500\u2500 ST_Distance()\r\n      \u251c\u2500\u2500 ST_Azimuth()\r\n      \u251c\u2500\u2500 ST_Within()\r\n      \u251c\u2500\u2500 ST_Contains()\r\n      \u2514\u2500\u2500 ST_Intersects()\r\n      \u2502\r\n      \u25bc\r\nAnalytical result<\/code><\/pre>\n<\/div>\n<p>For production systems, the key performance principles are equally important: <strong>GiST indexes for spatial predicates, B-tree indexes for hierarchical and temporal relationships, deterministic ordering for window functions, appropriate CRS handling, cycle protection for recursive queries, and <code dir=\"ltr\">EXPLAIN (ANALYZE, BUFFERS)<\/code> for validation<\/strong>.<\/p>\n<p>The result is a database-centric spatial processing architecture capable of handling sophisticated movement, hierarchy, and spatial relationship analysis without moving large datasets into external Python or GIS processing environments.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Spatial Window Functions and Recursive CTEs in PostGIS Introduction Complex spatial analysis in PostgreSQL rarely requires abandoning SQL for procedural code. By combining PostgreSQL&#8217;s analytical SQL features with PostGIS geometry functions, it is possible to build sophisticated workflows for trajectory analysis, spatial hierarchies, network-like traversals, and sequential geographic processes entirely inside the database. Two SQL &#8230; <a title=\"Spatial Window Functions and Recursive CTEs in PostGIS\" class=\"read-more\" href=\"https:\/\/helpwithmygisassignment.com\/blog\/spatial-window-functions-recursive-ctes-postgis\/\" aria-label=\"Read more about Spatial Window Functions and Recursive CTEs in PostGIS\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":1463,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1,4],"tags":[],"class_list":["post-1314","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized","category-gis-articles"],"_links":{"self":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1314","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/comments?post=1314"}],"version-history":[{"count":5,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1314\/revisions"}],"predecessor-version":[{"id":1464,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/posts\/1314\/revisions\/1464"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media\/1463"}],"wp:attachment":[{"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/media?parent=1314"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/categories?post=1314"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helpwithmygisassignment.com\/blog\/wp-json\/wp\/v2\/tags?post=1314"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}