3

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 :

enter image description here

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.

  • 1
    Can you tell us what you already tried in postgis? It always helps when you show your effort on it. – tilt Aug 7 '17 at 15:51
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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.