Node to Node Connectivity RRS feed

  • Question

  • Hi,

    i have a table named Roads it has

    Roadtype smallint, RoadID Bigint , StartPoint Geometry, EndPoint Geometry and Geom Geometry Witch stores the polyline Data

    and now i want to Connect the Road to Road Dynamic Connection Withn the Procedure.

    any help how do i Draw the road within seconds.



    Thursday, April 26, 2012 11:55 AM


  • The naive approach is to self-join the table using Roads.StartPoint.STEquals(Roads.EndPoint) = 1

    However, this is not a good idea and certainly won't lead to a high-performance solution. A better approach is to create a separate Nodes table that stores details of each node at which two roads intersect. This will be a single Point geometry, but you should also assign it an integer ID.

    Then, in your Roads table, add two more columns for From_NodeID and To_NodeID which reference the Nodes table, and place indexes on the columns. You can then connect roads together using a join on Roads.To_NodeID = Roads.From_NodeID, which will be more robust and a lot faster.

    twitter: @alastaira blog: http://alastaira.wordpress.com/

    • Proposed as answer by amber zhang Friday, April 27, 2012 1:29 AM
    • Marked as answer by amber zhang Friday, May 4, 2012 4:18 AM
    Thursday, April 26, 2012 3:52 PM