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

Answer 1

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 ...