1

I have a table with a column that contains MULTILINESTRINGs, where every string contains the longitude and latitude of a starting- and an end point, e.g.:

MULTILINESTRING((97.0253024 4.3685491,8.5618685 47.4524688))

Now I want to add geometry to those lines and make them curved so that I get an "arc" between the starting- and end point when I map them. What I have tried is the following:

UPDATE table_name SET the_geom = ST_Transform(ST_GeomFromText(column_with_multilinestrings, 4326), 953027)

Shouldn't this change the reference system from 4326 to 953027? It somehow doesn't work for me, I only get the following error:

Geometry SRID (953027) does not match column SRID (4326).

I already checked this post and the links in it. I also had a look at the cartodb tutorial for making curved lines, but everything I have seen so far concentrates on making curved lines/arcs from a centre point to other points (i.e. A -> B, A -> C, A -> D, etc.). The problem I have, is that I don't know how to draw those arcs from A -> B, C -> D, E -> F, etc.

What am I doing wrong? How can I transform these multilines to curved lines (i.e. arcs)?

John's comment on what ST_Segmentize actually does (and that everything gets converted back to 4326), led to the following SQL statement working to convert the MULTILINESTRINGs (in the_geom) to curved lines:

UPDATE table_name SET the_geom = (SELECT ST_Transform( ST_Segmentize( ST_Transform(table_name.the_geom, 953027),100000),4326))

  • Cartodb uses 4326 for uploaded data and has a hidden column, the_geom_webmercator, used for visualization, as all of the backdrops are in web mercator (3857). I am not sure what 953027 has to do with anything. – John Powell Aug 14 '15 at 8:20
  • OK, I've looked at that video now. You will see there is an outer ST_Transform which converts everything back to 4326 again, as they are working in 953027 to do the ST_Segmentize part, so that the lines appear curved, but the output is 4326. Please post your complete SQL statement. – John Powell Aug 14 '15 at 8:32
  • John, man, thank you so much for clarifying what the ST_Segmentize part actually does. The SQL statement in the UPDATE of my question worked. Greetings to Barça, the coolest city in the world! :) – user56591 Aug 14 '15 at 10:46
  • John, I think the credits for an answer should go to you, as you clarified what was not clear to me. So please feel free to write an answer... – user56591 Aug 14 '15 at 10:49
  • I am English but have been in Barcelona for 10 years. I'll write it up later as out now. Thanks and glad it worked – John Powell Aug 14 '15 at 12:32
2

In CartoDB, when you upload data, it must be in lat/lon, 4326, and this gets stored in a column called the_geom. An invisible column (in the GUI, at least) is also created called the_geom_webmercator, 3857, and this is what is actually used to display data, as all of the in-built wmts backdrops are in this projection.

In the CartoDB tutorial you reference, you will note that there is an outer call to ST_Transform that sets the resulting geometry back to 4326 that is used for the update, while the spatial reference 953207, which I believe is in meters -- actually I can find no reference to it in http://spatialreference.org/ -- is used inside the query as part of ST_Segmentize, which is the function that actually does the work of creating your nice curved (Multi)Linestrings, by breaking up the line into equal segment lengths (in the input SRID), which then appear curved when plotted on Web Mercator.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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