0

I'm creating isolines (isochrones) in Carto to show 15, 30, 45, and 60 minute drive times from a set of points in a shapefile. Here is the SQL I've written to create those isochrones in an empty table. But I'd also like to populate a field with the corresponding drive time, so 15, 30, 45, or 60. Here is my SQL:

INSERT INTO <my_empty_table> (the_geom, point_name, latitude, longitude, drive_time) 
    SELECT (cdb_isochrone(the_geom, 'car', ARRAY[900,1800,2700,3600]::integer[], ARRAY['is_destination=true','mode_traffic=enabled','quality=1']::text[])).the_geom, 
      point_name, latitude, longitude, ??????
      FROM <my_points_table>

This SQL (minus my new "drive_time" field) works, because I've successfully run it. But what I'm struggling with now is what to write in place of the ?????? so that I can populate the new drive_time field with the corresponding drive_time that's being calculated in ARRAY[900,1800,2700,3600]. (The isochrone array is in seconds, which is why my minutes are multiplied by seconds to get 900, 2700, etc., although for the drive_time field I'd like them to simply read 15, 30, 45, 60).

I'm thinking it's something like XXX.drive_time, but I'm not sure what XXX should be. I tried (ARRAY[15,30,45,60]::integer[]).drive_time but got the error: Error in SQL query: column notation .drive_time applied to type integer[], which is not a composite type, so I obviously do not know what I'm doing.

For reference, here are Carto's isoline functions:
https://carto.com/developers/data-services-api/reference/#isoline-functions

1

You would need to extract the_geom and distance range from the isochrone function (= data_range) and transform the seconds to minutes.

For example:

WITH isochrone_result AS (
SELECT  my_points_table.longitude, my_points_table.latitude, iso.the_geom, iso.data_range / 60 as drive_time
    FROM  (
             SELECT the_geom, ST_X(the_geom) as longitude, ST_Y(the_geom) as latitude 
              FROM my_points_table
      ) my_points_table,
        cdb_isochrone(
              my_points_table.the_geom, 
              'car', 
               ARRAY[900,1800,2700,3600]::integer[], 
               ARRAY['is_destination=true','mode_traffic=enabled','quality=1']::text[]
        ) as iso
)
INSERT INTO my_empty_table(the_geom, latitude, longitude, drive_time) 
SELECT the_geom, latitude, longitude, drive_time FROM isochrone_result
| improve this answer | |

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.