Count ships within each grid-tile

count_ships.sql

-- Count number of ships (ais_position) within each grid-tile (mgrs)

-- Query 1 : Spatial query
SELECT count(*)
FROM ais_position as a
,    mgrs as m
WHERE ST_Within(a.geom, m.geom)
GROUP BY m.name;

-- Way to slow. Instead doing ST_Within on INSERT
-- and storing mgrs names in Array-column for each ais_position

-- Query 2 : Array containing mgrs name
SELECT count(*)
FROM ais_position as a
,    mgrs as m 
WHERE a.mgrs_name @> ARRAY[m.name]
GROUP BY m.name;

-- Much faster. 
-- Execution time 400 miliseconds when
-- ais_position has 400 thousand rows.
-- Not scalable. Runs forever when
-- ais_position has 400 million rows.

-- Wait a minute... ais_position contains all information wee need!

-- Query 3 : "Map-Reduce-ish"
with names (name) as (
    SELECT unnest(mgrs_name)
    FROM ais_position
)
SELECT name, count(*)
FROM names
GROUP BY name;
  
-- Execution time 738 milliseconds when
-- ais_position has 400 thousand rows. (Slower than query 2)
-- Execution time 7 minutes when
-- ais_position has 400 million rows!