Re: [GENERAL] Zip Code Proximity
Actually I was thinking more on the lines of: select location from test where location @ '((31.6283,93.6347), 1.39)'::circle; The above lat/lon is for: Zwolle, LA If Zylks, LA (lat/lon = 32.9696,93.9801) is also in the DB. The above query should select Zylks since 1.39 * 62.1 = 86 miles approx. 86 miles is about correct for those two zip codes. Wouldn't this be much easier? Is the data type POINT index-able? Yes, I have all of the zip codes and lat/lon information. Thanks Andy On Thu, 18 May 2000, Jeff Hoffmann wrote: Andy Lewis wrote: Hello All, I know there's been quite a few posts on Zip Code Proximity. Can anyone point me in the right direction to find the code to calulate the distance between two zip codes? I'm basically trying to take a zip code given by a user and return them all of the zip codes within, say 10 miles or 20 miles. I've tried the mailing list search but, they seem to be down or not available. Thanks Andy i'm surprised that nobody else has apparently responded. first you need to have a table of zipcodes lat-longs for those zip codes. it may take a little looking, but you should be able to find that. now take a look at the earthdistance function in the contrib directory of the distribution. assuming your table is something like: create table zipcodes ( zip int4, location point); next populate the table with the zipcodes next install the earthdistance function assuming you know the lat,lon of the zipcode in question, you can query the table with something like this. it'll pick the 10 closest zipcodes and order them by the closest: select zip, location @ '(lat, lon)'::box from zipcodes order by location @ '(lat, lon)'::box limit 10; i'll leave using indexes as an exercise for the reader. it may or may not help depending on whether you have all the zipcodes for the country or not. plus i don't know if this is going to work. it should, but i haven't tested it.
Re: anybody know who the damn list owner is? RE: [GENERAL] Postgresqlism Vacuum?
For starters I head over to www.postgresql.org Then I'd probably click on the "Info Central" link and then the "Mailing lists" link. From there I'd click on the list that I was subscribed to under "Mailing List Archives" Then I'd read the info at the top of the page. Those pages haven't really changed in content in years On Fri, 14 Apr 2000, Oelkers, Phil wrote: I've been trying to get off this list forever no luck. I thought I got the damn thing set to nomail, still get mail. DOES ANYBODY KNOW WHERE I CAN CONTACT A LIKE PERSON TO GET OFF THIS DAMN LIST? -Original Message- From: Peter Eisentraut [SMTP:[EMAIL PROTECTED]] Sent: Friday, April 14, 2000 6:59 AM To: Thomas Cc: [EMAIL PROTECTED] Subject:Re: [GENERAL] Postgresqlism Vacuum? On Fri, 14 Apr 2000, Thomas wrote: I think there must be something wrong with the optimiser that it's "postgresqlism" that you must vacuum analyze frequently. One thing that is not widely known is that vacuum actually has two orthogonal tasks: garbage collection and statistics collection (only when you ANALYZE). The fact that this is combined in one command is a historical artifact, and there are some voices that want to separate the commands. The way I see it, if you have enough disk space you never have to run vacuum to garbage collect. It might lead to obvious problems when the heap files get so large that it takes more time to physically access them. The alternative is to garbage collect on each transaction commit but that bears its own set of performance implications. The analyze part would probably not need an exclusive lock on the table but the vacuum certainly does. -- Peter Eisentraut Sernanders väg 10:115 [EMAIL PROTECTED] 75262 Uppsala http://yi.org/peter-e/Sweden
Re: [GENERAL] Web-based PSQL?
The URL http://www.vale-it.co.uk doesn't work for me. Andy On Wed, 29 Mar 2000, Moray McConnachie wrote: Try pgadmin, which is not web-based, but allows you to do all these things. Only runs from MS platform, though. http://www.vale-it.co.uk Alternatively, it would be mind-numbingly simple to write (e.g.) a Perl CGI interface to allow psql access . Have to make sure it was well-secured, though. Indeed, I might have a go at it this afternoon... M. [EMAIL PROTECTED] - Original Message - From: "Stan Jacobs" [EMAIL PROTECTED] To: "Egon Schmid" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, March 29, 2000 9:33 AM Subject: Re: [GENERAL] Web-based PSQL? I know this has a standard SQL interface, but I was wondering if there was an existing web app that let you show schemas, create tables, edit data, and all of that from one simple interface. Does PHP provide this? Sorry for my confusing question. :-) On Wed, 29 Mar 2000, Egon Schmid wrote: Stan Jacobs wrote: is there a version of psql that runs via web interface? It seems like it would be rather trivial to build if it doesn't already exist, right? (And generally, at least to me, "trivial" means someone's already done it. *smile*) PHP: http://www.php.net/ http://www.php.net/version4 http://www.zend.com/ -Egon -- Six Offene Systeme GmbH. Stuttgart - Berlin - New York. Fon +49 711 9 90 91 64 · Fax +49 711 9 90 91 99 · http://www.six.de/
Re: [GENERAL] Hardware optimising
What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian| http://www.op.net/~candle [EMAIL PROTECTED]| (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Hardware optimising
Thanks for the info! Much appreciated! Andy On Thu, 26 Aug 1999, Mike Mascari wrote: --- Andy Lewis [EMAIL PROTECTED] wrote: What scheduler are we speaking of here? Andy On Thu, 26 Aug 1999, Bruce Momjian wrote: as for the processor, this will see an increase, of course. note, however, that since PostgreSQL is _not_ multithreaded, that it will run only on one of the processors. (i'm about to assume you are using linux here... 'scuse me if i'm wrong) however, the good news is that you can encourage linux (through the scheduler) to run postgres on one of the processors and everything else on the other one. this should give the database its own processor more oft than not. things may still drift, etc... but it will be better this way Different backends can use different CPU's, no problem. -- Bruce Momjian Bruce, of course, is, as always, absolutely correct. Each connection to the backend starts a postgres process which will be assigned to either CPU by Linux. I have read (either somewhere in the SMP FAQ or some mailing list) that there are utilities forthcoming (this was awhile ago) to assign a process to a specific CPU. There are several advantages to having a multithreaded backend instead of a multitasking backend since connections would be faster, no need for shared memory segments, etc., but use of multiple processors is not exclusive to multithreading applications. Any application which forks() or execs() another can take advantage of multiple processors. And there are disadvantages to multithreading too as pointed out in previous threads (no pun intended), such as stability of the running process if one of its threads dies abnormally. With regard to the original post, I again, agree fully with Bruce - SCSI first. And spend an extra couple hundred to get the 80MBs variety, dual channel controllers; its worth it. Hopefully one would also be able to optimize the disk configuration as well. We run RedHat Linux 2.0.36 on a Dual 450Mhz deskside server with 256M of RAM. The only regret I have is we didn't get the 80MBs (we got 40MBs) controller and (6) 4 Gig hard drives. Instead we got (2) 9 Gig drives. This forces us to only run RAID 1. For only a few hundred more, we could have run RAID 0+1 on dual channels (with each mirror on the other channel). We also put the database on the second innermost partition, with the outer being swap. Finally, if you are using Linux and choose to go the SMP route, I highly recommend the newer 2.2 kernels. We saw dramatic improvement in speed over 2.0.36 vs. 2.2.x in our testing environment. In fact, to enable SMP on a 2.0.36 kernel, you must modify the top-level Makefile for the kernel and rebuild. Anyways, Hope that helps, Mike Mascari ([EMAIL PROTECTED]) P.S. From previous posts, I'm starting to think that there is a VAST misconception that a single-threaded database engine (which is what Oracle was until some version 7 releases, I believe, called Oracle MTS appeared) can only handle ONE query at a time, and does not exec() a child process for each connection. Someone ought to start the propoganda of claiming multi-threaded DBMS as "single process" servers. __ Do You Yahoo!? Bid and sell for free at http://auctions.yahoo.com
Re: [GENERAL] PL/pgSQL - mailng list
The only thing that I know is whats in the docs and whats in the src directory. I'd like to see/read more. I was going to pick up a Oracle book on it but, I'm afraid that PL/pgSQL and PL/SQL aren't the same. Thanks Andy On Sat, 22 May 1999, dustin sallings wrote: On Sat, 22 May 1999, Andy Lewis wrote: How can we tell you more if we don't know what you know? It's usually pretty safe to just go ahead and ask the question. You'll be redirected if you've posted to the wrong place. // I'd just plain would like to know more about PL/pgSQL. // // Andy // // On Sat, 22 May 1999, Piotr Stelmaszyk wrote: // // I'd like to get to know where should I post messages concerning // PL/pgSQL. // // // // // /- Piotr Stelmaszyk | Student of Computer Science ---/ // /-- | at Poznan University of Technology / // /- mailto:[EMAIL PROTECTED] -/ // /- mailto:[EMAIL PROTECTED] ---/ // // // // // // // // -- Principal Member Technical Staff, beyond.comThe world is watching America, pub 1024/3CAE01D5 1994/11/03 Dustin Sallings [EMAIL PROTECTED] |Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE L__ and America is watching TV. __
Re: [GENERAL] Full Text Searches
Its not really, really explanitory. On Mon, 24 May 1999, Brett W. McCoy wrote: On Sun, 23 May 1999, Bruce Momjian wrote: We have a fulltext stuff in the contrib directory. What's it called? I only see some tcl frontend stuff. Despite my pessimism form the prior message, I am interested in a full text retrieval engine. Brett W. McCoy http://www.lan2wan.com/~bmccoy/ --- Lonely is a man without love. -- Englebert Humperdinck
Re: [GENERAL] Weird datetime problem
I was thinking that it may be a timezone thing. Some of the dates are of CDT and some are CST. I live in Dallas which is the Central Time Zone. I just don't understand. I seem to have alot of time/date problems with Postgres. Maybe its just me. Actually, now I see what is going on. I defined this column as type datetime with a default to now(). At one time the default worked ok. Now it doesn't work at all. Perhaps this is a bug? If I insert a record and not state a date_submitted value the value that I get when I select that row is an eroneous date. However if I use now() even though it should default to now(), it works fine. Maybe this message would be better posted in the hackers list. Andy d, 12 May 1999, Chris Bitmead wrote: It's not a timezone problem is it? Did the dates get re-loaded correctly? Andy Lewis wrote: I have 2 tables both of which have a datetime field and both have an identifier of date_submitted. I've been using(via PHP) a query of the following for a month or so with no problem: select count(*) from mytable1 where date_submitted 'today'::datetime and select count(*) from mytable2 where date_submitted 'today'::datetime Both queries were working fine until I dumped the data in mytable2 to file and reloaded it. Now I can enter a date and not get the rows that I put in today(or any other day). The date_submitted has a btree index on it. I've even dropped and created a new index and that still hasn't helped. I still come up with 0 rows found. All I'm trying to do is get the number of rows submitted since midnight. Oh, yeah, Postgres 6.4.2, PHP 3.0.7, Linux Slackware 2.0.35, Dual Pent 450's. Anyone have any ideas? Thanks in advance. Andy
Re: [GENERAL] PLpgSQL Stat Problem
Postgres is allowed to access that file. No error message on the:
cat plpgsql.so /dev/null
Andy
On Wed, 28 Apr 1999, Oleg Broytmann wrote:
On Wed, 28 Apr 1999, Andy Lewis wrote:
I got the following error after trying to copy the example at:
http://www.postgresql.org/mhonarc/pgsql-sql/1999-04/msg00076.html
--
test= select a(pin,first_name) from ibs_subscriber ;
ERROR: stat failed on file ${exec_prefix}/lib/plpgsql.so
--
The file plpgsql.so does exist in /usr/local/pgsql/lib.
Any ideas?
What are permissions on the file plpgsql.so? Does postgres allowed
access to the file? Check it:
su - postgres
cat plpgsql.so /dev/null
Andy
Oleg.
Oleg Broytmann http://members.xoom.com/phd2/ [EMAIL PROTECTED]
Programmers don't die, they just GOSUB without RETURN.
Re: [GENERAL] PL/pgSQL
Thanks I'll give it a try! Andy On Tue, 27 Apr 1999, [iso-8859-1] José Soares wrote: Andy Lewis ha scritto: How does one compile or add PL/pgSQL if it doesn't already exist? I've searched the DOCS and mailing list and the best that I can come up with is an enable script that complains about the missing .sql file. I'm running 6.4.2 cd postgresql-v6.4.2/src/pl/plpgsql and run: make clean make all make install to compile and install plpgsql.so then create language by: cd postgresql-v6.4.2/src/pl/plpgsql/src psql mklang.sql José
RE: [GENERAL] SELECT Date
I tried this but, got the following: --- mytest= select fagentname from feedback where ftdate between now()::date and mytest- (now()::datetime - '@ 7 days'::timespan)::date; ERROR: function date(timestamp) does not exist --- I have this column identified as type date and not timestamp. Any suggestions? On Thu, 8 Oct 1998, Jackson, DeJuan wrote: I have a table with somedate defined as date. I want to select all rows that are from todays date back to 7 days ago. Or in this case 10-1-1998. Use the function age() somewhat like this: select * from sometable where date_part( 'epoch' , age( 'now', somedate ) ) 604800 The 'epoch' date part is the total number of seconds between the two dates. For other variations, see: http://www/postgresql.org/docs/user/functions1574.htm Why don't you just use between: select * from sometable where somedate between now()::date and (now()::datetime - '@ 7 days'::timespan)::date; -DEJ
