logo标签列表

如何提高GTFS查询速度？

mysqlgtfs
3
我正在尝试处理GTFS数据库，具体来说是RATP为巴黎及其周边提供的数据库。
数据集非常庞大。stop_times表有1400万行。
以下是表模式：https://github.com/mauryquijada/gtfs-mysql/blob/master/gtfs-sql.sql 我正在尝试找到在特定位置获取可用路线的最有效方法。就我理解的GTFS规范而言，这里是我的数据从（纬度/经度）到路线的表格和链接：
stops      | stop_times     | trips      | routes
-----------+----------------+------------+--------------
lat        | stop_id        | trip_id    | route_id
lon        | trip_id        | route_id   |
stop_id    |                |            |

我将我想要的内容编译成了三个步骤（实际上是我们在上面四个表格之间的三个链接），为了清晰起见，发布在这个gist下：https://gist.github.com/BenoitDuffez/4eba85e3598ebe6ece5f
以下是我创建此脚本的方法。
我能够在不到一秒钟的时间内快速找到所有步行距离内（比如200米）的站点。我使用：
$ . mysql.ini && time mysql -h $host -N -B -u $user -p${pass} $name -e "SELECT stop_id, (6371000*acos(cos(radians(48.824699))*cos(radians(s.stop_lat))*cos(radians(2.3243)-radians(s.stop_lon))+sin(radians(48.824699))*sin(radians(s.stop_lat)))) AS distance
FROM stops s
GROUP BY s.stop_id
HAVING distance < 200
ORDER BY distance ASC" | awk '{print $1}'
3705271
4472979
4036891
4036566
3908953
3908755
3900765
3900693
3900607
4473141
3705272
4472978
4036892
4036472
4035057
3908952
3705288
3908814
3900832
3900672
3900752
3781623
3781622

real    0m0.797s
user    0m0.000s
sys     0m0.000s

然后，获取所有今天之后的停靠时间（使用 stop_times.departure_time > '``date +%T``'）需要花费很长时间：

"SELECT trip_id
 FROM stop_times
 WHERE
     stop_id IN ($stops) AND departure_time >= '$now'

 GROUP BY trip_id"

在第一步中，$stops 包含从获取到的站点列表。以下是一个例子：

$ . mysql.ini && time mysql -h $host -N -B -u $user -p${pass} $name -e "SELECT stop_id, (6371000*acos(cos(radians(
FROM stops s
GROUP BY s.stop_id
HAVING distance < 200
ORDER BY distance ASC" | awk '{print $1}'
3705271
4472979
4036891
4036566
3908953
...
9916360850964321
9916360920964320
9916360920964321

real    1m21.399s
user    0m0.000s
sys     0m0.000s

这个结果有超过2000行。

我最后的步骤是选择所有与这些trip_id相匹配的路线。这非常简单，并且相当快速:

