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

Bruce Rindahl bruce.rindahl at gmail.com
Sun Feb 5 14:23:30 PST 2023 

FWIW I agree.  What is the pain point that this will fix?  I for one enjoy
explaining why a buffer of 10 around a lat/long point doesn't give what
they were looking for.

On Sun, Feb 5, 2023, 2:50 PM Paul Ramsey <pramsey at cleverelephant.ca> wrote:

> 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
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20230205/9bcd05d1/attachment-0001.htm>

More information about the postgis-devel mailing list