Selecting points having 1, 3 or more lines intersecting using PostGIS

Question

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.

Answer 1

Using our SCHOOLS and CITY ROADS, I can select schools that have 3 road segments within a distance of the school point.

In the sub-query, I'm querying two tables, but not joining them in the FROM statement.

I'm asking for the SCHNUM, and a COUNT of the roads that intersect the 200' buffer of the school. Note I'm transforming both geometries to STATE PLANE (feet) so I can create the buffer in feet.

I've added a where clause of only 1 school to test, but you would leave this out.

Then finally wrap the subquery in a SELECT and alias the subquery as COUNTS, then you can filter records from the COUNTS subquery where COUNTS.COUNT_ROADS = 3, ie. show me schools that have 3 road segments that touch the 200' buffer.

SELECT *
    FROM (

           SELECT
             sch.schnum
             , count(CASE WHEN ST_Intersects(ST_Transform(st.geom, 2877), ST_Buffer(ST_Transform(sch.geom, 2877), 200))
             THEN 1 END) AS count_roads

           FROM dpsdata."Schools_Current" AS sch
             , ccd."CCD_StreetCenterlines" AS st

           WHERE schnum = '215'

           GROUP BY sch.schnum

         ) AS counts

    WHERE counts.count_roads = '3'

Maybe the ST_DWithin function would work better here, but I've not tried it with lines before...