1

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

from 2012 GEA Global Energy Assessment

3

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
1

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

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.