3

I have data to import to cartoDB in CSV format in which there is a bounding box column holding two lat/long coordinates representing the corners of a bounding box, like

53.113206182344,-9.15629032156; 53.119089697211,-9.141677599209

I need to retain this information as text in its own column, but I also would like to derive a polygon and store its geometry as the_geom. I'm assuming that the polygon geometry would be created post import through a SQL update ... but need guidance in how to approach this.

2

I think the easiest route would be to use ST_MakeEnvelope (docs) after doing some basic parsing of your text. I'm not sure if CartoDB lets you create functions or not, but I'd make a little one for this:

CREATE OR REPLACE FUNCTION parse_box (text)
RETURNS geometry AS $$
WITH split AS (SELECT regexp_split_to_array($1, '[^\d.-]+') AS val)
SELECT ST_MakeEnvelope(val[1]::float, 
                       val[2]::float,
                       val[3]::float,
                       val[4]::float) FROM split;
$$ LANGUAGE SQL;

And then:

UPDATE my_table SET geom = parse_box(box);
  • Thanks. CartoDB does allow functions to be created and I use your solution with one change: CREATE OR REPLACE FUNCTION parse_box(text) RETURNS geometry AS $$ WITH split AS (SELECT regexp_split_to_array($1, '[^\d.-]+') AS val) SELECT ST_MakeEnvelope(val[2]::float, val[1]::float, val[4]::float, val[3]::float,4326) FROM split; $$ LANGUAGE SQL; then UPDATE table SET the_geom=parse_box(bbox) – user1461794 Dec 19 '15 at 14:28

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.