$ . mysql.ini && time mysql -h $host -u $user -p${pass} $name -e "SELECT r.id, r.route_long_name FROM trips t, routes r WHERE t.trip_id IN (`cat trip_ids | tr '\n' '#' | sed -e 's/##$//' -e 's/#/,/g'`) AND r.route_id = t.route_id GROUP BY t.route_id"
+------+-------------------------------------------------------------------------+
| id   | route_long_name                                                         |
+------+-------------------------------------------------------------------------+
|  290 | (PLACE DE CLICHY <-> CHATILLON METRO) - Aller                           |
|  291 | (PLACE DE CLICHY <-> CHATILLON METRO) - Retour                          |
|  404 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Aller  |
|  405 | (PORTE D'ORLEANS-METRO <-> ECOLE VETERINAIRE DE MAISON-ALFORT) - Retour |
|  453 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  457 | (PORTE D'ORLEANS-METRO <-> LYCEE POLYVALENT) - Retour                   |
|  479 | (PORTE D'ORLEANS-METRO <-> VELIZY 2) - Retour                           |
|  810 | (PLACE DE LA LIBERATION <-> GARE MONTPARNASSE) - Aller                  |
|  989 | (PORTE D'ORLEANS-METRO) - Retour                                        |
| 1034 | (PLACE DE LA LIBERATION <-> HOTEL DE VILLE DE PARIS_4E__AR) - Aller     |
+------+-------------------------------------------------------------------------+

real    0m1.070s
user    0m0.000s
sys     0m0.000s

这里有一个名为trip_ids的文件，其中包含了2k个trip ID。

如何更快地获得这个结果？除了我已经采取的stops>stop_times>trips>routes路径之外，是否有更好的方法来爬取数据？

在这里，实际上只需要进行一个查询：“这个位置200米内有哪些路线可用？”总时间大约为30秒。太慢了...

3个回答
4
简短回答是：使用表连接和索引。
以下是更详细的答案：
您的想法是正确的，您对表之间的关系的理解也是正确的。但是，如果您要求DBMS从列表中匹配字段值（使用WHERE...IN），而不是将表连接在一起，则需要它执行比实际需要更多的工作。
您真正想要做的是将所有内容作为单个查询执行，使用JOIN子句将表链接在一起。请尝试以下操作，此外，还可以将calendars和calendar_dates表连接起来，以仅限制结果为今天实际运营的路线：
SELECT DISTINCT r.id, r.route_long_name
  FROM (SELECT s.stop_id, (6371000 *
          acos(cos(radians(48.824699)) * cos(radians(s.stop_lat)) *
          cos(radians(2.3243) - radians(s.stop_lon)) +
          sin(radians(48.824699)) * sin(radians(s.stop_lat)))) AS distance
          FROM stops AS s) AS i_s
  INNER JOIN stop_times AS st ON st.stop_id = i_s.stop_id
  INNER JOIN (SELECT trip_id, route_id FROM trips AS t
                INNER JOIN (SELECT service_id FROM calendars
                              WHERE start_date <= '2014-09-09'
                                AND end_date >= '2014-09-09'
                                AND tuesday = 1
                              UNION
                                SELECT service_id FROM calendar_dates
                                  WHERE date = '2014-09-09'
                                    AND exception_type = 1
                              EXCEPT
                                SELECT service_id FROM calendar_dates
                                  WHERE date = '2014-09-09'
                                    AND exception_type = 2) AS c
                   ON c.service_id = t.service_id) AS t_r
    ON t_r.trip_id = st.trip_id
  INNER JOIN routes AS r ON r.route_id = t_r.route_id
  WHERE st.departure_time > '$now'
    AND i_s.distance < 200;

这里使用INNER JOIN将另一张表的列“添加”进来，只包括符合ON语句条件的行。与先生成结果列表再喂入下一个查询相比，这应该快得多。
为了获得更好的性能，您需要创建索引以防止DBMS线性扫描表。经验法则是为每个在JOIN或WHERE子句中使用的列定义索引。以下是我定义的索引，您应该会发现它们使上面的查询表现良好：
CREATE INDEX calendar_dates_date_exception_type_service_id_index
  ON calendar_dates (date, exception_type, service_id);

CREATE INDEX trips_service_id_trip_id_route_id_index
  ON trips (service_id, trip_id, route_id);

CREATE INDEX stop_times_trip_id_departure_time_stop_id_index
  ON stop_times (trip_id, departure_time, stop_id);

CREATE INDEX routes_route_id_index ON routes (route_id);

CREATE INDEX stops_stop_id_index ON stops (stop_id);
啊，非常感谢您的快速回复。我已经尝试使用LEFT JOIN，但它花费了很长时间（实际上我不知道，我用Ctrl-C中断了查询）。您的查询只花费了53秒，比我所有的查询加起来都要好得多。添加索引花费了一些时间，现在已经过去几分钟了，但它们还没有被创建。请继续关注！ - Benoit Duffez
其实那是公平的。我使用硬编码为 18:30:00 的出发时间进行测试，我在36秒内得到了我们在我的问题中看到的结果。 - Benoit Duffez
我要提一下的另一件事是：实际上，我们的查询应该连接“calendars”和“calendar_dates”表，以便将结果限制为仅运行今天的路线。(请参见“GTFS查询包括日历例外”中的我的答案。) - user473305
不，您的查询没有结果。我稍微改写了一下，仍然没有结果。但我的脚本运行良好。我已更新我的gist，以包含我用于构建数据库的脚本。不过要小心，这将向服务器发送751MB的数据，并且所使用的InnoDB空间超过2.6GB。// PS：日历方面很棒。我将不得不添加这个连接！非常感谢。 - Benoit Duffez
谢谢。我在脑海中进行所有这些操作时处于不利地位。让我看看能否弄清楚为什么查询失败了。 - user473305
显示剩余5条评论
2
我使用的表架构是错误的，我应该在使用之前自己构建或至少进行分析。
以下是更新后的表结构：
CREATE TABLE `agency` (
    transit_system VARCHAR(50) NOT NULL,
    agency_id VARCHAR(100),
    agency_name VARCHAR(255) NOT NULL,
    agency_url VARCHAR(255) NOT NULL,
    agency_timezone VARCHAR(100) NOT NULL,
    agency_lang VARCHAR(100),
    agency_phone VARCHAR(100),
    agency_fare_url VARCHAR(100),
    PRIMARY KEY (agency_id)
);

CREATE TABLE `calendar_dates` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    service_id VARCHAR(255) NOT NULL,
    `date` VARCHAR(8) NOT NULL,
    exception_type TINYINT(2) NOT NULL,
    KEY `service_id` (service_id),
    KEY `exception_type` (exception_type)    
);

CREATE TABLE `calendar` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    service_id VARCHAR(255) NOT NULL,
    monday TINYINT(1) NOT NULL,
    tuesday TINYINT(1) NOT NULL,
    wednesday TINYINT(1) NOT NULL,
    thursday TINYINT(1) NOT NULL,
    friday TINYINT(1) NOT NULL,
    saturday TINYINT(1) NOT NULL,
    sunday TINYINT(1) NOT NULL,
    start_date VARCHAR(8) NOT NULL, 
    end_date VARCHAR(8) NOT NULL,
    KEY `service_id` (service_id)
);

CREATE TABLE `fare_attributes` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    fare_id VARCHAR(100),
    price VARCHAR(50) NOT NULL,
    currency_type VARCHAR(50) NOT NULL,
    payment_method TINYINT(1) NOT NULL,
    transfers TINYINT(1) NOT NULL,
    transfer_duration VARCHAR(10),
    exception_type TINYINT(2) NOT NULL,
    agency_id INT(100),
    KEY `fare_id` (fare_id)
);

CREATE TABLE `fare_rules` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    fare_id VARCHAR(100),
    route_id VARCHAR(100),
    origin_id VARCHAR(100),
    destination_id VARCHAR(100),
    contains_id VARCHAR(100),
    KEY `fare_id` (fare_id),
    KEY `route_id` (route_id)
);

