Find out how to plot only the vessels crossing ECA areas.

[raquelalegre]

At the moment I have two layers in my local CartoDB instance:

• vessels5000K: contains 5 million points from vessels in an 8-month subset of the 2012 dataset.
• us_eca_no_continents: contains the polygons describing the US ECA areas prepared as per #17.

For the ECA areas use case, we need to find out which vessels have been in the ECA area, but in CartoDB it doesn't seem very straight forward to do a query that involves data from different layers.

Note CartoDB layers can only hold data from a single table and a single geometry, so points and polygons can't be plotted in the same layer, AFAIK.

[raquelalegre]

Here's an example query done in CartoDB plotting in the map all the points of the vessels that have points inside the Hawaiian Islands ECA from April to July 2012:

SELECT * FROM vessels5000k
WHERE mmsi IN (
    SELECT DISTINCT mmsi
    FROM vessels5000k, us_eca_no_continent
    WHERE 
        ST_WITHIN(vessels5000k.the_geom_webmercator, us_eca_no_continent.the_geom_webmercator)
        AND 
        us_eca_no_continent.first_fld LIKE '%Hawaiian Islands%'
        AND vessels5000k.time >= '2012-04-01' AND vessels5000k.time <= '2012-07-31'
)

The Hawaiian Islands area is in purple. The query returns the path of three vessels:

Note tiling is not working properly when zooming out since the DB is huge, but zooming in displays all points.

Is this the kind of thing you need for the ECA areas use case, @juliaschaumeier ?

[raquelalegre]

Julia is happy with this.