# Creating weighted lines on map in CartoDB

I am trying to illustrate global trade flows within CartoDb. Flows are visualized by a line connecting two lat/long points on a map. The size of the flow would be represented by the width of the line. I have a lat/long and trade magnitude file but cannot figure out, 1) how to get the points to connect in CartoDb using PostGIS functions and 2) how to adjust the line width based on the value using CartoCSS.

Pasted below is the head of the data:

``````id  start_lat   start_lon   end_lat     end_lon         Value
1   32.89595056 -97.0372    35.04022222 -106.6091944    100
2   41.979595   -87.9044641 30.19453278 -97.66987194    500
3   32.89595056 -97.0372    41.93887417 -72.68322833    750
4   18.43941667 -66.0018333 41.93887417 -72.68322833    1200
``````

Here's an example of the type of visualization I am trying to make. More or less it is a spatial sankey diagram: from 2012 GEA Global Energy Assessment

In order to create lines from coordinates, you have to run the following query in your SQL console:

``````SELECT
a.value,
st_transform(
st_makeline(
ST_SetSRID(st_makepoint(a.start_lon, a.start_lat),4326),
ST_SetSRID(st_makepoint(b.end_lon, b.end_lat),4326)
),
3857) as the_geom_webmercator
FROM
points a,
points b
WHERE
a.cartodb_id = b.cartodb_id
``````

I am going to explain the query step by step:

1. The `st_makepoint` function creates the starting and ending points.
2. The `st_srid` function sets the points to the Mercator projection (`4326`).
3. The `st_transform` function transform the geometry from Mercator into Web Mercator projection.
4. The `a.cartodb_id = b.cartodb_id` condition is a trick to connect the points from the same row.

Finally, you need to set the `line-width` CartoCSS property according to your `value` column. You need to use something like this:

``````#layer {
line-color: #FFFFFF;
line-opacity: 0.7;
}

#layer[value=100] {
line-width: 2;
}
#layer[value=500] {
line-width: 5;
}
#layer[value=750] {
line-width: 7;
}
#layer[value=1200] {
line-width: 10;
}
``````

The resulting map will be similar to this one. More about styling maps based on table values here.

If your lines cross the Date Line then you need to make a fairly more complicated query. Check this awesome blog post to learn more about this problem and how to solve it.

• How would one also add a tip to the line so it is an arrow and make it curved? – Danny May 31 '16 at 22:13
• CartoDB is a data analysis and visualization platform, but thanks to the flexibility of CartoCSS properties sometimes you can do some magic. But there are much easier graphic design tools for this last porpuse. – ramiroaznar Jun 1 '16 at 8:03

Following the other answer I was able to draw the lines with curves using the following code:

``````UPDATE flights_1
SET the_geom_webmercator=
(SELECT st_transform(
st_segmentize(
st_makeline(
st_transform(CDB_LatLng(start_lat,start_lon),953027),
st_transform(CDB_LatLng(end_lat,end_lon),953027)
),
100000),
3857))
``````

If you are getting an error message indicating that CartoDb does not recognize 953027 first insert this code to the SQL interface:

``````INSERT into spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext) values ( 953027, 'esri', 53027, '+proj=eqdc +lat_0=0 +lon_0=0 +lat_1=60 +lat_2=60 +x_0=0 +y_0=0 +a=6371000 +b=6371000 +units=m +no_defs ', 'PROJCS["Sphere_Equidistant_Conic",GEOGCS["GCS_Sphere",DATUM["Not_specified_based_on_Authalic_Sphere",SPHEROID["Sphere",6371000,0]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]],PROJECTION["Equidistant_Conic"],PARAMETER["False_Easting",0],PARAMETER["False_Northing",0],PARAMETER["Central_Meridian",0],PARAMETER["Standard_Parallel_1",60],PARAMETER["Standard_Parallel_2",60],PARAMETER["Latitude_Of_Origin",0],UNIT["Meter",1],AUTHORITY["EPSG","53027"]]');
``````
• Well done! That's the way of connecting lines using great circles. I have mentioned this method at the end of my answer. – ramiroaznar Jun 2 '16 at 7:53
{"ft_lang_label":"__label__en","ft_lang_prob":0.8297025,"math_prob":0.8960985,"size":856,"snap":"2019-35-2019-39","text_gpt3_token_len":264,"char_repetition_ratio":0.08450704,"word_repetition_ratio":0.0,"special_character_ratio":0.41238317,"punctuation_ratio":0.13043478,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9774919,"pos_list":[0,1,2],"im_url_duplicate_count":[null,1,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2019-09-21T15:53:32Z\",\"WARC-Record-ID\":\"<urn:uuid:c4725c5a-f149-4904-b9b1-2ad77babc193>\",\"Content-Length\":\"146687\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:47916567-9d9a-4890-9751-37748601c4ab>\",\"WARC-Concurrent-To\":\"<urn:uuid:185bb5ff-6c35-4116-bc31-480dfa4eb077>\",\"WARC-IP-Address\":\"151.101.193.69\",\"WARC-Target-URI\":\"https://gis.stackexchange.com/questions/195457/creating-weighted-lines-on-map-in-cartodb\",\"WARC-Payload-Digest\":\"sha1:5PFZ4JUIDIIEMGBOV75GLKEOAT3RKD4G\",\"WARC-Block-Digest\":\"sha1:DI43233TYSESWCZVAUHH4IDM4MVZPEDI\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2019/CC-MAIN-2019-39/CC-MAIN-2019-39_segments_1568514574532.44_warc_CC-MAIN-20190921145904-20190921171904-00094.warc.gz\"}"}