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

[Paul Ramsey]

Sounds like any attempt to gain geography compatibility via
polymorphism will end in tears, or at least a lot of text input
variants that wash out any advantage gained from the polymorphism...

P

On Sun, Feb 5, 2023 at 1:46 AM Regina Obe <lr at pcorp.us> wrote:
>
> 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
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel