search

postgrespro / pgsphere

PgSphere provides spherical data types, functions, operators, and indexing for PostgreSQL.
https://pgsphere.org
BSD 3-Clause "New" or "Revised" License
16 stars 14 forks source link

Add spoint_deg() and spoly_deg() functions? #22

Closed esabol closed 11 months ago

esabol commented 1 year ago

All of the database tables we use have the spherical positions stored as degrees, not radians. Adding spoint_deg() and spoly_deg() would be useful for this use-case. There's a 5-year-old PR that implements this here:

https://github.com/akorotkov/pgsphere/pull/6

It just needed a little clean up, but nobody followed through. Would you consider incorporating this functionality here?

vitcpp commented 1 year ago

@esabol Sure, I will check and incorporate it. I know about akorotkov repo but I'm not sure that it still alive. Anyway, if someone wants to create a ready-to-merge PR, it will make the process faster (assign yourself on the Issue first to avoid conflicts). There are a lot of other pending PR. I plan to review and incorporate some other pending PR in our branch.

dura0ok commented 11 months ago

Maybe we can use set_sphere_output to implement this functions? @esabol

vitcpp commented 11 months ago

@stepan-neretin7 set_sphere_output is used to configure how objects are displayed in user console. spoint_deg and spoly_deg construct new objects. Users may use radians and degrees to construct different objects in the same query. Of course, user may use textual representation of coordinates in degrees by adding 'd' suffix but it is not comfortable because user have to format a string.

esabol commented 11 months ago

The typical use-case is that you have a database table with two columns containing longitudes and latitudes in degrees. I think most everyone stores longitudes and latitudes in degrees. Fortunately, PostgreSQL has a radians() function to convert our degrees to the radians that pgSphere requires. So a typical pgSphere use-case is a query like this:

select * from sometable where spoint(radians(ra),radians(dec))
                                  @ scircle(spoint(radians(10),radians(10)),radians(1))

Well, adding all those radians() functions to your SQL is cumbersome. Also, it would be faster and more efficient if pgSphere's most commonly used functions had alternate versions which take degrees and convert to radians internally (at C code level) instead of using PostgreSQL's radians() function. In such a scenario, the SQL then becomes:

select * from sometable where spoint_deg(ra,dec) @ scircle_deg(spoint_deg(10,10),1))

which is both more succinct and probably faster.

esabol commented 11 months ago

PR #38 has been merged, addressing this issue. Closing.