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 19073On 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 19073On 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 publicThere 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 19073Bruno Wolff III wrote:
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.
[ CC changed to hackers.]
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
Auto-install cube. I think this is done by psql making/installing libpq
because it depends on that.
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.
OK.
--
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 19073Import Notes
Reply to msg id not found: 20020907193240.GB30941@wolff.to | Resolved by subject fallback
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.
I am almost done. While working on the regression test I found a significant
bug in the original earth distance package, so this really does need to
get updated. While I was doing that I switched it to use the haversine
formula as that is more accurate for short distances than the formula
they used previously.
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