Thread: Making small bits of code available
I have some sql to define some functions for doing conversions between cube and latitude and longitude (as float8) and for calculating great circle distances between cubes (using a spherical model of the earth). I am not sure the code is suitable for contrib. The code picks a radius of the earth in meters. Other people may choose to use different units or even use a different radius in meters. I have grants in the code to make the cube functions and the functions defined by the script as execute for public. (The cube stuff needs to be done as postgres since a type is created, but then the functions aren't generally accessible by default.) The script is about 5K. Some people might find this useful as there are some advantages to keeping track of locations on the earth using cube (with 3D coordinates) as opposed to using point (with 2D coordinates).
/contrib/earthdistance already exists. Is this new functionality? --------------------------------------------------------------------------- Bruno Wolff III wrote: > I have some sql to define some functions for doing conversions between > cube and latitude and longitude (as float8) and for calculating > great circle distances between cubes (using a spherical model of the earth). > I am not sure the code is suitable for contrib. > The code picks a radius of the earth in meters. Other people may choose to > use different units or even use a different radius in meters. > I have grants in the code to make the cube functions and the functions > defined by the script as execute for public. (The cube stuff needs to be > done as postgres since a type is created, but then the functions aren't > generally accessible by default.) > The script is about 5K. > Some people might find this useful as there are some advantages to keeping > track of locations on the earth using cube (with 3D coordinates) as opposed > to using point (with 2D coordinates). > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Fri, Sep 06, 2002 at 09:58:00 -0400, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > /contrib/earthdistance already exists. Is this new functionality? This works with cube instead of point. If you use point hold latitude and longitude you have to worry about whether you will have data near 180 degrees of longitude or near the poles. This may not be a problem if your data is mostly on one continent. The script I have is most grant calls for the cube functions. Since cube needs to be installed as postgres (or other super user), most likely you want to grant execute to public on the provided functions. (I don't know if you need to do this for ones just used be the gist stuff.) The stuff people might want to see are a few sql functions for getting to and from latitude and longitude and cube (as domain earth) and some functions related to getting the size of boxes to use for searching for points within a great circle distance of a specified point. If 5K isn't too much I could post it to the list and it will get archived and people that are interested can find it with google and can take what they want from the code. This stuff isn't packaged up neatly for a contrib with a regression test and all. Probably people who use this will want to tinker with it before using it themselves. The function prototypes extracted from the file are: create function earth() returns float8 language 'sql' immutable as create function sec_to_gc(float8) returns float8 language 'sql' create function gc_to_sec(float8) returns float8 language 'sql' create function ll_to_earth(float8, float8) returns earth language 'sql' create function latitude(earth) returns float8 language 'sql' create function longitude(earth) returns float8 language 'sql' create function earth_distance(earth, earth) returns float8 language 'sql' create function earth_box(earth, float8) returns cube language 'sql'
What would be really valuable would be to add your routines to /contrib/earthdistance. Is that possible? --------------------------------------------------------------------------- Bruno Wolff III wrote: > On Fri, Sep 06, 2002 at 09:58:00 -0400, > Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > > /contrib/earthdistance already exists. Is this new functionality? > > This works with cube instead of point. If you use point hold latitude and > longitude you have to worry about whether you will have data near 180 > degrees of longitude or near the poles. This may not be a problem if > your data is mostly on one continent. > > The script I have is most grant calls for the cube functions. Since cube > needs to be installed as postgres (or other super user), most likely > you want to grant execute to public on the provided functions. (I don't > know if you need to do this for ones just used be the gist stuff.) > > The stuff people might want to see are a few sql functions for getting > to and from latitude and longitude and cube (as domain earth) and some > functions related to getting the size of boxes to use for searching for > points within a great circle distance of a specified point. > > If 5K isn't too much I could post it to the list and it will get archived > and people that are interested can find it with google and can take what they > want from the code. > > This stuff isn't packaged up neatly for a contrib with a regression test > and all. Probably people who use this will want to tinker with it before > using it themselves. > > The function prototypes extracted from the file are: > create function earth() returns float8 language 'sql' immutable as > create function sec_to_gc(float8) returns float8 language 'sql' > create function gc_to_sec(float8) returns float8 language 'sql' > create function ll_to_earth(float8, float8) returns earth language 'sql' > create function latitude(earth) returns float8 language 'sql' > create function longitude(earth) returns float8 language 'sql' > create function earth_distance(earth, earth) returns float8 language 'sql' > create function earth_box(earth, float8) returns cube language 'sql' > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sat, Sep 07, 2002 at 10:05:14 -0400, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > What would be really valuable would be to add your routines to > /contrib/earthdistance. Is that possible? Yes. Right now the script contains: Some leading comments grant execute to public commands for each function in contrib/cube A definition of the earth domain along with comments about what check constraints should be used (until domains support check constraints) For each new function there is a comment about it, a definition (using language 'sql') and a grant execute to public There is currently no regression test. Now to the questions. Were the function names (earth, sec_to_gc, gc_to_sec, ll_to_earth, latitude, longitude, earth_distance, and earth_box) acceptable? Should I make a separate regression test file or add it on to the existing one for earth_distance? Should I make a separate README file or just add stuff to the end of the existing REAMDE file? Should I leave the grants in, leave that to the administrator or provide a separate script? Should the creation of these functions be added to the existing script for earth_distance or should it be a separate script? It seems unlikely that someone would be using both of these at the same time, since one is based on the point type and the other on the cube type. However the overhead of installing both seems small, so maybe making it easier to try both and then pick one is worthwhile. Another option would be to go back to the contrib/cube install script and and grants for the functions there. And then just to a grant for the old geo_distance function in earthdistance (since that is the only 'C' function)? I didn't do that previously because the previous contrib/cube didn't, but of course, functions didn't have an execute privilege previously. If I do that, do I have to grant public access to internal functions (used for the gist index) or can I just make the ones meant for users to access directly public?
Bruno Wolff III wrote: > On Sat, Sep 07, 2002 at 10:05:14 -0400, > Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > > What would be really valuable would be to add your routines to > > /contrib/earthdistance. Is that possible? > > Yes. > > Right now the script contains: > > Some leading comments > > grant execute to public commands for each function in contrib/cube > > A definition of the earth domain along with comments about what check > constraints should be used (until domains support check constraints) > > For each new function there is a comment about it, a definition (using > language 'sql') and a grant execute to public > > There is currently no regression test. > > Now to the questions. > > Were the function names (earth, sec_to_gc, gc_to_sec, ll_to_earth, latitude, > longitude, earth_distance, and earth_box) acceptable? Sure. > Should I make a separate regression test file or add it on to the existing > one for earth_distance? No, just add. If someone wants earth measurements, it should all be in one place. > Should I make a separate README file or just add stuff to the end of the > existing REAMDE file? Just add. > Should I leave the grants in, leave that to the administrator or provide > a separate script? I would not add the grants. > Should the creation of these functions be added to the existing script > for earth_distance or should it be a separate script? It seems unlikely > that someone would be using both of these at the same time, since one > is based on the point type and the other on the cube type. However the > overhead of installing both seems small, so maybe making it easier to > try both and then pick one is worthwhile. Install them both. Just make sure it is clear which is which, or are yours superior and the old one should be removed? > Another option would be to go back to the contrib/cube install script > and and grants for the functions there. And then just to a grant for > the old geo_distance function in earthdistance (since that is the only > 'C' function)? I didn't do that previously because the previous contrib/cube > didn't, but of course, functions didn't have an execute privilege previously. > If I do that, do I have to grant public access to internal functions > (used for the gist index) or can I just make the ones meant for users > to access directly public? Not sure. I don't think we want to public permit this stuff unless the admin asks for it. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
On Sat, Sep 07, 2002 at 12:52:06 -0400, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > > > Should the creation of these functions be added to the existing script > > for earth_distance or should it be a separate script? It seems unlikely > > that someone would be using both of these at the same time, since one > > is based on the point type and the other on the cube type. However the > > overhead of installing both seems small, so maybe making it easier to > > try both and then pick one is worthwhile. > > > Install them both. Just make sure it is clear which is which, or are > yours superior and the old one should be removed? They are different and someone could want either. I forgot to ask about how to handle the dependency on contrib/cube. I can see three options. Automatically install contrib/cube when building contrib/earthdistance, refuse to work unless contrib cube appears to be installed, or only install the original stuff if contribe/cube is not available. Trying to do different installs based on whether or not contrib/cube is installed seems like a bad idea as it is mistake prone and could be confusing. > > Not sure. I don't think we want to public permit this stuff unless the > admin asks for it. I will put in some comments about needing to make functions public for normal user access.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Bruno Wolff III wrote:
>> Should I leave the grants in, leave that to the administrator or provide
>> a separate script?
> I would not add the grants.
Actually I disagree. Bruno's comment made me realize that all the
contrib scripts that create functions are now effectively broken,
because they create functions that are not callable by anyone
except the creating user. 99% of the time that will be wrong.
The scripts were all written under the assumption that the functions
they create would be callable by world. I think we should add explicit
GRANT EXECUTE TO PUBLIC commands to them to maintain
backwards-compatible behavior.
If there's anyone who does not want that result, they can easily edit
the script before they run it. Adding missing GRANTs to a creation
script is a lot harder than commenting out ones you don't want ...
>> If I do that, do I have to grant public access to internal functions
>> (used for the gist index) or can I just make the ones meant for users
Don't believe it matters. Anything taking an INTERNAL parameter cannot
be called manually anyway.
regards, tom lane