Selecting lines with start and end points inside polygons but do not cross them with PostGIS

Question

After trying several ways of selecting those lines, I can't seem to find any kind of solution. So, I have two tables

1. network : lines forming a network containing source (id of node) and target (id of node).
2. nodes : the network nodes.
3. 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.

Answer 1

Well, I found an error in my code, I forgot to create a buffer only around cities with an importance < 2. But I also found the right way to select it, by using a join, counting the number of different buffers intersecting each line and selecting only those with up to 2 intersecting buffer. Here's the code :

CREATE TABLE "ReseauRoutier".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 "ReseauRoutier".network',
             array(SELECT id_node FROM "ReseauRoutier".network_cities WHERE importance < 2),
             array(SELECT id_node FROM "ReseauRoutier".network_cities WHERE importance < 2), FALSE) as dij
             INNER JOIN "ReseauRoutier".network net
             ON dij.edge = net.id
        GROUP BY start_vid, end_vid),
citynodesbuffer AS (SELECT nd.id, ST_Buffer(nd.geom, 0.020) as geom
        FROM "ReseauRoutier".network_nodes nd, "ReseauRoutier".network_cities ct
        WHERE nd.id = ct.id_node AND ct.importance < 2)
SELECT dij.start_vid, dij.end_vid, dij.geom, count(cn.id) FROM dijkstra dij
         LEFT OUTER JOIN citynodesbuffer cn
         ON ST_Intersects(dij.geom, cn.geom)
GROUP BY dij.start_vid, dij.end_vid, dij.geom
HAVING count(*) < 3);

Answer 2

St_difference will always return a line in your case, with a "hole" in the city buffers. Your second query returns nothing because each line do intersect with the start/end buffer.

To get a working solution, you can count the line/buffer intersections and keep only the cases with 2 intersections