<div dir="auto">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.</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Sun, Feb 5, 2023, 2:50 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Sounds like any attempt to gain geography compatibility via<br>
polymorphism will end in tears, or at least a lot of text input<br>
variants that wash out any advantage gained from the polymorphism...<br>
<br>
P<br>
<br>
On Sun, Feb 5, 2023 at 1:46 AM Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>> wrote:<br>
><br>
> I remembered another issue:<br>
> Things like ST_StartPoint('LINESTRING(1 1, 2 2)') wouldn't work anymore. If<br>
> ST_StartPoint was changed to ST_StartPoint(anyelement)<br>
><br>
> You'd get an error:<br>
><br>
> ERROR: could not determine polymorphic type because input has type unknown<br>
><br>
> I personally thing people shouldn't be doing things like that anyway so not<br>
> sure I much care about that kind of breakage. But that would definitely be<br>
> considered a break.<br>
><br>
> We've got functions like ST_GeoHash that already don't work with unknowns<br>
> because we never bothered to define an ST_GeoHash(text) variant.<br>
><br>
> So doing this<br>
><br>
> SELECT ST_GeoHash('POINT(1 2)');<br>
><br>
> Gets you:<br>
><br>
> function ST_GeoHash(unknown) is not unique<br>
><br>
> I was also thinking about how we would introduce our own anygeo, and at a<br>
> glance seems these are pretty baked in and no way to extend that.<br>
><br>
> <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/uti" rel="noreferrer noreferrer" target="_blank">https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/uti</a><br>
> ls/fmgr/funcapi.c;h=217835d590bedde3cbc1026c6583a66f90761704;hb=HEAD#l858<br>
><br>
> I was hoping anycompatible might be the answer, but seems more designed for<br>
> the case of functions that take two different types, like our raster,<br>
> geometry functions and allows one to be coerced to the other.<br>
><br>
> All polymorpics are described here:<br>
> <a href="https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES" rel="noreferrer noreferrer" target="_blank">https://www.postgresql.org/docs/current/extend-type-system.html#EXTEND-TYPES</a><br>
> -POLYMORPHIC<br>
><br>
><br>
> I was hoping anycompatible would do the trick to allow unknowns, but it has<br>
> warts.<br>
><br>
> e.g.<br>
><br>
> For example if I define a function:<br>
><br>
> CREATE OR REPLACE FUNCTION test_st_startpoint(<br>
> anycompatible)<br>
> RETURNS anycompatible<br>
> LANGUAGE 'c'<br>
> COST 50<br>
> IMMUTABLE STRICT PARALLEL SAFE<br>
> AS '$libdir/postgis-3', 'LWGEOM_startpoint_linestring';<br>
><br>
><br>
> I can do this:<br>
><br>
> SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::geometry); -> geometry<br>
><br>
> If I do this:<br>
><br>
> SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::geography) -> geography<br>
><br>
> If I try this:<br>
><br>
> SELECT test_st_startpoint('LINESTRING(1 1, 2 2)');<br>
> Or this:<br>
> SELECT test_st_startpoint('LINESTRING(1 1, 2 2)'::text);<br>
><br>
> I get an error ERROR: Unknown geometry type: 64 - Invalid type<br>
><br>
> Which if we handled that case, would still come out as a text.<br>
> And anycompatible only works if your output is a coercion of your input.<br>
><br>
> e.g.<br>
><br>
> CREATE OR REPLACE FUNCTION test_st_npoints(<br>
> anycompatible)<br>
> RETURNS integer<br>
> LANGUAGE 'c'<br>
> COST 50<br>
> IMMUTABLE STRICT PARALLEL SAFE<br>
> AS '$libdir/postgis-3', 'LWGEOM_npoints'<br>
> ;<br>
><br>
> Works for nothing but the integer family, which of course doesn't work for<br>
> our function.<br>
><br>
> It's equivalent to writing<br>
><br>
> CREATE OR REPLACE FUNCTION test_st_npoints(<br>
> integer)<br>
> RETURNS integer<br>
> LANGUAGE 'c'<br>
> COST 50<br>
> IMMUTABLE STRICT PARALLEL SAFE<br>
> AS '$libdir/postgis-3', 'LWGEOM_npoints'<br>
> ;<br>
><br>
> > -----Original Message-----<br>
> > From: Regina Obe [mailto:<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>]<br>
> > Sent: Tuesday, January 31, 2023 5:34 PM<br>
> > To: 'PostGIS Development Discussion' <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a>><br>
> > Subject: RE: [postgis-devel] An any spatial type that works for geometry /<br>
> > geography<br>
> ><br>
> > Yah that was my concern too, well it wouldn't be all of them, just a lot<br>
> of them.<br>
> ><br>
> > Strk has a nifty trick in place to at least handle this for upgrading<br>
> where he has<br>
> > logic to rename the old function swap in the new function and notify<br>
> people<br>
> > that they have views that need to be updated (assuming anyone pays<br>
> > attention to those notices).<br>
> ><br>
> > Strk I now there was a reason we just don't replace the views, I forget<br>
> the<br>
> > reason something about check options? I now we can't replace materialized<br>
> > views since there is no CREATE OR REPLACE MATERIALIZED VIEW command.<br>
> ><br>
> > So I was thinking we could experiment with things people probably rarely<br>
> use.<br>
> ><br>
> > Like the case of geometrytype(geometry) , geometrytype(geography)<br>
> ><br>
> > Which we already have two versions of and I suspect it might only be used<br>
> in<br>
> > our own views, if it's used their at all. So get rid of the both and<br>
> replace with<br>
> > geometrytype(anygeo).<br>
> ><br>
> > > -----Original Message-----<br>
> > > From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel-bounces@lists.osgeo.org</a>] On<br>
> > > Behalf Of Paul Ramsey<br>
> > > Sent: Tuesday, January 31, 2023 5:15 PM<br>
> > > To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a>><br>
> > > Subject: Re: [postgis-devel] An any spatial type that works for<br>
> > > geometry / geography<br>
> > ><br>
> > > The main thing (?) I think we've had problems with in<br>
> > > geometry/geography casting is that even if we can avoid issues of<br>
> > > conflicts on types in the input side, the output of the default<br>
> > > function signatures is always geometry... so<br>
> > ><br>
> > > ST_StartPoint(geography) -> ST_StartPoint(geometry) returns geometry<br>
> > ><br>
> > > I haven't done the research, but the idea of a polymorphic function<br>
> > > feels "kinda right?" you feet ST_StartPoint(geography) -><br>
> > > ST_StartPoint(anygeo) returns anygeo and then returns geography. Magic<br>
> > occurs.<br>
> > ><br>
> > > Big problem I see is how much function redefinition it requires. Like<br>
> > > all of them. So take our "changing aggregate signatures breaks / drops<br>
> views"<br>
> > > problem, and multiply by 100. Not sure how we get around that.<br>
> > ><br>
> > > P<br>
> > ><br>
> > > > On Jan 31, 2023, at 12:17 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank" rel="noreferrer">lr@pcorp.us</a>> wrote:<br>
> > > ><br>
> > > > We've had this issue for a long time that many functions that can<br>
> > > > work just as easily for geography require geography to be cast to<br>
> > > > geometry to use them.<br>
> > > ><br>
> > > > This we've been discussing on #Postgis irc what to do.<br>
> > > ><br>
> > > > There have been the usual suspects bandied about for years:<br>
> > > ><br>
> > > > 1) Just define the functions for geography -- this balloons our list<br>
> > > > of functions, making it hard to browse the list in psql/pgadmin and<br>
> > > > also adds technical debt, way more redundant functions to wade thru<br>
> > > > and patch in our scripts.<br>
> > > ><br>
> > > > 2) Define an autocast from geography to geometry - this causes much<br>
> > > > unpredicatability.<br>
> > > > Note we already have an autocast from geometry -> geography, but<br>
> > > > that is less of a concern Since if a geometry is not in spheroidal<br>
> > > > spatial ref, the autocast fails.<br>
> > > > The geography would just silently pass and do an unexpected horrible<br>
> > > > thing that wouldn't be discovered until some plane crashes.<br>
> > > ><br>
> > > > My final thought is the cleanest way to do this is with an<br>
> > > > anypostgisvector or some such thing and for select functions that<br>
> > > > underlying logic is the same for both planar and spheroidal, would<br>
> > > > allow<br>
> > > either in.<br>
> > > ><br>
> > > > Right now I can test the theory out by for example,<br>
> > > ><br>
> > > > Defining a st_pointn variant<br>
> > > ><br>
> > > > CREATE OR REPLACE FUNCTION test_st_pointn(anyelement,integer)<br>
> > > > RETURNS anyelement<br>
> > > > LANGUAGE 'c'<br>
> > > > COST 50<br>
> > > > IMMUTABLE STRICT PARALLEL SAFE<br>
> > > > AS '$libdir/postgis-3', 'LWGEOM_pointn_linestring';<br>
> > > ><br>
> > > > And with that, I can do<br>
> > > > SELECT test_st_pointn(ST_GeomFromText('LINESTRING(1 2, 3 4, 5<br>
> > > > 6)'),1); -> returns a geometry SELECT<br>
> > > > test_st_pointn(ST_GeogFromText('LINESTRING(1 2, 3 4, 5 6)'),1); -><br>
> > > > returns a geography<br>
> > > ><br>
> > > > The only thing I don't like about the above, is then it will also<br>
> > > > match any gibberish like<br>
> > > ><br>
> > > > SELECT test_st_pointn('hello there'::text,1);<br>
> > > > ERROR: Unknown geometry type: 231 - Invalid typeSQL state: XX000 Or<br>
> > > > SELECT test_st_pointn(ST_AsGeoJSON(ST_GeomFromText('LINESTRING(1<br>
> > 2,<br>
> > > 3<br>
> > > > 4, 5 6)')),1);<br>
> > > ><br>
> > > > ERROR: Unknown geometry type: 1920226149 - Invalid type SQL state:<br>
> > > > XX000<br>
> > > ><br>
> > > > But hey, at least it returns an error.<br>
> > > ><br>
> > > > So perhaps just using the anyelement type for these cases isn't<br>
> > > > horrible especially if we've tested in the face of dangerous inputs.<br>
> > > > The only downside of this, is it would require dropping our geometry<br>
> > > > type functions that under the plumbing should call the same for<br>
> > > > geography and replacing with an anyelement type.<br>
> > > ><br>
> > > > I'm not sure how hard it would be to create our own type that just<br>
> > > > targets geometry and geography. That I think would be cleaner if<br>
> > > > it's not too much trouble.<br>
> > > ><br>
> > > > Anyone see issues with this idea?<br>
> > > ><br>
> > > > Thanks,<br>
> > > > Regina<br>
> > > ><br>
> > > ><br>
> > > ><br>
> > > ><br>
> > > > _______________________________________________<br>
> > > > postgis-devel mailing list<br>
> > > > <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a><br>
> > > > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
> > ><br>
> > > _______________________________________________<br>
> > > postgis-devel mailing list<br>
> > > <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a><br>
> > > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
><br>
> _______________________________________________<br>
> postgis-devel mailing list<br>
> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank" rel="noreferrer">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
</blockquote></div>