Re: [SQL] Multiple DB servers on a single machine
> I am having an RH Linux 7.3 box which is already running an Postgres > 7.3.4 server. I want to install Postgres 7.4 on the same machine. Is > it possible to install different versions of Postgres in a single RH > Linux 7.3 box and the postmaster up and running for both (7.3.4 & 7.4 > database servers). Yes, you can run multiple servers on a single machine, provided on different port & data directory. Change PGDATA & PGPORT variables in your init.d script. regards, bhuvaneswaran signature.asc Description: This is a digitally signed message part
[SQL] Anti log in PostgreSQL
Dear all , In one of our project I require to calculate antilog of (3.3234) But I could not find any functions in Documentation for the same. In mathematics I would have written it something like A = antilog (3·3234) = 2144 Any suggestions or links are most welcome . Regards, Vishal Kashyap. ---(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] Anti log in PostgreSQL
El Vie 26 Dic 2003 19:12, Sai Hertz And Control Systems escribió: > Dear all , > > In one of our project I require to calculate antilog of (3.3234) > But I could not find any functions in Documentation for the same. > > In mathematics I would have written it something like > > A = antilog (3·3234) = 2144 As I can understand, this is a 10 base log, so that what you want is 10^(3.3234)? For that you have the exponential operator ^. -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Anti log in PostgreSQL
Dear Wei Weng , Nope select exp(3.3234) as a2144 Gives me 27.754555808589792 But the answer expected is some what near to 2144 The log tables show this Regards, Vishal Kashyap. What is the mathematical operation that "antilog" is supposed to perform? Its going to calculate rate of intrest for an fixed deposit , Iknow theirs other metod also bu theoratically using antilog you get calculations bang corect This is inverse of log Now doing select 1/log(3.3234) does not help _ Vishal Kashyap ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Anti log in PostgreSQL
The antilog of x is 10^x, so all you need to do is used the ^ operator. If you are doing the antilog for some other base, there is formula to do that as well, but I'm forgetting it. Regards, Yasir On Fri, 26 Dec 2003, Martin Marques wrote: > Date: Fri, 26 Dec 2003 19:34:35 -0300 > From: Martin Marques <[EMAIL PROTECTED]> > To: [EMAIL PROTECTED], > Sai Hertz And Control Systems <[EMAIL PROTECTED]>, > [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] Anti log in PostgreSQL > i> El Vie 26 Dic 2003 19:12, Sai Hertz And Control Systems escribió: > > Dear all , > > > > In one of our project I require to calculate antilog of (3.3234) > > But I could not find any functions in Documentation for the same. > > > > In mathematics I would have written it something like > > > > A = antilog (3·3234) = 2144 > > As I can understand, this is a 10 base log, so that what you want is > 10^(3.3234)? > > For that you have the exponential operator ^. > > -- > select 'mmarques' || '@' || 'unl.edu.ar' AS email; > - > Martín Marqués |[EMAIL PROTECTED] > Programador, Administrador, DBA | Centro de Telemática >Universidad Nacional > del Litoral > - > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(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] Anti log in PostgreSQL
Sai Hertz And Control Systems <[EMAIL PROTECTED]> writes: >> What is the mathematical operation that "antilog" is supposed to perform? > Its going to calculate rate of intrest for an fixed deposit , This is not a mathematical operation, it is a financial issue that has to conform to rules developed long ago by bankers. The closest thing that PG offers is 10^x, but regression=# select 10^(3.3234); ?column? -- 2105.71698391175 (1 row) which is not real close to the 2144 that you say you want. The difference must be due to compounding rules, and perhaps also 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. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Radius of a zip code
Title: Message
Hello
all,
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.
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
Anyone that has this
experience, can you validate this for correctness?
Thanks in
advance,
Andy
Re: [SQL] Radius of a zip code
"Andy Lewis" <[EMAIL PROTECTED]> writes:
> I was trying to find all zip codes within a given zip code or radius.
I think there are canned solutions for this available in PostGIS ---
have you looked at that?
> 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
I'm guessing that the big problem is that you didn't measure longitude
and latitude in identical units in your table, so your "circle" isn't
real circular, and the smaller problem is that "miles" converts to
"degrees of arc" differently at different latitudes.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] Anti log in PostgreSQL
Dear Martin Marques , In mathematics I would have written it something like A = antilog (3·3234) = 2144 As I can understand, this is a 10 base log, so that what you want is 10^(3.3234)? For that you have the exponential operator ^. Nope select exp(3.3234) as a2144 Gives me 27.754555808589792 But the answer expected is some what near to 2144 The log tables show this Regards, Vishal Kashyap. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Anti log in PostgreSQL
Sai Hertz And Control Systems wrote: Dear Martin Marques , In mathematics I would have written it something like A = antilog (3·3234) = 2144 As I can understand, this is a 10 base log, so that what you want is 10^(3.3234)? For that you have the exponential operator ^. Nope select exp(3.3234) as a2144 Gives me 27.754555808589792 But the answer expected is some what near to 2144 The log tables show this Regards, Vishal Kashyap. What is the mathematical operation that "antilog" is supposed to perform? Wei ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Anti log in PostgreSQL
El Vie 26 Dic 2003 19:46, Sai Hertz And Control Systems escribió: > Dear Martin Marques , > > >>In mathematics I would have written it something like > >> > >>A = antilog (3·3234) = 2144 > >> > >> > > > >As I can understand, this is a 10 base log, so that what you want is > >10^(3.3234)? > > > >For that you have the exponential operator ^. > > > > > Nope > select exp(3.3234) as a2144 > Gives me > 27.754555808589792 > But the answer expected is > some what near to 2144 > The log tables show this As I said, log10, not natural logaritm prueba=> select 10^3.3234 AS res; res -- 2105.71698391175 (1 row) Octave gives me this: octave:1> 10^3.3234 ans = 2105.7 What is 2144? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - ---(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] 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] Radius of a zip code
Michael Fuhr wrote:
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
[...snip...]
Here's the meat of the function (written in C); the coordinates have by
now been converted to radians:
[...snip...]
If anybody's interested I'll post the entire file.
FWIW, here's a plpgsql function I wrote a while ago based on the
Haversine formula:
CREATE FUNCTION "zipdist" (float8,float8,float8,float8 ) RETURNS float8 AS '
DECLARE
lat1 ALIAS FOR $1;
lon1 ALIAS FOR $2;
lat2 ALIAS FOR $3;
lon2 ALIAS FOR $4;
dist float8;
BEGIN
dist := 0.621 * 6371.2 * 2 *
atan2( sqrt(abs(0 + pow(sin(radians(lat2)/2 -
radians(lat1)/2),2) + cos(radians(lat1)) * cos(radians(lat2)) *
pow(sin(radians(lon2)/2 - radians(lon1)/2),2))),sqrt(abs(1 -
pow(sin(radians(lat2)/2 - radians(lat1)/2),2) + cos(radians(lat1)) *
cos(radians(lat2)) * pow(sin(radians(lon2)/2 -
radians(lon1)/2),2;
return dist;
END;
' LANGUAGE 'plpgsql';
I used the following PHP code to start looking for a match in a small
circle, and then expand it if no matches were found:
$dist = INIT_DIST;
$cnt = 0;
$cntr = 0;
do {
if ((! $zip == "") && (! $dist <= 0)) {
$sql = get_zip_sql($lon1d,$lat1d,$dist,$numtoshow);
$rs = connexec($conn,$sql);
$rsf = rsfetchrs($rs);
$dist *= 2;
$cntr++;
} else {
$cntr = 10;
}
} while (count($rsf) < $numadvisorstoshow && $cntr < 10);
Hopefully you get the idea. You can narrow the results using a box to
make the query perform better, and then sort by distance to get the
closest alternative. Here's the related part of get_zip_sql():
function get_zip_sql($lon1d,$lat1d,$dist,$numtoshow)
{
$sql = "
SELECT DISTINCT
FROM tbl_a AS a
,tbl_d AS d
,tbl_a_zipcodes AS az
,tbl_zipcodes as z
WHERE
abs(z.lat - $lat1d) * 60 * 1.15078 <= $dist
and abs(z.long - $lon1d) * 60 * 1.15078 <= $dist
and zipdist($lat1d,$lon1d,lat,long) <= $dist
and z.zip = az.zipcode
ORDER BY
LIMIT $numtoshow;
";
return $sql;
}
The "X * 60 * 1.15078" converts differences in degrees lat/long into
rough distances in miles.
Hope this helps.
Joe
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] MD5 encrypt
On Wed, Dec 24, 2003 at 11:47:53AM -0500, [EMAIL PROTECTED] wrote: > > Is there any function on postgresql that allows to encrypt data when > making an Insert statment? What kind of encryption are you looking for? MD5 is a one-way hash: you can't decrypt the hash to get back the original plaintext. If that's what you need (e.g., for storing passwords), then you could use the md5() function that's part of the the standard distribution since version 7.4. http://www.postgresql.org/docs/current/static/functions-string.html For older versions of PostgreSQL or for additional encryption functions, see the code in contrib/pgcrypto. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Radius of a zip code
On Fri, Dec 26, 2003 at 19:42:44 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote: > > 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: The distance operator in contrib/earthdistance got changed to use haversine instead of the naive formula in 7.3. In 7.4 it also provides some functions that work with contrib/cube that allow for indexed searches. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] Radius of a zip code
On Fri, Dec 26, 2003 at 10:34:04PM -0600, Bruno Wolff III wrote: > On Fri, Dec 26, 2003 at 19:42:44 -0700, > Michael Fuhr <[EMAIL PROTECTED]> wrote: > > > > 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: > > The distance operator in contrib/earthdistance got changed to use > haversine instead of the naive formula in 7.3. In 7.4 it also provides > some functions that work with contrib/cube that allow for indexed > searches. I'll have to take a closer look at contrib/earthdistance. I'm using the function I wrote for legacy reasons -- I had ported an application from another DBMS to PostgreSQL and wanted to make as few changes as possible, so I ported the haversine() function that I had already written. 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. http://www.postgresql.org/docs/current/static/sql-load.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Anti log in PostgreSQL
Dear Martin Marques and other kind people out their , In mathematics I would have written it something like A = antilog (3·3234) = 2144 As I can understand, this is a 10 base log, so that what you want is 10^(3.3234)? Though antilog did not solve my problem the link below helped me to prove my point to my bankers http://www.ilovemaths.com/3depreciation.htm http://mathforum.org/dr.math/faq/faq.interest.html Thanks a load for the kind help forwarded by you all. Regards, Vishal Kashyap For that you have the exponential operator ^. ---(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
