如何进行单行结果查询以返回多行
如何进行单行结果查询以返回多行
提问于 2015-01-19 13:01:29
下面是SQL查询，它在1000米的距离内沿着一条路线选择了一组加油站。为了用米来测量，我把几何图形转换成了地理。
我还使用SRID 4326作为Linestring的给定路由。
在SQL语句中，我从站点获取1个点并搜索路由网络中最接近的点，但是我希望从WITH查询中对所有站点执行此操作。
WITH stations AS (SELECT id, geom FROM de_tt_stations AS s
WHERE ST_DWithin(s.geom::geography, ST_FromEWKB(%(route)s)::geography, 1000))
SELECT 1378549, destination
FROM (
SELECT id::integer AS destination
FROM de_2po_vertex ORDER BY geom_vertex <-> (
SELECT geom FROM stations LIMIT 1)
LIMIT 1)
AS foo从结果列表中，我想要计算每条路线的长度，这样我就可以选择最近的加油站。最优输出看起来像
start | destination | station_id | length
--------------------------------------------------------------
1378549 | de_2po_vertex.id | stations.id | SUM(pgr_dijkstra())
...我使用的是PostgreSQL 9.5，PostGIS2.1.5和p注浆2.0.0
表定义：
public.de_2po_4pgr
(
id integer NOT NULL,
osm_id bigint,
osm_name character varying,
osm_meta character varying,
osm_source_id bigint,
osm_target_id bigint,
clazz integer,
flags integer,
source integer,
target integer,
km double precision,
kmh integer,
cost double precision,
reverse_cost double precision,
x1 double precision,
y1 double precision,
x2 double precision,
y2 double precision,
geom_way geometry(LineString,4326),
CONSTRAINT pkey_de_2po_4pgr PRIMARY KEY (id)
)
public.de_2po_vertex
(
id integer NOT NULL,
clazz integer,
osm_id bigint,
osm_name character varying,
ref_count integer,
restrictions character varying,
geom_vertex geometry(Point,4326),
CONSTRAINT pkey_de_2po_vertex PRIMARY KEY (id)
)
public.de_tt_stations
(
id character varying(255) NOT NULL,
name character varying(255),
brand character varying(255),
street character varying(255),
"number" character varying(255),
zip character varying(255),
city character varying(255),
premium_e5_time timestamp without time zone,
premium_e5_value double precision,
premium_e10_time timestamp without time zone,
premium_e10_value double precision,
diesel_time timestamp without time zone,
diesel_value double precision,
holiday_identifier character varying(255),
opening_times text,
opening_times_extended text,
override_opening_times text,
geom geometry(Point,4326),
CONSTRAINT de_tt_stations_id_pkey PRIMARY KEY (id)
)回答 1
Database Administration用户
回答已采纳
发布于 2015-01-19 16:19:02
解决我发现的问题的一个可能的解决方案是一步一步的解决方案，如下所示：
BEGIN;
CREATE TEMP TABLE route ON COMMIT DROP AS
SELECT seq, source, target, km, kmh, clazz, geom_way
FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex
WHERE id = 1362258 OR id = 1625523 LIMIT 1) as box WHERE geom_way && box.box',
1362258, 1625523, FALSE, FALSE) AS route
LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id
ORDER BY seq;
CREATE TEMP TABLE filling (start integer, destination integer, station_id character varying(255), distance double precision) ON COMMIT DROP;
INSERT INTO filling (start, station_id)
SELECT 1378549, id FROM de_tt_stations AS s WHERE ST_DWithin(s.geom::geography, (SELECT ST_LineMerge(ST_union(geom_way))::geography FROM route), 1000);
UPDATE filling SET destination =
(SELECT id::integer FROM de_2po_vertex ORDER BY geom_vertex <->
(SELECT geom FROM de_tt_stations WHERE id = filling.station_id)
LIMIT 1);
WITH f AS (SELECT start, destination FROM filling)
UPDATE filling SET distance = (SELECT SUM(km) AS distance FROM (
SELECT km FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr,
(SELECT ST_Expand(ST_Extent(geom_vertex),0.05) as box FROM de_2po_vertex WHERE id = '|| filling.start ||' OR id = '|| filling.destination ||' LIMIT 1) as box
WHERE geom_way && box.box', filling.start, filling.destination, FALSE, FALSE) AS route
LEFT JOIN de_2po_4pgr AS info ON route.id2 = info.id) as dist);
SELECT * FROM filling ORDER BY distance;
COMMIT;执行时间是公平的，大约500毫秒和更少。是否有可能进一步优化Querys？
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/89628
复制相关文章
相似问题
腾讯云开发者
