terminator-search-request.sql

CREATE TABLE send (
    provider          INTEGER   NOT NULL,
    tariff_id         INTEGER   NOT NULL,
    active_from       INTEGER   NOT NULL,
    active_until      INTEGER   NOT NULL,
    tag_from_id       INTEGER   NOT NULL,
    terminal_id       INTEGER   NOT NULL,
    lat               FLOAT     NOT NULL,
    lon               FLOAT     NOT NULL,
    max_weight        INTEGER   NOT NULL,
    max_height        INTEGER   NOT NULL,
    max_length        INTEGER   NOT NULL,
    max_width         INTEGER   NOT NULL,
    max_declared_cost INTEGER   NOT NULL
);

CREATE TABLE receive (
    tag_from_id       INTEGER   NOT NULL,
    terminal_id       INTEGER   NOT NULL,
    lat               FLOAT     NOT NULL,
    lon               FLOAT     NOT NULL,
    max_weight        INTEGER   NOT NULL,
    max_height        INTEGER   NOT NULL,
    max_length        INTEGER   NOT NULL,
    max_width         INTEGER   NOT NULL,
    max_declared_cost INTEGER   NOT NULL,
    tariff_zone_id    INTEGER   NOT NULL,
    min_term          SMALLINT  NOT NULL,
    max_term          SMALLINT  NOT NULL
);

CREATE INDEX send_idx
  ON send(lon, lat, active_from, active_until);

CREATE INDEX receive_idx
  ON receive(tag_from_id, lon, lat);

SELECT r.terminal_id,
       r.lat, r.lon,
       r.tariff_zone_id,
       r.min_term, r.max_term
FROM   receive r
INNER JOIN (
    SELECT  DISTINCT ON (s.provider) provider, tariff_id, s.tag_from_id, Point(s.lat, s.lon) <-> Point (:seller_lat, :seller_lon) AS dist
    FROM   send s
    WHERE
        s.lat BETWEEN :seller_leftbot_lat AND :seller_righttop_lat
        AND s.lon BETWEEN :seller_leftbot_lon AND :seller_righttop_lon
        AND :now BETWEEN s.active_from AND s.active_until
        AND s.max_weight > :weight AND s.max_height > :height AND s.max_length > :length AND s.max_width > :width AND s.max_declared_cost > :declared_cost
    ORDER  BY provider, dist
    ) AS s USING (tag_from_id)
WHERE
    r.lat BETWEEN :buyer_leftbot_lat AND :buyer_righttop_lat
    AND r.lon BETWEEN :buyer_leftbot_lon AND :buyer_righttop_lon
    AND r.max_weight > :weight AND r.max_height > :height AND r.max_length > :length AND r.max_width > :width AND r.max_declared_cost > :declared_cost
LIMIT :limit;