[postgis-devel] An any spatial type that works for geometry / geography

Regina Obe lr at pcorp.us
Sun Feb 5 01:46:48 PST 2023 

I remembered another issue:
Things like ST_StartPoint('LINESTRING(1 1, 2 2)') wouldn't work anymore. If
ST_StartPoint was changed to ST_StartPoint(anyelement)

You'd get an error:

ERROR:  could not determine polymorphic type because input has type unknown

I personally thing people shouldn't be doing things like that anyway so not
sure I much care about that kind of breakage.  But that would definitely be
considered a break.

We've got functions like ST_GeoHash that already don't work with unknowns
because we never bothered to define an ST_GeoHash(text) variant.

So doing this

SELECT ST_GeoHash('POINT(1 2)');

Gets you:

 function ST_GeoHash(unknown) is not unique

I was also thinking about how we would introduce our own anygeo, and at a
glance seems these are pretty baked in and no way to extend that.

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/uti
ls/fmgr/funcapi.c;h=217835d590bedde3cbc1026c6583a66f90761704;hb=HEAD#l858

I was hoping anycompatible might be the answer, but seems more designed for
the case of functions that take two different types, like our raster,
geometry functions and allows one to be coerced to the other.

All polymorpics are described here: 
https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES
-POLYMORPHIC


I was hoping anycompatible would do the trick to allow unknowns, but it has
warts.

e.g. 

For example if I define a function:

CREATE OR REPLACE FUNCTION test_st_startpoint(
	anycompatible)
    RETURNS anycompatible
    LANGUAGE 'c'
    COST 50
    IMMUTABLE STRICT PARALLEL SAFE 
AS '$libdir/postgis-3', 'LWGEOM_startpoint_linestring';


I can do this:

SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::geometry); -> geometry

If I do this:

SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::geography) -> geography

If I try this:

SELECT test_st_startpoint('LINESTRING(1 1, 2 2)');  
Or this:
SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::text);  

I get an error ERROR:  Unknown geometry type: 64 - Invalid type

Which if we handled that case, would still come out as a text.
And anycompatible only works if your output is a coercion of your input.

e.g. 

CREATE OR REPLACE FUNCTION test_st_npoints(
	anycompatible)
    RETURNS integer
    LANGUAGE 'c'
    COST 50
    IMMUTABLE STRICT PARALLEL SAFE 
AS '$libdir/postgis-3', 'LWGEOM_npoints'
;

Works for nothing but the integer family, which of course doesn't work for
our function.

It's equivalent to writing

CREATE OR REPLACE FUNCTION test_st_npoints(
	integer)
    RETURNS integer
    LANGUAGE 'c'
    COST 50
    IMMUTABLE STRICT PARALLEL SAFE 
AS '$libdir/postgis-3', 'LWGEOM_npoints'
;

> -----Original Message-----
> From: Regina Obe [mailto:lr at pcorp.us]
> Sent: Tuesday, January 31, 2023 5:34 PM
> To: 'PostGIS Development Discussion' <postgis-devel at lists.osgeo.org>
> Subject: RE: [postgis-devel] An any spatial type that works for geometry /
> geography
> 
> Yah that was my concern too, well it wouldn't be all of them, just a lot
of them.
> 
> Strk has a nifty trick in place to at least handle this for upgrading
where he has
> logic to rename the old function swap in the new function and notify
people
> that they have views that need to be updated (assuming anyone pays
> attention to those notices).
> 
> Strk I now there was a reason we just don't replace the views, I forget
the
> reason something about check options?  I now we can't replace materialized
> views since there is no CREATE OR REPLACE MATERIALIZED VIEW command.
> 
> So I was thinking we could experiment with things people probably rarely
use.
> 
> Like the case of geometrytype(geometry) , geometrytype(geography)
> 
> Which we already have two versions of and I suspect it might only be used
in
> our own views, if it's used their at all. So get rid of the both and
replace with
> geometrytype(anygeo).
> 
> > -----Original Message-----
> > From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On
> > Behalf Of Paul Ramsey
> > Sent: Tuesday, January 31, 2023 5:15 PM
> > To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
> > Subject: Re: [postgis-devel] An any spatial type that works for
> > geometry / geography
> >
> > The main thing (?) I think we've had problems with in
> > geometry/geography casting is that even if we can avoid issues of
> > conflicts on types in the input side, the output of the default
> > function signatures is always geometry... so
> >
> > ST_StartPoint(geography) -> ST_StartPoint(geometry) returns geometry
> >
> > I haven't done the research, but the idea of a polymorphic function
> > feels "kinda right?" you feet ST_StartPoint(geography) ->
> > ST_StartPoint(anygeo) returns anygeo and then returns geography. Magic
> occurs.
> >
> > Big problem I see is how much function redefinition it requires. Like
> > all of them. So take our "changing aggregate signatures breaks / drops
views"
> > problem, and multiply by 100. Not sure how we get around that.
> >
> > P
> >
> > > On Jan 31, 2023, at 12:17 PM, Regina Obe <lr at pcorp.us> wrote:
> > >
> > > We've had this issue for a long time that many functions that can
> > > work just as easily for geography require geography to be cast to
> > > geometry to use them.
> > >
> > > This we've been discussing on #Postgis irc what to do.
> > >
> > > There have been the usual suspects bandied about for years:
> > >
> > > 1) Just define the functions for geography -- this balloons our list
> > > of functions, making it hard to browse the list in psql/pgadmin and
> > > also adds technical debt, way more redundant functions to wade thru
> > > and patch in our scripts.
> > >
> > > 2) Define an autocast from geography to geometry - this causes much
> > > unpredicatability.
> > > Note we already have an autocast from geometry -> geography, but
> > > that is less of a concern Since if a geometry is not in spheroidal
> > > spatial ref, the autocast fails.
> > > The geography would just silently pass and do an unexpected horrible
> > > thing that wouldn't be discovered until some plane crashes.
> > >
> > > My final thought is the cleanest way to do this is with an
> > > anypostgisvector or some such thing and for select functions that
> > > underlying logic is the same for both planar and spheroidal, would
> > > allow
> > either in.
> > >
> > > Right now I can test the theory out by for example,
> > >
> > > Defining a st_pointn variant
> > >
> > > CREATE OR REPLACE FUNCTION test_st_pointn(anyelement,integer)
> > >    RETURNS anyelement
> > >    LANGUAGE 'c'
> > >    COST 50
> > >    IMMUTABLE STRICT PARALLEL SAFE
> > > AS '$libdir/postgis-3', 'LWGEOM_pointn_linestring';
> > >
> > > And with that, I can do
> > >  SELECT test_st_pointn(ST_GeomFromText('LINESTRING(1 2, 3 4, 5
> > > 6)'),1); -> returns a geometry SELECT
> > > test_st_pointn(ST_GeogFromText('LINESTRING(1 2, 3 4, 5 6)'),1); ->
> > > returns a geography
> > >
> > > The only thing I don't like about the above, is then it will also
> > > match any gibberish like
> > >
> > > SELECT test_st_pointn('hello there'::text,1);
> > > ERROR:  Unknown geometry type: 231 - Invalid typeSQL state: XX000 Or
> > > SELECT test_st_pointn(ST_AsGeoJSON(ST_GeomFromText('LINESTRING(1
> 2,
> > 3
> > > 4, 5 6)')),1);
> > >
> > > ERROR:  Unknown geometry type: 1920226149 - Invalid type SQL state:
> > > XX000
> > >
> > > But hey, at least it returns an error.
> > >
> > > So perhaps just using the anyelement type for these cases isn't
> > > horrible especially if we've tested in the face of dangerous inputs.
> > > The only downside of this, is it would require dropping our geometry
> > > type functions that under the plumbing should call the same for
> > > geography and replacing with an anyelement type.
> > >
> > > I'm not sure how hard it would be to create our own type that just
> > > targets geometry and geography.  That I think would be cleaner if
> > > it's not too much trouble.
> > >
> > > Anyone see issues with this idea?
> > >
> > > Thanks,
> > > Regina
> > >
> > >
> > >
> > >
> > > _______________________________________________
> > > postgis-devel mailing list
> > > postgis-devel at lists.osgeo.org
> > > https://lists.osgeo.org/mailman/listinfo/postgis-devel
> >
> > _______________________________________________
> > postgis-devel mailing list
> > postgis-devel at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-devel

More information about the postgis-devel mailing list