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

Regina Obe lr at pcorp.us
Sun Feb 5 19:04:38 PST 2023 

I’m beginning to feel that way too

The pain point is a lot of geometry functions work just fine in geography if you do 

 

geography::geometry

 

But if all you work with is geography, the casting becomes a pain.

 

We don’t want to autocast geography to geometry, because a not so insignificant number of geometry do not work correctly with geography. 

 

It was brought up in IRC because someone wanted to define all these trivial functions for geography and I said NO.  That will add another 500 functions to our list, adding a not so insignificant amount of technical debt and my user debt, cause when I browser my function tree I have 500 more functions that look very similar and do the same thing.

 

So how about this as a compromise:

 

If we add new functions for geometry that could work with geography without change, we set them to anyelement?

 

Bruce,

Functions where we specifically have versions for geography because the behavior is different

 

ST_Buffer, ST_DWithin, ST_Distance, ST_Segmentize, ST_Length, ST_Perimeter etc, were never on the table.

 

It’s more things like ST_NPoints, geometrytype.

 

Thanks,

Regina

 

From: postgis-devel [mailto:postgis-devel-bounces at lists.osgeo.org] On Behalf Of Bruce Rindahl
Sent: Sunday, February 5, 2023 5:24 PM
To: PostGIS Development Discussion <postgis-devel at lists.osgeo.org>
Subject: Re: [postgis-devel] An any spatial type that works for geometry / geography

 

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 <mailto: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 <mailto: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 <mailto:lr at pcorp.us> ]
> > Sent: Tuesday, January 31, 2023 5:34 PM
> > To: 'PostGIS Development Discussion' <postgis-devel at lists.osgeo.org <mailto: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 <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 <mailto: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 <mailto: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 <mailto:postgis-devel at lists.osgeo.org> 
> > > > https://lists.osgeo.org/mailman/listinfo/postgis-devel
> > >
> > > _______________________________________________
> > > postgis-devel mailing list
> > > postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
> > > https://lists.osgeo.org/mailman/listinfo/postgis-devel
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org <mailto:postgis-devel at lists.osgeo.org> 
> https://lists.osgeo.org/mailman/listinfo/postgis-devel
_______________________________________________
postgis-devel mailing list
postgis-devel at lists.osgeo.org <mailto: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/b3505b35/attachment-0001.htm>

More information about the postgis-devel mailing list