Skip to content

Latest commit

 

History

History
156 lines (124 loc) · 8.73 KB

POSTGIS.md

File metadata and controls

156 lines (124 loc) · 8.73 KB
Raw

Optimizing AirCasting

Working on the PurpleAir integration, we realized that the current architecture is limiting performance.

Loading ~10.000 sessions takes several seconds. In fact, AirCasting performs the following:

  1. BACKEND: Queries multiple MySql tables: users, sessions, streams and sometimes measurements against the filtering criteria
  2. BACKEND: Loops through records to build the JSON
  3. Sends tens of megabytes to the browser
  4. FRONTEND: Loops through each item from the JSON to draw single session markers on the map
  5. FRONTEND: Clusters single session markers that are close to each other into cluster markers

Currently, the browser needs to know about each individual session matching the filtering criteria to:

a. MAP: Render the single session markers with the average value b. BOTTOM: Render all the session cards c. BOTTOM-LEFT: Export the sessions via email (the URL includes the ids of all the sessions shown on the map to export) d. Probably some other minor reasons

But there is no need to:

  • Render tens of thousands of single markers on the map just to have them clustered later
  • Render tens of thousands of session cards; nobody would scroll through all of them

It would be more performant to:

  • Calculate the clusters on the backend and send aggregated information (e.g., bounding box, average value) to the frontend (instead of sending each individual session); this will probably reduce the items sent to the browser from ~10.000 to ~200.
  • Render one card per cluster (not per session)

Hex map from the Zalando blog post (see list at the bottom)

Some other ideas:

  • Migrate to Postgres and Postgis (instead of MySql)
    • Implement the heatmap / crowdmap / clusters with ST_SquareGrid or ST_HexagonGrid
    • Go through a transition period where some things are in MySQL some others in Postgis (PurpleAir?)
  • Use QGis to visualize things in development
  • Treat latitude and longitude as coordinates in the database, not as decimal numbers.
  • Restructure the database schema
    • Maybe PurpleAir measurements could be one table instead of four (sessions, streams, measurements, users)?

With the current version of Rails (thanks Tomasz for preparing the foundations to make it possible!), we could use two databases to migrate AirCasting to Postgres / Postgis incrementally. Here's some pseudo-code to keep PurpleAir measurements in Postgis:

gem "pg", "~> 1.3"
gem "activerecord-postgis-adapter", "~> 7.1"


class CreatePurpleAirMeasurements < ActiveRecord::Migration[6.1]
  def change
    create_table :purple_air_measurements do |t|
      t.float :value
      t.st_point "point", geography: true # or geometry: true
      t.datetime "time_local"
      t.datetime "time_utc"
      t.string "location"
      t.string "city"
    end
  end
end

class PostgisApplicationRecord < ActiveRecord::Base
  self.abstract_class = true

  connects_to database: { writing: :postgis, reading: :postgis }
end

class PurpleAirMeasurement < PostgisApplicationRecord
end


PurpleAirMeasurement.new(
  # Need to choose the proper factory.
  point: RGeo::Geographic.spherical_factory.point(longitude, latitude),
  # ...
)
-- Cluster the extent of the measurements in purple_air_measurements
-- and return for each:
--   number of sessions
--   bounding box
--   average value
-- This query is copied from the ST_HexagonGrid docs (see list at the bottom).

SELECT COUNT(*), hexes.geom, AVG(value)
FROM
    ST_HexagonGrid(
        10000,
        ST_SetSRID(ST_EstimatedExtent('purple_air_measurements', 'point'), 3857)
    ) AS hexes
    INNER JOIN
    purple_air_measurements AS measurement
    ON ST_Intersects(measurement.point, hexes.geom)
GROUP BY hexes.geom;

What the iOS app does

Marysia implemented a cool algorithm to display the heatmap on iOS:

  1. User selects a single session to display
  2. The route is drawn as a polyline where segments are connecting the coordinates of each subsequent measurement
  3. The map is subdivided into 120 squares
  4. Measurements are bucketized into each square
  5. Empty squares remain transparent
  6. Squares containing at least one measurement are colored depending on the average value of the contained measurements

Notice that:

  • The algorithm works smoothly with ~30.000 measurements
  • All the sessions are stored on the device: no need to query or fetch anything
  • The algorithm is implemented to display single session (not all of them at once)
  • Single squares are not interactive (i.e., they don't do anything if tapped)
  • Polygons are redrawn when zooming and dragging the map

Unfortunately, this wouldn't speed up what the webapp does (see the first list in this document) because iOS does not do the same work:

  1. Not needed because the data is already on the device
  2. Not needed because the data is already on the device
  3. Not needed because the data is already on the device
  4. and 5. are pretty much done in one pass

Interesting Reads