<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>I’m beginning to feel that way too<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>The pain point is a lot of geometry functions work just fine in geography if you do <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>geography::geometry<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>But if all you work with is geography, the casting becomes a pain.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>We don’t want to autocast geography to geometry, because a not so insignificant number of geometry do not work correctly with geography. <o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>So how about this as a compromise:<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>If we add new functions for geometry that could work with geography without change, we set them to anyelement?<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Bruce,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Functions where we specifically have versions for geography because the behavior is different<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>ST_Buffer, ST_DWithin, ST_Distance, ST_Segmentize, ST_Length, ST_Perimeter etc, were never on the table.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>It’s more things like ST_NPoints, geometrytype.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Thanks,<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Regina<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0in 0in 0in 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0in 0in 0in'><p class=MsoNormal><b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>From:</span></b><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'> postgis-devel [mailto:postgis-devel-bounces@lists.osgeo.org] <b>On Behalf Of </b>Bruce Rindahl<br><b>Sent:</b> Sunday, February 5, 2023 5:24 PM<br><b>To:</b> PostGIS Development Discussion <postgis-devel@lists.osgeo.org><br><b>Subject:</b> Re: [postgis-devel] An any spatial type that works for geometry / geography<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>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.<o:p></o:p></p></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>On Sun, Feb 5, 2023, 2:50 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0in 0in 0in 6.0pt;margin-left:4.8pt;margin-right:0in'><p class=MsoNormal>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">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" 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" 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">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">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">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">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">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">postgis-devel@lists.osgeo.org</a><br>> > > > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" 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">postgis-devel@lists.osgeo.org</a><br>> > > <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" 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">postgis-devel@lists.osgeo.org</a><br>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" 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">postgis-devel@lists.osgeo.org</a><br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><o:p></o:p></p></blockquote></div></div></div></body></html>