-
-
Save iosadchiy/e77e4ae79829bdb2328f1f4b15b06378 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment