Add more geography functions

[vogg]

When working with the GEOGRAPHY type, there are still many functions missing, compared to the GEOMETRY type. This makes working with GEOGRAPHY a lot more cumbersome than it needs to be.

While it is clear that some functions require extra care, we should at least provide proper support for the "trivial" functions which are merely packing and unpacking data structures.

In a real-world codebase, I found myself implementing the following helper functions. Those already go a long way, and motivated coworkers to use proper GEOGRAPHY where they otherwise would have avoided all that trouble:

create or replace function ad_internal.ST_MakeLine(geog1 geography, geog2 geography) returns geography immutable language sql as $$
  select ST_MakeLine(geog1::geometry, geog2::geometry)::geography
$$;

create or replace function ad_internal.ST_StartPoint(line geography) returns geography immutable language sql as $$
  select ST_StartPoint(line::geometry)::geography
$$;

create or replace function ad_internal.ST_EndPoint(line geography) returns geography immutable language sql as $$
  select ST_EndPoint(line::geometry)::geography
$$;

create or replace function ad_internal.ST_Collect(g1 geography, g2 geography) returns geography immutable language sql as $$
  select ST_Collect(g1::geometry, g2::geometry)::geography
$$;

create or replace function ad_internal.ST_Points(geog geography) returns geography immutable language sql as $$
  select ST_Points(geog::geometry)::geography
$$;

In addition, I propose the following addition to ST_Azimuth(), which allows us to treat a 2-point-line as vector and calculating its azimuth (it may be open to debate whether this function should be restricted to 1-point lines, or should allow for any line string, but taking the vector only from the first point directly to the last point):

create or replace function ad_internal.ST_Azimuth(line geography) returns float immutable language sql as $$
  select ST_Azimuth(ad_internal.ST_StartPoint(line), ad_internal.ST_EndPoint(line))
$$;

Note that all implementations are pure SQL. Is this okay, or are those functions are only acceptable when provided as low-level C/C++ functions?

[vogg]

There a typo in the description (I don't seem to have permission to edit it):

"… restricted to 1-point lines …"

This was of course meant to be:

"… restricted to 2-point lines …"

[robe]

@vogg,

Sadly I'm going to have to dismiss this as a wontfix.

The reason we didn't bother with the trivial functions for geography is because it would balloon up our function list and would also require adding additional overloads to prevent the

"ambiguous function"

issue when people use text.

That said there is a feature in PostgreSQL mentioned in #postgis irc channel (by RhodiumToad), I forget the date, that would possibly allow us to better control auto casting in functions for geometry and geography. This was discussed in IRC a while back. That we could take advantage of the category feature of type - ​https://www.postgresql.org/docs/12/sql-createtype.html

Unfortunately I think it might need us to redefine as I don't think there is an alter type for that.

which would allow reuse of geometry functions in geography without having to create parallel versions of them.