After trying several ways of selecting those lines, I can't seem to find any kind of solution. So, I have two tables
- network : lines forming a network containing source (id of node) and target (id of node).
- nodes : the network nodes.
- cities : well, cities... contains also the id of the closest node.
For now, the query I'm building calculate the shortest way between each cities of a certain importance. But I want to get rid of the ways that are passing near other cities. A picture will probably be clearer (sorry, paint, you know...) :
Black dots are nodes affected to a city, and red is a buffer of x metres. Basically, I need a way to keep the blue one and the orange one and get rid of the green one. The blue and orange lines connects two city nodes, the green one connects two city nodes as well but also cross the buffer of an other city.
I've tried several ways but it just won't return the right results. dijkstra temp table contains merged lines according to source and target nodes. citynodesbuffer contains buffers around nodes that are associated with a city.
CREATE TABLE graphtest AS (
WITH dijkstra AS (SELECT DISTINCT start_vid, end_vid, (ST_Dump(ST_LineMerge(ST_Collect(net.geom)))).geom
FROM pgr_dijkstra('SELECT id, source, target, cost FROM network',
array(SELECT id_node FROM network_cities WHERE importance < 2),
array(SELECT id_node FROM network_cities WHERE importance < 2), FALSE) as dij
INNER JOIN network net
ON dij.edge = net.id
GROUP BY start_vid, end_vid),
citynodesbuffer AS (SELECT ST_Buffer(nd.geom, 0.020) as geom
FROM network_nodes nd, network_cities ct
WHERE nd.id = ct.id_node)
SELECT DISTINCT dij.start_vid, dij.end_vid, ST_Difference(dij.geom, cn.geom) FROM dijkstra dij, citynodesbuffer cn
WHERE GeometryType(ST_Difference(dij.geom, cn.geom)) = 'LINESTRING');
This query, apart from being a waste of resources, returns all input lines, just like if ST_Difference didn't make any... differences.
An other.
CREATE TABLE graphtest AS (
WITH dijkstra AS (SELECT DISTINCT start_vid, end_vid, (ST_Dump(ST_LineMerge(ST_Collect(net.geom)))).geom
FROM pgr_dijkstra('SELECT id, source, target, cost FROM network',
array(SELECT id_node FROM network_cities WHERE importance < 2),
array(SELECT id_node FROM network_cities WHERE importance < 2), FALSE) as dij
INNER JOIN network net
ON dij.edge = net.id
GROUP BY start_vid, end_vid),
citynodesbuffer AS (SELECT ST_Buffer(nd.geom, 0.020) as geom
FROM network_nodes nd, network_cities ct
WHERE nd.id = ct.id_node)
SELECT DISTINCT dij.start_vid, dij.end_vid, ST_Difference(dij.geom, cn.geom) FROM dijkstra dij, citynodesbuffer cn
WHERE (ST_Intersects(ST_StartPoint(dij.geom), cn.geom) AND ST_Intersects(ST_EndPoint(dij.geom), cn.geom))
--the line above is not really useful
AND NOT EXISTS (SELECT FROM citynodesbuffer cn2 WHERE ST_Crosses(dij.geom, cn2.geom)));
Worst, this one returns nothing, I guess this is because of ST_Crosses which eliminate every lines but I had hope concerning this function.