Re: [SQL] Radius of a zip code
On Fri, Dec 26, 2003 at 22:19:52 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > > Incidentally, I see the following in README.earthdistance: > > A note on testing C extensions - it seems not enough to drop a function > and re-create it - if I change a function, I have to stop and restart > the backend for the new version to be seen. I guess it would be too > messy to track which functions are added from a .so and do a dlclose > when the last one is dropped. > > Maybe you've already figured it out, but LOAD should allow you to reload > a .so file without having to restart the backend. I didn't write that. It came from the person(s) who worked on earthdistance before I touched it. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Anti log in PostgreSQL
Definition of log (base n) is that log n(x) = y where n^y = x for all values of x and y. n is the base. So a base 10 log would be reversed by doing 10^x=y. If we know x, we use the exponential operation; if we know y we use log(y) = x. For ln (natural logs, base e, e is approx. 2.818), use e^x=y. Hope this explains things. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Anti log in PostgreSQL
On December 26, 2003 06:37 pm, Tom Lane wrote: > accumulated roundoff in the monetary amounts. I think you will > need to write a little function (in plpgsql or your language of > choice) that performs the sequence of compounding steps, if you > want to get an answer that a banker will like. And make sure that you check your local rules. Mortgages are actually calculated differently in Canada and the US. -- D'Arcy J.M. Cain <[EMAIL PROTECTED]|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] What am I doing wrong in here?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm tyring to create a new table/database/user; and I get some errors. I'm running: [EMAIL PROTECTED]:template1=# SELECT version(); version - --- PostgreSQL 7.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031022 (Red Hat Linux 3.3.2-1) which has been installed using Lamar's RPMS. Here is set of commands: = CREATE USER tdmsoftmailserveruser WITH ENCRYPTED PASSWORD 'test' NOCREATEDB NOCREATEUSER; CREATE DATABASE tdmsoftmailserver WITH ENCODING 'LATIN5' OWNER=tdmsoftmailserveruser; \c tdmsoftmailserver tdmsoftmailserveruser CREATE TABLE public.tdmalias ( mid serial, address varchar(255) NOT NULL default '' PRIMARY KEY, goto text NOT NULL, domain varchar(255) NOT NULL default '', create_date timestamp NOT NULL DEFAULT 'NOW', change_date timestamp NOT NULL DEFAULT 'NOW', active int2 NOT NULL default '1' ); And I get: NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for "serial" column "tdmalias.mid" ERROR: permission denied for schema pg_catalog Google'd a bit; but could not find a solution for that. Is is something with schemas? Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.TDMSoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/7aF+tl86P3SPfQ4RAtC7AJ974H/j5rWlTYP32De+LYLgEb2GmgCePZFW QHbdSSw9OKvY0cF/nYbUM5g= =3jq0 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] What am I doing wrong in here?
Devrim GUNDUZ (Saturday 27 December 2003 10:12)
> create_date timestamp NOT NULL DEFAULT 'NOW',
> change_date timestamp NOT NULL DEFAULT 'NOW',
Do these actually work? I've always used 'default now()'...
> And I get:
>
> NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for
> "serial" column "tdmalias.mid"
If you want to avoid this, then create your sequences manually instead of
using 'serial'.
\echo '* datasources'
create sequence "datasources_id_seq"
start 1
increment 1
maxvalue 9223372036854775807
minvalue 1
cache 1;
comment on sequence "datasources_id_seq" is 'Datasources ID';
create table "datasources" (
"id" integer not null unique default nextval
('datasources_id_seq'),
"name" varchar(32) not null unique,
"type_id" varchar(16) not null,
primary key ("id")
);
comment on table "datasources" is 'Datasource Definition';
This also gives you more flexibility since you can change the parameters of
the sequence (oftentimes I use start 0 minvalue 0, instead), and you can use
a name of your own choice. The settings shown are the default when you use
'serial'.
> ERROR: permission denied for schema pg_catalog
The user you create the user as needs to have createuser permission.
select * from "pg_catalog"."pg_user";
...will show you all that you need to know.
alter user "foo" with createuser;
...(run as an appropriate user) will grant the user such permission.
Vertu sæll,
--
Sigþór Björn Jarðarson (Casey Allen Shobe)
[EMAIL PROTECTED] / http://rivyn.livejournal.com
Jabber: [EMAIL PROTECTED]; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy
Free development contributor of:
> KDE toolbar icons
> Kopete user interface, usability, and testing
> X11 Icelandic Dvorak keymaps
> Reporting of over 100 Kopete bugs
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Radius of a zip code
Thanks All for your suggestions, I have enough information to construct
what I need.
-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Friday, December 26, 2003 8:43 PM
To: Andy Lewis
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Radius of a zip code
On Fri, Dec 26, 2003 at 05:42:08PM -0600, Andy Lewis wrote:
> I was trying to find all zip codes within a given zip code or radius.
>
> I have map points and Latitude and Longitude in my zip table.
>
> I remember seeing a post or two referencing this but can't see to find
> it.
The code in contrib/earthdistance in the PostgreSQL source code might be
what you're looking for. I haven't used it myself, as I had already
written a function I needed for another DBMS and ported it to
PostgreSQL.
> I've tried the following with no luck:
>
> -- 20 Miles
> --select 20 * 360.0 / (7900.0 * atan2(1,1) * 4.0);
> select * from zip_code where map_loc @
> circle(map_point('dallas','tx','75201'), .290105212724467 ) order by
> city
This isn't related to the problem, but is there a reason your map_point
function requires city, state, and zip code? If you know the zip code
then you shouldn't need the city and state.
> Anyone that has this experience, can you validate this for
> correctness?
I have several databases with lat/lon coordinates and frequently make
"show me all records within a certain distance of this point" queries. I
wrote a haversine() function that uses the Haversine Formula to
calculate the great circle distance between two points on a sphere
(assuming the earth is a perfect sphere is accurate enough for my uses).
Here's a web site with related info:
http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1
Here's an example of how I use the haversine() function. I'm not using
PostgreSQL's geometric types -- latitude and longitude are stored in
separate fields. The function takes two lat/lon coordinates in degrees
and optionally a radius (the default is 3956.0, the approximate radius
of the earth in miles); it returns the distance in whatever units the
radius is in.
SELECT a.zipcode, a.city, a.state,
haversine(a.latitude, a.longitude, b.latitude, b.longitude) AS
dist FROM zipcode AS a, zipcode AS b WHERE b.zipcode = 75201
AND haversine(a.latitude, a.longitude, b.latitude, b.longitude) <= 20
ORDER BY dist;
zipcode | city | state | dist
-+---+---+---
75201 | Dallas| TX| 0
75270 | Dallas| TX| 0.460576795779555
75202 | Dallas| TX| 0.62326173788043
.
.
.
76012 | Arlington | TX| 19.644132573068
75126 | Forney| TX| 19.8963253723536
75024 | Plano | TX| 19.9884653971924
(106 rows)
As for validating the function's correctness, I'm using a well-known
formula and I've compared the function's output to distances measured on
a map. I wouldn't use it for missile targeting, but it's sufficiently
accurate for "show me all stores within 20 miles of my home."
Here's the meat of the function (written in C); the coordinates have by
now been converted to radians:
dlat = lat2 - lat1;
dlon = lon2 - lon1;
a1 = sin(dlat / 2.0);
a2 = sin(dlon / 2.0);
a = (a1 * a1) + cos(lat1) * cos(lat2) * (a2 * a2);
c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a));
dist = radius * c;
If anybody's interested I'll post the entire file.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What am I doing wrong in here?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Sat, 27 Dec 2003, Casey Allen Shobe wrote: > > ERROR: permission denied for schema pg_catalog > > The user you create the user as needs to have createuser permission. > alter user "foo" with createuser; > > ...(run as an appropriate user) will grant the user such permission. Hmm, that solved the problem, thanks. But I still could not understand why the lack of createuser permission caused the error above... Regards, - -- Devrim GUNDUZ [EMAIL PROTECTED] [EMAIL PROTECTED] http://www.TDMSoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/7akUtl86P3SPfQ4RAkBRAJ9gHSdmypPYYJRFEkLjgwbcQwB1ugCg1iE2 goLf9LvTeQkblKho+e5yUk8= =Y7Vg -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What am I doing wrong in here?
Devrim GUNDUZ (Saturday 27 December 2003 10:45) > > > ERROR: permission denied for schema pg_catalog > > > > The user you create the user as needs to have createuser permission. > > alter user "foo" with createuser; > > > > ...(run as an appropriate user) will grant the user such permission. > > Hmm, that solved the problem, thanks. > > But I still could not understand why the lack of createuser permission > caused the error above... Because database users (and lots of other database information) is stored in the pg_catalog schema. When you create, alter, or drop a user, you are performing an insert, update, or delete on pg_catalog.pg_shadow. I'm not familiar enough with the internals to say exactly how createuser=t in the same table grants update permission to the user, but that is the effect. Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) [EMAIL PROTECTED] / http://rivyn.livejournal.com Jabber: [EMAIL PROTECTED]; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] What am I doing wrong in here?
Devrim GUNDUZ (Saturday 27 December 2003 10:45) > But I still could not understand why the lack of createuser permission > caused the error above... Though I do think a more clear error in this case would be helpful (*hint hint*). Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) [EMAIL PROTECTED] / http://rivyn.livejournal.com Jabber: [EMAIL PROTECTED]; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Radius of a zip code
Bruno Wolff III <[EMAIL PROTECTED]> writes: > Michael Fuhr <[EMAIL PROTECTED]> wrote: >> Maybe you've already figured it out, but LOAD should allow you to reload >> a .so file without having to restart the backend. > I didn't write that. It came from the person(s) who worked on earthdistance > before I touched it. I've removed the incorrect comment. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What am I doing wrong in here?
Devrim GUNDUZ <[EMAIL PROTECTED]> writes: > And I get: > NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for > "serial" column "tdmalias.mid" > ERROR: permission denied for schema pg_catalog I can't replicate that here: I get NOTICE: CREATE TABLE will create implicit sequence "tdmalias_mid_seq" for "serial" column "tdmalias.mid" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tdmalias_pkey" for table "tdmalias" CREATE TABLE tdmsoftmailserver=> The nearby comments about needing to be superuser to create a user seem to me to be well wide of the mark, since you'd have failed much earlier than this in the script if that were the issue. What do you get from "select * from pg_namespace" in the database where this happens? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] What am I doing wrong in here?
Casey Allen Shobe <[EMAIL PROTECTED]> writes: > Devrim GUNDUZ (Saturday 27 December 2003 10:45) >> Hmm, that solved the problem, thanks. >> >> But I still could not understand why the lack of createuser permission >> caused the error above... > Because database users (and lots of other database information) is stored in > the pg_catalog schema. When you create, alter, or drop a user, you are > performing an insert, update, or delete on pg_catalog.pg_shadow. This analysis is nonsense ... system catalog operations do not do the same kinds of permission checks as user queries do. Furthermore, if he'd not had permissions to create users, the initial CREATE USER command would have failed, and so would CREATE DATABASE (since it would then be specifying a nonexistent owner name). My guess is that Devrim interpreted your suggestion as telling him to make the created user (tdmsoftmailserveruser) a superuser, which would naturally suppress any and all permissions failures for operations executed by that user. That's hardly a reasonable answer to his problem though. As to what his real problem is, I dunno, but I'd like to find out. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] What am I doing wrong in here?
Saturday 27 December 2003 13:50 > This analysis is nonsense ... system catalog operations do not do the > same kinds of permission checks as user queries do. Furthermore, if > he'd not had permissions to create users, the initial CREATE USER > command would have failed, and so would CREATE DATABASE (since it would > then be specifying a nonexistent owner name). The CREATE USER *did* fail, didn't it? I'm sorry if I was inaccurate in my response, but I don't believe I was - I think maybe we're just interpreting the E-Mail two different ways. Devrim, if you could copy and paste exactly what you're doing and exactly where the errors appear in a psql session, that would be most helpful. I did nat say that the user had to be a superuser, only that the user had to have createuser permission in order to CREATE USER. And the last sentence of the first paragraph may not be syntactically accurate because I am not familiar with PostgreSQL's internals, but that is the effect of creating a user, as I said in the second paragraph. > My guess is that Devrim interpreted your suggestion as telling him to > make the created user (tdmsoftmailserveruser) a superuser, which would > naturally suppress any and all permissions failures for operations > executed by that user. That's hardly a reasonable answer to his problem > though. That is *NOT* what I suggested at all. > As to what his real problem is, I dunno, but I'd like to find out. Sure. Vertu sæll, -- Sigþór Björn Jarðarson (Casey Allen Shobe) [EMAIL PROTECTED] / http://rivyn.livejournal.com Jabber: [EMAIL PROTECTED]; ICQ: 1494523; AIM/Yahoo: SomeLinuxGuy Free development contributor of: > KDE toolbar icons > Kopete user interface, usability, and testing > X11 Icelandic Dvorak keymaps > Reporting of over 100 Kopete bugs ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] What am I doing wrong in here?
Casey Allen Shobe <[EMAIL PROTECTED]> writes: >> My guess is that Devrim interpreted your suggestion as telling him to >> make the created user (tdmsoftmailserveruser) a superuser, which would >> naturally suppress any and all permissions failures for operations >> executed by that user. > That is *NOT* what I suggested at all. No, of course not, but that's the only way I can make sense of his response that said your suggestion had fixed the problem. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
