Disambiguate overlapping lines in SQL Server spatial or OpenLayers?

Question

I have records in a table representing bus routes, with SQL Server spatial columns for the actual route geometry. (They're stored in a Geography column type.)

I use OpenLayers to display these bus routes on top of an OpenStreetMap layer.

In instances where the bus routes overlap, currently you cannot see anything but the top route. I've tried using transparency on the lines, but it doesn't look that great.

Is there a way, either in OpenLayers or SQL Server spatial, to take overlapping lines and shift them slightly (but keep them parallel) in order to have them all visible? If nothing is built in to handle this, is there a standard algorithm to accomplish this?

Answer 1

SQL Server Spatial Tools has a method, ShiftGeometry, that will shift a geometry (not geography) over by a given X and Y amount.

So, you could, for each successive geography:

1. convert to geometry
2. break the multi-line strings down to lines
3. buffer and intersect with all previously processed lines
4. shift intersecting lines over by slope-appropriate X & Y amounts
5. munge the corners back together (??)
6. reconstitute for display

Alternatively, rather than shift lines over, fatten them up:

1. buffer enough to detect intersects
2. intersect w/ previously processed shapes
3. buffer again, enough to be visible as a fatter shape beneath the previous one (--how much??)
4. union with original shape
5. display w/ layers.

Looks expensive!

OpenLayers may have something for this. If not, it may have better primitives for accomplishing it. On SQL Server, it's going to be very CPU intensive. Intersects aren't cheap, and you will very quickly need to perform a lot of intersects.