0

Carto.com: We are mapping homicides and when two deaths occur at the same location, how do I prevent 2 points with the same lat, lon from overwriting each other? I want to be able to view each point and the associated data.

  • Using a SQL query to detect the points that are equal and moving one slightly? – John Powell Jul 22 '16 at 8:43
2

There are several alternatives using CARTO to solve the points-overlapping problem. First, if you have a timestamp column and the deaths have happened in two different dates, you can use Torque. That is an animated map. Second, the Wizard of the old Editor allow you to aggregate points in clusters, intensity or density maps.

But my favorite example is using the stacking chip method (sorry for the old blog post). This is basically a hack using PostGIS functions:

WITH 
  m AS (
    SELECT array_agg(cartodb_id) id_list, the_geom_webmercator, ST_Y(the_geom_webmercator) y 
    FROM table_name
    GROUP BY the_geom_webmercator 
    ORDER BY y DESC
  ),
  f AS (
    SELECT  generate_series(1, array_length(id_list,1)) p, unnest(id_list) cartodb_id, the_geom_webmercator 
    FROM m
  )
SELECT  ST_Translate(f.the_geom_webmercator,0,f.p*50) the_geom_webmercator, f.cartodb_id, q.city, q.country
FROM f, table_name q
WHERE f.cartodb_id = q.cartodb_id

Here you can find a working example using CARTO.js.

| improve this answer | |
  • That's a very cool approach. – John Powell Jul 22 '16 at 9:44
  • Excellent, that is exactly what we needed. Cool approach indeed. – Steve Jul 22 '16 at 19:33

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.