CREATE TABLE `feed_info` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    feed_publisher_name VARCHAR(100),
    feed_publisher_url VARCHAR(255) NOT NULL,
    feed_lang VARCHAR(255) NOT NULL,
    feed_start_date VARCHAR(8),
    feed_end_date VARCHAR(8),
    feed_version VARCHAR(100)
);

CREATE TABLE `frequencies` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    trip_id VARCHAR(100) NOT NULL,
    start_time VARCHAR(8) NOT NULL,
    end_time VARCHAR(8) NOT NULL,
    headway_secs VARCHAR(100) NOT NULL,
    exact_times TINYINT(1),
    KEY `trip_id` (trip_id)
);

CREATE TABLE `routes` (
    transit_system VARCHAR(50) NOT NULL,
    route_id VARCHAR(100),
    agency_id VARCHAR(50),
    route_short_name VARCHAR(50) NOT NULL,
    route_long_name VARCHAR(255) NOT NULL,
    route_type VARCHAR(2) NOT NULL, 
    route_text_color VARCHAR(255),
    route_color VARCHAR(255),
    route_url VARCHAR(255),
    route_desc VARCHAR(255),
    PRIMARY KEY (route_id),
    KEY `agency_id` (agency_id),
    KEY `route_type` (route_type),
    CONSTRAINT `agency_id` FOREIGN KEY (`agency_id`) REFERENCES `agency` (`agency_id`)
);

CREATE TABLE `shapes` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    shape_id VARCHAR(100) NOT NULL,
    shape_pt_lat DECIMAL(8,6) NOT NULL,
    shape_pt_lon DECIMAL(8,6) NOT NULL,
    shape_pt_sequence TINYINT(3) NOT NULL,
    shape_dist_traveled VARCHAR(50),
    KEY `shape_id` (shape_id)
);

CREATE TABLE `stops` (
    transit_system VARCHAR(50) NOT NULL,
    stop_id VARCHAR(255),
    stop_code VARCHAR(50),
    stop_name VARCHAR(255) NOT NULL,
    stop_desc VARCHAR(255),
    stop_lat DECIMAL(10,6) NOT NULL,
    stop_lon DECIMAL(10,6) NOT NULL,
    zone_id VARCHAR(255),
    stop_url VARCHAR(255),
    location_type VARCHAR(2),
    parent_station VARCHAR(100),
    stop_timezone VARCHAR(50),
    wheelchair_boarding TINYINT(1),
    PRIMARY KEY (stop_id),
    KEY `zone_id` (zone_id),
    KEY `stop_lat` (stop_lat),
    KEY `stop_lon` (stop_lon),
    KEY `location_type` (location_type),
    KEY `parent_station` (parent_station)
);

