SQLQueryCollection - UCL-ShippingGroup/shipviz GitHub Wiki

Training files to be imported as datasets

  • cartodb_minihourly.csv provides data points of a vessel's trajectory, imported colomns are:

    • cartodb_id (row number, created on data import by CartoDB)
    • the_geom (lon/lat pairs, created on data import by CartoDB)
    • eu_trip (boolean, data point belongs to a trip from/to EU)
    • identifier (number, unique vessel identifier)
    • latitude (number, vessel lat-location)
    • longitude (number, vessel lon-location)
    • speed (number, vessel speed)
    • time (date, reporting time)
    • total_co2(number, emitted co2 within that hour)

  • cartodb_minivessel.csv provides static data about vessels, imported columns are:

    • cartodb_id (row number, created on data import by CartoDB)
    • the_geom (here null)
    • capacity (number, in cubic metres)
    • dwt (number, in tonnes)
    • identifier (number, unique vessel identifier, same as in cartodb_minihourly)
    • teu (number, in twenty-foot equivalent units)
    • vessel_group (string, vessel group)
    • was_eu (boolean, vessel did at least one EU trip in that year)

  • ecas_no_continent.zip provides the geometry of ECAs (emission control areas). Note that this version has minor inaccuracies along the coast line. Imported columns are:

    • cartodb_id (row number, created on data import by CartoDB)
    • the_geom (polygon, created on data import by CartoDB)
    • eca (string, name of ECA)

Vessel tracks filtered by non-geospatial criteria (from separate table/dataset)

For this query we are using the tables cartodb_minihourly and cartodb_minivessel. The aim is to filter out all vessel tracks according to some static criterion, here deadweight tonnes.

SELECT * FROM 
	(SELECT * FROM cartodb_minihourly) AS mh,
	(SELECT identifier AS id, vessel_group, dwt, teu, capacity, was_eu 
	FROM cartodb_minivessel
    	WHERE dwt <= 1200) AS mv  	
WHERE mh.identifier=mv.id

Vessel tracks filtered by geospatial criteria (e.g. shapefile)

For this query we are using the table cartodb_minihourly and ecas_no_continent.

SELECT * FROM 
	(SELECT * FROM cartodb_minihourly) AS mh,
	(SELECT the_geom_webmercator AS gwm FROM ecas_no_continent
     	WHERE eca = 'North_Sea'
     	OR eca = 'Baltic_Sea') AS shp
WHERE ST_WITHIN(mh.the_geom_webmercator, shp.gwm)

Create buffer around shapefile

For this query we are using the table ecas_no_continent.

SELECT cartodb_id,
ST_Transform(ST_Buffer(the_geom,1), 3857) as the_geom_webmercator
FROM ecas_no_continent

Make lines out of points

See here

Change column data type

The data type of a column can be changed either in the data view (entire dataset) by clicking on the drop-down menu below the column name or by writing an SQL query. See a few examples below, unfortunately it is not possible to run more than one query at once.

ALTER TABLE cartodb_minihourly 
    ALTER COLUMN "draught" TYPE double precision using draught::double precision;
ALTER TABLE cartodb_minihourly
    ALTER COLUMN "imo" TYPE integer using imo::integer;
ALTER TABLE cartodb_minihourly 
    ALTER COLUMN "time" TYPE timestamp without time zone USING to_date(time, 'YYYY-MM-DD HH24:MI:SS');

Insert column

A column can be inserted either by clicking the 'add colunm' field in the data view (bottom right) or by SQL query. The latter can be advantageous if the data type is not supported in the drop-down menu. For example, a number will always be cast as double precision, however it is possible to have integers as well (in the underlying postgres table, cartodb_id is handled as an integer).

ALTER TABLE cartodb_minihourly
    ADD COLUMN eu_trip boolean

Create arc lines from one point to another.

In my case, I had a table with polygons, not points, so I needed to find the center of the polygon first and use that as start of the line. This will change the dataset at CartoDB FOREVER, so make a duplicate first. Then run this query:

UPDATE table SET the_geom = ST_Centroid(table.the_geom)

Check the map and see the polygons are gone and you only have points.

We need a spatial reference system where the arc segments will be based on. For this, we need to run this in any CartoDB SQL editor:

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"]]');

Then choose a central point where you want all the points to link to, and execute this query:

UPDATE table
SET the_geom =
   ST_Transform(
     ST_Segmentize(
       ST_MakeLine(
         ST_Transform(table.the_geom, 953027),
         ST_Transform(CDB_LatLng(central_lat, central_lon), 953027)
       ), 
       100000
     ), 
    4326 
   )

This will create a map similar to the following one, with all lines starting in the center of the polygons and going to the chosen central location. This can them be styled with CartoDB's visualisation options for line features.