Here's the problem : I have two tables, one with roads, the other with points (which are startpoints and endpoints of each road sections, without duplicates).
I want to extract only points that have 1, 3 or more different road sections intersecting.
1 = edge of the network
2 = unecessary nodes between two lines (to erase)
3 and more = network junction
I need those points because of their importance in the network, here's a picture to illustrate the issue :
Basically, I need a PostGIS query that will return point 1 and 3 and leave point 2.
Edit 1 (poor optimization):
DPSSpatial's solution worked fine, even with ST_DWithin. Here's the code :
CREATE TABLE "ReseauRoutier".highways_nodes_clean AS ( SELECT * FROM (SELECT nd.gid, count( CASE WHEN ST_DWithin(nd.geom, sg.geom, 0) THEN 1 END) AS count_roads, nd.geom FROM "ReseauRoutier".highways_nodes_test nd, "ReseauRoutier".highways_segments_test sg GROUP BY nd.gid) AS counts WHERE counts.count_roads > 2);
I think ST_DWithin using a tolerance of 0 may not be appropriate though and might affect the process time on larger datasets...
Edit 2 (also poor optimization):
Just a quick update to show my current code with the ST_Intersects function. Process time is now reduced by half.
CREATE TABLE "ReseauRoutier".highways_nodes_clean AS ( WITH counts AS ( SELECT nd.gid, count(CASE WHEN ST_Intersects(nd.geom, sg.geom) THEN 1 END) AS count_roads, nd.geom FROM "ReseauRoutier".highways_nodes nd, "ReseauRoutier".highways_segments sg GROUP BY nd.gid) SELECT * FROM counts WHERE counts.count_roads != 2);
I decided to keep nodes that have only 1 intersecting road as they represent the network's edges.
Edit 3 (great optimization):
I had to edit this post as I think the following is the best solution for this issue. Using ST_Intersects inside the CASE will force PostGIS to test every lines for every nodes, I soon hit the temp file limitation when I used it on large dataset. Thus, my co worker came up with the join idea.
CREATE TABLE nodes_clean AS ( WITH joined AS (SELECT n.id AS node_id, count(w.id), n.geom FROM nodes n INNER JOIN ways w ON ST_Intersects(n.geom, w.geom) GROUP BY node_id, n.geom) SELECT * FROM joined j WHERE count != 2)
That request takes 1.5 sec instead of 50 sec, which is quite an improvement.