CREATE TABLE `trips` (
    transit_system VARCHAR(50) NOT NULL,
    route_id VARCHAR(100) NOT NULL,
    service_id VARCHAR(100) NOT NULL,
    trip_id VARCHAR(255),
    trip_headsign VARCHAR(255),
    trip_short_name VARCHAR(255),
    direction_id TINYINT(1), #0 for one direction, 1 for another.
    block_id VARCHAR(11),
    shape_id VARCHAR(11),
    wheelchair_accessible TINYINT(1), #0 for no information, 1 for at least one rider accommodated on wheel chair, 2 for no riders accommodated.
    bikes_allowed TINYINT(1), #0 for no information, 1 for at least one bicycle accommodated, 2 for no bicycles accommodated
    PRIMARY KEY (trip_id),
    KEY `route_id` (route_id),
    KEY `service_id` (service_id),
    KEY `direction_id` (direction_id),
    KEY `block_id` (block_id),
    KEY `shape_id` (shape_id),
    CONSTRAINT `route_id` FOREIGN KEY (`route_id`) REFERENCES `routes` (`route_id`),
    CONSTRAINT `service_id` FOREIGN KEY (`service_id`) REFERENCES `calendar` (`service_id`)
);

CREATE TABLE `stop_times` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    trip_id VARCHAR(100) NOT NULL,
    arrival_time VARCHAR(8) NOT NULL,
    arrival_time_seconds INT(100),
    departure_time VARCHAR(8) NOT NULL,
    departure_time_seconds INT(100),
    stop_id VARCHAR(100) NOT NULL,
    stop_sequence VARCHAR(100) NOT NULL,
    stop_headsign VARCHAR(50),
    pickup_type VARCHAR(2),
    drop_off_type VARCHAR(2),
    shape_dist_traveled VARCHAR(50),
    KEY `trip_id` (trip_id),
    KEY `arrival_time_seconds` (arrival_time_seconds),
    KEY `departure_time_seconds` (departure_time_seconds),
    KEY `stop_id` (stop_id),
    KEY `stop_sequence` (stop_sequence),
    KEY `pickup_type` (pickup_type),
    KEY `drop_off_type` (drop_off_type),
    CONSTRAINT `trip_id` FOREIGN KEY (`trip_id`) REFERENCES `trips` (`trip_id`),
    CONSTRAINT `stop_id` FOREIGN KEY (`stop_id`) REFERENCES `stops` (`stop_id`)
);

CREATE TABLE `transfers` (
    id INT(12) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    transit_system VARCHAR(50) NOT NULL,
    from_stop_id INT(100) NOT NULL,
    to_stop_id VARCHAR(8) NOT NULL,
    transfer_type TINYINT(1) NOT NULL,
    min_transfer_time VARCHAR(100)
);

我已经将xyz_id键作为自己表中的PRIMARY KEY，并在其他表中作为FOREIGN KEY。我还需要对此架构进行一些优化。
现在，此查询的运行时间少于1-5秒：
SELECT
    s.stop_id,
    (6371000*acos(cos(radians(48.1128135))*cos(radians(s.stop_lat))*cos(radians(-1.6470705)-radians(s.stop_lon))+sin(radians(48.1128135))*sin(radians(s.stop_lat)))) AS distance,
    t.route_id,
    st.*,
    t.*,
    r.*,
    c.*

FROM stop_times st

LEFT JOIN stops s USING (stop_id)
LEFT JOIN trips t USING (trip_id)
LEFT JOIN routes r USING (route_id)

LEFT JOIN calendar c ON c.service_id = t.service_id 

where
    c.start_date <= 20140915
    and c.end_date >= 20140915
    and c.sunday = 1

    and st.departure_time > '15:00:00'

HAVING
    distance < 200

ORDER BY st.departure_time ASC
实际上规范没有提到类型，所以我们只能假设是 VARCHAR。这超出了我的理解范围，因为整数键要快得多。 - Benoit Duffez
在您的“shapes”表中，shape_pt_lon DECIMAL(8,6)实际上应该是DECIMAL(9,6)，以考虑“-180到180”的范围？（尽管这对于您拥有的数据可能并非必要） - tospig
1
你说得对，我之前注意到了那个 bug 并修复了它。我没有费心在这里编辑我的代码。 - Benoit Duffez
-1
我只能告诉你，我曾尝试使用SQL做同样的事情，但它花了很长时间，所以我不得不先用Perl（没有任何提升），然后用C++编写脚本（快了35倍）。
1
这应该是一条评论，而不是回答问题。 - Benoit Duffez
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接