I am using some legacy code which requires my data in PostgreSQL box type e.g. this format (37357,50073),(34242,357).

However, I am using PostGIS with box2df i.e. I get: BOX2DF(588893 4515997, 600342 4517442.5). Is there a PostGIS/PostgreSQL function which can transform my data from the BOX2DF format to the BOX format?

I cannot simply do a type cast:

ERROR: cannot cast type box2df to box


It looks like the output (convert-to-text) function is defined for the BOX2DF internal type, so you should be able to convert it to text and then to BOX2D with string manipulation:

SELECT replace(mybox2df::text, '2DF', '')::box2d

To get the native PostgreSQL box type, I think you'd need to break out some regular expressions:

CREATE OR REPLACE FUNCTION box2df_to_box (box2df) 
DECLARE matches text[];
  SELECT array_agg(regexp_matches[1]) FROM (SELECT regexp_matches($1::text, '\d+[.]?\d*', 'g')) sq INTO matches;
  RETURN box(point(matches[2]::float, matches[3]::float), point(matches[4]::float, matches[5]::float));
  • Thanks. Then the transformed output is BOX(588893 4515997,600342 4517442.5) which is still not exactly like the original box type. Not sure if this is right. – audlift-bit Apr 17 '17 at 13:49
  • My bad, I assumed you meant the PostGIS box type, not the PostgreSQL box type. – dbaston Apr 17 '17 at 13:52
  • @audlift-bit Edited w/code to get native PostgreSQL box. – dbaston Apr 17 '17 at 14:07

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.