Thursday, April 26, 2012 11:55 AM
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 3:52 PMAnswerer
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/