How to create polygon from bounding box coordinates (PostGIS/cartoDB)

Question

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.

Answer 1

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);