2

I am trying to create a bus route map in Los Angeles. I realize that it is shooting for the moon (perhaps it is impossible to do well) since there are so many overlapping routes, but I think I've gone a long way toward having at least the labels appear for each route.

I have used line-offset: -2; on the Rapid, Limited Express and Circulation routes and it seems to work nicely (it applies the offset to every part of the route), but I still have many overlapping Local routes. Is there a way to use Postgis to identify the overlapping lines and to generate new coordinates, offset from each other? Or, use it to at least identify where lines are overlapping (and then use line-offset: -2; to offset just that little portion?

The Map

In essence, I am trying to create a web-based, interactive version of a classic transit map.

Additional references/resources I've found helpful thus far:

Improve this question

1 Answer 1

Reset to default
1

You can also try to translate your lines via SQL query, by following an example similar to the use case done here with points:

    WITH q AS (SELECT cartodb_id, the_geom_webmercator FROM twain WHERE name = 'New Orleans, LA, US'),
         m AS (SELECT count(*) n, array_agg(cartodb_id) id_list, the_geom_webmercator FROM q GROUP BY the_geom_webmercator),
         f AS (SELECT  n, generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator FROM m)
         SELECT ST_SetSRID(ST_MakePoint(ST_X(f.the_geom_webmercator) + f.p * 180 * cos(f.p * radians(301*ceil(f.n/20.0))/f.n), ST_Y(f.the_geom_webmercator) + f.p*180 * sin(f.p * radians(301*ceil(f.n/20.0))/f.n)),3857) the_geom_webmercator, f.cartodb_id, t.name, t.passage, t.source FROM f, twain t WHERE f.cartodb_id = t.cartodb_id

For example, to apply it to shifting lines I used ST_Translate and reused the generated series from the previous query (by @andrewxhill) in order to apply a shift on the Y axis:

WITH q AS (SELECT cartodb_id, the_geom_webmercator FROM tablename),
m AS (SELECT count(*) n, array_agg(cartodb_id) id_list, the_geom_webmercator FROM q GROUP       BY the_geom_webmercator),
f AS (SELECT  n, generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator FROM m)
SELECT ST_SetSRID(ST_Translate(f.the_geom_webmercator, 0,f.p*10),3857) the_geom_webmercator, f.cartodb_id, t.name FROM f, tablename t WHERE f.cartodb_id = t.cartodb_id

Visually:

From: From To: to

Improve this answer
3
  • Thanks @iriberri. I appreciate your post. It looks really promising. I'll have to do some more SQL and PostGIS learning to digest it all, but it's great.
    – Dave
    Commented Mar 27, 2015 at 4:15
  • In the last query I posted you just need to edit "tablename" by yours to do a small test, but it will require some extra work in order to shift only the ones you need.
    – iriberri
    Commented Mar 27, 2015 at 9:47
  • I know this is an old question, but how can I offset the points dynamically based on zoom level?
    – atlasofcoffee
    Commented Mar 20, 2020 at 0:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.