Speeding Point‑in‑Polygon Queries with Detaspace Geo‑Partitioning

Problem: Slow Point‑in‑Polygon Lookups

Large geospatial tables—think 10‑million GPS points—can make a simple point‑in‑polygon query take 10–15 seconds on a standard Detaspace cluster. The engine still scans every row because it has no spatial awareness of where points live on the map. This becomes a bottleneck for dashboards, real‑time alerts, and any read‑heavy workload.

How Geo‑Partitioning Solves It

Detaspace’s Geo‑Partitioning feature automatically tiles a table into spatial blocks. You declare a grid (e.g., 1 km × 1 km) and the system splits the data into separate files—one per tile. During a query, the engine examines the polygon’s bounding box, determines which tiles intersect it, and reads only those files. The rest of the data is skipped entirely.

The partitioning step is one‑time; after the table is materialized, the tiles are stored as ordinary files in the catalog. Existing SQL syntax remains unchanged—your point‑in‑polygon query still looks the same, but the engine does the heavy lifting behind the scenes.

Practical Implementation Example

1. Prerequisites
   • Detaspace 2.3 or newer.
   • Administrative rights to create tables and run partitioning jobs.
   • A sample point table named raw_points with columns id, geom, value.
2. Create a partitioned table

   # Run in the Detaspace shell as an admin
   CREATE TABLE geo_points PARTITION BY GEO_GRID(
       tile_size => 1000,        -- 1 km grid in meters
       overlap   => 0            -- no overlap to avoid duplicates
   ) AS
   SELECT id, geom, value
   FROM raw_points;
   

   This command tells the engine to split raw_points into 1 km tiles. The resulting geo_points table contains the same schema but is backed by many small files, one per tile.

3. Run a point‑in‑polygon query

   SELECT id, geom, value
   FROM geo_points
   WHERE ST_Contains(
       ST_MakePolygon(
           ST_GeomFromText('LINESTRING(30 10, 40 40, 20 40, 30 10)')
       ),
       geom
   );
   

   Because the polygon’s bounding box only overlaps a handful of tiles, the query engine reads those files only. The execution plan (visible via EXPLAIN) will show a small number of file reads compared to the unpartitioned case.

4. Validate no duplicates

   SELECT COUNT(DISTINCT id) AS unique_count
   FROM geo_points
   WHERE ST_Contains(...);
   

   With overlap => 0 you should see unique_count equal to the number of points returned by the plain query. If you enable overlap, add DISTINCT or aggregate to avoid double counting.

5. Check storage impact

   # Query the catalog for table size
   SELECT table_name, total_size_bytes
   FROM detaspace_catalog.tables
   WHERE table_name = 'geo_points';
   

   Compare this size to the original raw_points table. Expect a modest increase—typically 10–20%—due to file metadata and possible duplicate boundary points when overlap is used.

Trade‑offs & Limitations

• Storage overhead – Each tile is a separate file. With fine‑grained grids (e.g., 100 m tiles) the file count grows, increasing filesystem overhead and catalog size. Overlap adds duplication.
• Write latency – Inserting new points requires determining the correct tile and writing to its file. For write‑heavy pipelines, this can add latency.
• Tile size selection – Too coarse a grid (e.g., 10 km) means many irrelevant points still need to be scanned within a tile, reducing the benefit. Too fine a grid increases file count and overhead.
• Version support – Geo‑Partitioning is only available in Detaspace 2.3+. Earlier releases lack spatial metadata and must use manual filtering.
• Duplicate handling – Overlap must be carefully managed; otherwise, queries may return duplicate points unless DISTINCT or aggregation is applied.

Actionable Next Steps

1. Run the benchmark on your own dataset: create a 1 km grid, measure latency before and after, and record the number of files read.
2. Use the catalog’s partition_stats view to monitor tile distribution and adjust tile_size if some tiles are too dense or sparse.
3. For incremental data, append to existing tiles with INSERT INTO geo_points SELECT …; if many new tiles are needed, run ALTER TABLE geo_points ADD PARTITION … to create them without reprocessing the whole table.
4. If storage is a concern, experiment with a 2 km grid and zero overlap; compare query latency and catalog size.
5. Document the chosen grid parameters in your data‑engineering playbook so future teams can replicate the partitioning strategy.

By following these steps you can reduce a 12‑second point‑in‑polygon query to under 1 second on a standard cluster, while keeping the trade‑offs transparent and manageable.