0

Using this snippet of code:

INSERT INTO my_new_table 
    (the_geom) SELECT (cdb_isodistance(the_geom, 'walk', 
    string_to_array(distance, ',')::integer[])).the_geom 
FROM my_original_pts_table

I am trying to create isochrones for a table of points. I get an error after applying an INSERT.

Why is the first geom in parenthesis? Is the distance parameter something that will vary from table to table? After this does work, I still have to wrap it in a ST_Transform correct?

  • There are really too many questions here for a focussed answer. First of all, what does the error say? The reason it is in parenthesis is because cdb_isodistance presumably is a set returning function and so the (....).the_geom bit means just return the geometry -- ST_Dump works the same way, if you want to understand this better. – John Powell Apr 4 '18 at 8:41
  • The error I get is Syntax error: relation "my_new_table" does not exist It doesn't exist because I am in the process of creating, right? – atlasofcoffee Apr 5 '18 at 17:15
3

The first the_geom is in parenthesis because it is the proper way to do an INSERT in PostgreSQL. Secondly, the distance parameter is up to the user. You can use a fixed value from a dataset column or something like this if you want just one track:

SELECT (cdb_isodistance(cdb_latlng(41,-3), 'walk', ARRAY[300]::integer[])).the_geom

Or more than one (i.e. three):

SELECT (cdb_isodistance(cdb_latlng(41,-3), 'walk', ARRAY[300,600,900]::integer[])).the_geom

And this is the result if you wrap the first query with ST_AsText:

MULTIPOLYGON(((-2.99880970689654 40.9988826465517,-2.998809 40.998883,-2.997996 40.999345,-2.997987 40.999354,-2.998112 40.999221,-2.99828 40.999043,-2.99880970689654 40.9988826465517)),((-3.00064100000013 40.9983293333333,-3.000817 40.998276,-3.00063 40.998333,-3.00064100000013 40.9983293333333)),((-2.99880970689654 40.9988826465517,-2.998811 40.998882,-2.998848 40.998871,-3.00066 40.998323,-3.00064100000013 40.9983293333333,-3.00052 40.998366,-2.999707 40.998612,-2.998868 40.998865,-2.99880970689654 40.9988826465517)))

And finally, don't need to use ST_Transform.

  • Thanks for your explanation. The error I get is Syntax error: relation "my_new_table" does not exist – atlasofcoffee Apr 5 '18 at 17:12
  • 1
    So, you need to create a new (empty) dataset named "my_new_table". – ramiroaznar Apr 5 '18 at 20:04

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.