Add support for a polymorphic data type for enclosed regions

[esabol]

I think PgSphere could use a built-in, performant polymorphic datatype for an enclosed region. Basically, a type that could be either an scircle or an sbox or an spoly or an sellipse. Then define the usual operators that operate on this datatype for checking whether a point or other geometrical type is inside it, etc.

I propose calling this polymorphic datatype "sregion", but I'm open to alternatives.

Rationale: It's becoming more common for astronomical observations to have the fields-of-view (FoVs) of the instrument(s) doing the observing to be well defined as polygons of points in space, but it's still very common for boxes or circles to be used as approximations of FoVs for older observations as well. And IVOA standards (ObsCore) implementing searches on observation databases require lumping together all of these observations from these disparate sources into one giant table for searching. Without such a polymorphic type, you have to do a bunch of UNIONs and/or CASE...WHENs to handle the different types of enclosed regions that might be contained in these database tables. It would be beneficial if PgSphere could alleviate that level of complexity by incorporating support for this.

[obartunov]

On Wed, Jul 17, 2024 at 11:38 PM Ed Sabol ***@***.***> wrote: I think PgSphere could use a built-in, performant polymorphic datatype for an enclosed region. Basically, a type that could be either an scircle or an sbox or an spoly or an sellipse. Then define the usual operators that operate on this datatype for checking whether a point or other geometrical type is inside it, etc.

I'd like to propose calling this polymorphic datatype "sregion", but I'm

open to alternatives. Rationale: It's becoming more common for astronomical observations to have the fields-of-view (FoVs) of the instrument(s) doing the observing to be well defined as polygons of points in space, but it's still very common for boxes or circles to be used as approximations of FoVs for older observations as well. And IVOA standards (ObsCore) implementing searches on observation databases require lumping together all of these observations from these disparate sources into one giant table for searching. Without such a polymorphic type, you have to do a bunch of UNIONs and/or CASE...WHENs to handle the different types of enclosed regions that might be contained in these database tables. It would be beneficial if PgSphere could alleviate that level of complexity by incorporating support for this.

I think this is very useful and 'sregion' is a good name for the datatype. Do you have any idea about implementation ?

…

— Reply to this email directly, view it on GitHub <#128>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABQURYWWEY6TWTMHE45OTJTZM3I3HAVCNFSM6AAAAABLBLK2XOVHI2DSMVQWIX3LMV43ASLTON2WKOZSGQYTINJTGEYTGMY> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>

-- Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

[esabol]

Do you have any idea about implementation ?

We have a rudimentary polymorphic data type that's implemented in SQL, which looks something like the below snippet, but it's not very performant. I think it needs to be implemented at the C level.

        CREATE TYPE sregion AS
        (   
            gtype   int,       -- 0 for null, 1 for scircle, 2 for spoly, etc.
            spec    text,      -- text definition of region
            circle  scircle,   -- circle delegate
            poly    spoly      -- polygon delegate
        );