attendee VL map data prep

readme.md

Segments to Madrid

Instructions to create a dataset with lines that go to Madrid, ready to be used with CARTO VL

1. Correct geocodings: locations_attendees

Manually fixed records without geometries with

1. Fix manually the addresses with wrong names
2. Update the table geocoding those records

update locations_attendees  set
the_geom = cdb_geocode_street_point(concat_ws(', ',address_2,address_3)) 
where the_geom is null

2. Group by geometry: locations_points

Generate a new dataset that stores the unique geometries and also the number of attendees

  select the_geom,
         count(1) as num
    from locations_attendees
group by the_geom

3. Create the segments table: locations_segments

With the following query we

1. Generate the great circle that connects each point with the location of Goya Theatre
2. Break those lines into points
3. Join consecutive points
4. Compute the number of points per line and the maximum number
5. Generates an animating_id using each segment identifier and the previous figures

with 
_data as ( select * from locations_points ),
_mad as ( select cdb_latlng(40.40748,-3.72514) as the_geom ),
_lines as (
    select d.cartodb_id, d.num,
            round(st_distance_sphere(d.the_geom,m.the_geom)) as distance,
            st_transform(
            st_segmentize(
                st_makeline(d.the_geom, m.the_geom )::geography,
                10000
                )::geometry,
            3857
            ) the_geom_webmercator
    from _data d, 
        _mad m
),
_dumps as ( select *, ST_DumpPoints(the_geom_webmercator) as dp from _lines),
_points as (
    select distance,
           num,
           cartodb_id as line_id,
           (dp).path[1] as point_id, 
           (dp).geom as the_geom_webmercator
      from _dumps
),
_segments as (
    select distance,
           num,
           line_id,
           point_id,
           ST_MakeLine(
              the_geom_webmercator,
              lead(the_geom_webmercator) over (partition by line_id order by point_id)
           ) as the_geom_webmercator
      from _points
),
_max_point as (select max(point_id) as max_point_id from _segments),
_max_per_line as (select line_id, max(point_id) as max_point_id from _segments group by line_id)
    select row_number() over() as cartodb_id,
           s.*,
           m.max_point_id - l.max_point_id + s.point_id as animating_id
     from _max_point m,
          _segments s  
left join _max_per_line l on s.line_id = l.line_id