What I am trying to do is create a table contains intersections at the location where each road intersects Roadname 1 & Roadname 2 as well as the geometry spatial data for the intersection ST_Intersection(a.geom, b.geom)

But it seems it doesn't work out l. I am pretty new to PostgreSQL, and I am wondering if there is a way to do so? The following is what I have so far.

Create Table roads(
    intid_ integer,
    muni text,
    alias_ text,
    roadnames text,
    geom geometry);

Insert into roads (roadnames, geom, intid_, alias_)
    trim(student.sc_e911slrn.prefix) || trim(student.sc_e911slrn.name) || trim(student.sc_e911slrn.suffix), 
    geom, objectid, alias
FROM student.sc_e911slrn;


CREATE type student.inter AS (
     roadnames text,
        intid_ int,
        alias_ text,
        muni text,
        ST_Intersection geometry,
        geom geometry);

--< Cursor >--------------------------------------------------------
Create function create_intersections()
    /*returns table student.intersect_roads As $method1$*/
    RETURNS table (
        roadnames text,
        intid_ int,
        alias_ text,
        muni text,
        ST_Intersection geometry,
        geom geometry) As $$

    return_intersect inter;
    rec_road Record;
    cur_road Cursor()
        For Select intid_, geom, roadnames
        From student.roads;

    Open cur_road();

        fetch cur_road into rec_road;
        exit when not found;

        If ST_Touches(rec_road.geom, student.roads.geom) AND
            rec_road.geom != student.roads.geom AND 
            rec_road.intid_ != student.roads.intid_

        Then Update student.intersect_road
             Set st_intersection = ST_Intersection (rec_road.geom, student.roads.geom),
                 intid_ = rec_road.intid_,
                 intersections = rec_road.roadnames|| ' & ' || student.raods.roadnames,
                 alias_ = rec_road.alias_,
                 /*muni = */
                 polyline = ST_Union(rec_road.geom, student.roads.geom)
                 RETURNING * into return_intersect;
                 RETURN NEXT;
        End if;
    End Loop;
    Close cur_road;


$$ Language plpgsql;

--< return the table>----------------------------------------------
SELECT create_intersections(); 

The Roadname 1 & Roadname 2 should be generated from roadnames column from ROADS table, and the geometry for the intersection ST_Intersection(a.geom, b.geom) should be generated from the geom column from ROADS table. When I run it, it returns no result.

The table ROADS is something like this road table,(geometry view):

Intid_     Muni    Alias_           Roadnames            geom
24581722   NULL    CR 52       Coopers Falls road        ...
24581723   NULL    CR 44       Rama road                 ...

Expected output should be similar to this one, but we need other fields to be filled as well.--->table (geometry view)

I also tried

CREATE FUNCTION create_intersections() 
     UPDATE student.intersect_roads AS $$

Which is not working as well.

  • One issue I can see is that you're using != to compare geometries. You need to use 'not st_equals(rec_road.geom,student.roads.geom)' instead. There is also a typo near 'Then Update...' - it says student.raods.roadnames – jbalk Mar 11 at 17:53
  • Do I understand you right that you have a road coverage and everywhere two roads are intersecting you basically want a point with the two road names? – MortenSickel Mar 13 at 14:46

Your Answer

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

Browse other questions tagged or ask your own question.