rental_info.sql

-- You'll need the post GIS extensions installed on your Postgres instance
-- https://postgis.net/

CREATE TABLE rentals (
    id   INTEGER UNIQUE,
    price_in_cents INTEGER,
    url  TEXT,
    -- Used to determine uniqueness of the posting, perform a SHA hash of the content
    hash TEXT,
    -- This instructs PG to make location a geographic point on the 4326 sphere (The appromiximation of the earth used by GIS systems)
    location GEOGRAPHY(POINT,4326)
);

-- A zone is one of the polygons from your front end
CREATE TABLE zones (
      id   INTEGER UNIQUE,
      name TEXT,
      area GEOGRAPHY(POLYGON, 4326)
);

-- This returns all of the details of the rentals, joined with the name of the zone which contains them
-- The result set would have this schema
-- | id | url | hash | location | zone_id | zone_name |

SELECT r.*, z.id as zone_id, z.name as zone_name FROM rentals r JOIN zones z WHERE ST_Contains(z.area, r.location);

-- We can then do a subquery to aggregate all of the data by zone the result of this query would look like this:
-- | zone_id | zone_name | number_of_rentals | average_price | std_price
-- +---------+-----------+-------------------+---------------+----------
-- + 1       | foo       | 123               | 123.0         | 123.0   
-- +---------+-----------+-------------------+---------------+----------
-- + 2       | bar       | 123               | 123.0         | 123.0   
-- +---------+-----------+-------------------+---------------+----------
SELECT 
  z, 
  count(*) as number_of_rentals, 
  AVG(zr.price_in_cents)::FLOAT / 100 as average_price, 
  STD_DEV(zr.price_in_cents)::FLOAT / 100 as std_price
FROM (
  SELECT r.*, z.id as zone_id, z.name as zone_name FROM rentals r JOIN zones z WHERE ST_Contains(z.area, r.location)
) as zone_rentals zr GROUP BY zone_id;