Cast polygon to json array and json array to polygon

Question

I've created this two functions, and I want to know if there is a better way to cast json arrays to polygons without having to install the PostGIS extension.

Raw type:

((-34.888733,-57.956764),(-34.92303,-57.99367),(-34.953579,-57.95255))

polygon_to_json:

["-34.888733,-57.956764","-34.92303,-57.99367","-34.953579,-57.95255"]

json_to_polygon:

((-34.888733,-57.956764),(-34.92303,-57.99367),(-34.953579,-57.95255))

create or replace function json_to_polygon(p_poly json, out p_out polygon) returns polygon
parallel safe
returns null on null input
immutable
language plpgsql
as $$
begin
    if(p_poly is null) then
        return;
    end if;
select
    polygon(concat('((', string_agg(x, '),('), '))'))
from
    json_array_elements_text(p_poly) x
into
    p_out;

end;
$$;
create or replace function polygon_to_json(p_poly polygon) returns json
parallel safe
returns null on null input
immutable
language plpgsql
as $$
begin
    if(p_poly is null) then
        return null;
    end if;
return to_json(string_to_array(replace(replace(p_poly::text, '((', ''), '))', ''), '),('));

end;
$$;

Erwin Brandstetter · Accepted Answer · 2024-08-16T02:54:17.007

Yes, there is a better way:

CREATE OR REPLACE FUNCTION json_to_polygon (p_poly json)
  RETURNS polygon
  LANGUAGE sql PARALLEL SAFE STRICT IMMUTABLE
RETURN replace(replace(replace(
            p_poly::text
         , '","', '),(')
         , '["', '((')
         , '"]', '))')::polygon;
CREATE OR REPLACE FUNCTION polygon_to_json (p_poly polygon)
  RETURNS json
  LANGUAGE sql PARALLEL SAFE STRICT IMMUTABLE
RETURN replace(replace(replace(
            p_poly::text
         , '),(', '","')
         , '((' , '["')
         , '))' , '"]')::json;

fiddle

I use the syntax variant for SQL-standard functions (added with Postgres 14. See:
- What does BEGIN ATOMIC mean in a PostgreSQL SQL function / procedure?
Obviously, since both functions are declared STRICT ( = RETURNS NULL ON NULL INPUT), we need not check for null input.
The simple functions can be LANGUAGE sql, so they can be inlined. See:
- How can I find out if a SQL function can be inlined?
concat() is only STABLE, not IMMUTABLE, use the IMMUTABLE concatenation operator || instead. Then declaring the function IMMUTABLE is correct, and also won't stand in the way of function inlining. See:
- How to concatenate columns in a Postgres SELECT?
- Combine two columns and add into one new column
A 3rd replace() should be faster than pulling in string_to_array().
Finally, I make json_to_polygon() the exact reverse of polygon_to_json(), which should again be faster, and easier to read and maintain.

All that said, PostGIS does have some functions to help with that ...

Cast polygon to json array and json array to polygon

1 Answers1