Node to Node Connectivity

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.

 

thanks

Answer 1

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/