从图数据库中提取节点

Question

我刚接触过Postgres和PostGIS，但不熟悉地理空间应用程序。

我有一个以链接(边)的形式装载图形数据的表格。链接数据库大约有60,000,000行。我正在尝试提取节点，以便更快地进行搜索。对于我的一些概念证明工作，我能够使用链接表进行搜索，但是会有很多重复的，并且不能保证源列或目标列包含所有节点。

这是Postgres & PostGIS，所以我还使用表来缓存几何学->地理转换。是的，我确实需要使用地理字段。我也复制几何信息“以防万一”。

表创建SQL：

-- Recreate table and index
DROP TABLE IF EXISTS nodes;

CREATE TABLE nodes (node integer PRIMARY KEY, geog geography(POINT,4326) );

CREATE INDEX geogIndex ON nodes USING GIST(geog);

SELECT AddGeometryColumn('nodes', 'geom', 4326, 'POINT', 2);

-- Insert all unique nodes from the source column
INSERT INTO nodes (node,geog,geom)
       SELECT DISTINCT ON (source) source,geography( ST_Transform(geom_source,4326)),geom_source 
       FROM view_topo;

-- Insert any nodes in the target column that we don't have already
INSERT INTO nodes (node,geog,geom)
       SELECT DISTINCT ON (target) target,geography( ST_Transform(geom_target,4326)),geom_target 
       FROM view_topo
       WHERE NOT EXISTS( SELECT 1 FROM nodes WHERE nodes.node = view_topo.target);

VACUUM ANALYZE;

我离开了第一个INSERT运行了一夜，它花了大约2-3小时运行。这导致添加了大约40,000,000个唯一节点。

我刚刚启用了第二个INSERT和VACUUM ANALYZE。我希望它至少要到午餐时间。

幸运的是，这只是一个批处理作业，必须在加载新的链接表之后执行一次，但是有更好的方法吗？有更快的路吗？

Answer 1

看看PostgreSQL关于向表中添加大量数据的技巧。特别是，您确定在对所有数据进行INSERT之前都需要该索引吗？如果在将所有数据添加到表中之后创建索引，它可能会加快速度。

Answer 2

不存在的地方不是快速SQL。两个选择的结合如何，在一个选择不同的？我没有一个postgresql实例来测试这一点，但可能是这样的:插入节点(节点、geom、geom)选择DISTINCT on (节点)，geom1，geom2 FROM (选择源作为节点，地理( ST_Transform(geom_source,4326))作为geom1，geom_source作为geom2从view_topo中选择目标作为节点，地理( ST_Transform(geom_target,4326))作为geom1，geom_target作为geom2 ( view_topo) )