Re: [GENERAL] Finding points within 50 miles
I'm interested in doing a project for calculating distances similar to
this. Anyone have suggestions on how/where this type of data can be
obtained? Is it freely available anywhere?
On 6/27/05, Uwe C. Schroeder <[EMAIL PROTECTED]> wrote:
>
> Actually it does.
> I'm using a bounding box too. I have a stored procedure to get me what I need
> - here's the relevant part of it.
> Explanation: zc is the record holding the point of origin. I just added the
> maxdistance definition for this, because in my function its a parameter.
>
>
>
>
> SELECT INTO zc z.* FROM v_profile p JOIN zipcodes z ON
> z.zipcode=p.zipcode WHERE p.uid=uid;
> IF NOT FOUND THEN
> RAISE EXCEPTION \'Cant find member %\',uid;
> END IF;
> maxdistance:=50;
> la_min:=(zc.latn - (maxdistance::float8/70.0));
> la_max:=(zc.latn + (maxdistance::float8/70.0));
> lo_min:=(zc.longw - (maxdistance::float8/70.0));
> lo_max:=(zc.longw + (maxdistance::float8/70.0));
>
>
> stmt:=''SELECT n.username, n.uid, n.areacode, n.zipcode
> geo_distance(point('' || zc.longw ||'',''||
> zc.latn ||''),point(z.longw, z.latn))::int as distance,
> n.image_thumbnail,n.city, n.state_code
> FROM v_new_members n JOIN zipcodes z ON
> z.zipcode=n.zipcode
> AND (z.latn BETWEEN '' || la_min || '' AND ''
> || la_max || '')
> AND (z.longw BETWEEN '' || lo_min || '' AND
> '' || lo_max || '') AND
> geo_distance(point(''|| zc.longw
> ||'',''||zc.latn||''),point(z.longw, z.latn))::int <= ''||maxdistance ;
>
>
>
>
> hope that helps
>
> UC
>
>
> On Monday 27 June 2005 02:08 am, you wrote:
> > Uwe C. Schroeder wrote:
> > >in the where clause use something like (requires the earthdistance contrib
> > > to be installed):
> > >
> > >geo_distance(point([origin longitude],[origin latitude]),point([target
> > >longitude column],[target latitude column])))::int <= 50
> >
> > I don't suppose geo_distance really returns a number in miles, does it?
> >
> >
> > ---(end of broadcast)---
> > TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
>
> --
> UC
>
> --
> Open Source Solutions 4U, LLC 2570 Fleetwood Drive
> Phone: +1 650 872 2425 San Bruno, CA 94066
> Cell: +1 650 302 2405 United States
> Fax:+1 650 872 2417
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Version Control?
How would you handle the migration of the data with these user scripts? Dump it to a temp table? On 6/9/05, elein <[EMAIL PROTECTED]> wrote: > Up until the database goes into production, > keep files: schema.sql (table creation), > views.sql, functions.sql triggers.sql trigfunctions.sql > in cvs/svn. > > Afterwards any changes to the schema are in > change01.sql, change02.sql,... > > The change scripts hold the alter table statements > for schema changes. They must be cumulative. > Ideally you'd have corresponding undochange01.sql > but that is icing. > > Never let anyone change the database without creating > the appropriate change script. > > --elein > > On Thu, Jun 09, 2005 at 03:25:14PM -0500, Peter Fein wrote: > > Hi- > > > > Any general tips on using version control (CVS, SVN) while doing > > database design? My thought was to do a text-mode dump (including > > populated code tables) from PGAdmin. > > > > How do people do this? > > > > -- > > Peter Fein [EMAIL PROTECTED] 773-575-0694 > > > > Basically, if you're not a utopianist, you're a schmuck. -J. Feldman > > > > ---(end of broadcast)--- > > TIP 5: Have you checked our extensive FAQ? > > > >http://www.postgresql.org/docs/faq > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] monetary data
I was just curious how you guys implement storage / calculation of monetary data in postgresql. The docs say to use the numeric data type, but I'm curious what precision is typically defined for storing monetary data in the numeric data type. Thanks for any info... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Postgres, tablespaces, and quotas
I was just curious about what would happen given the following scenario: - Unix user has their own tablespace (and corresponding database) stored in /home/someuser/mydata - Quotas are enabled on /home - User reaches their quota, effectively causing only this tablespace to run out of disk space. How would postgres handle this scenario? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Tablespaces on partition with quotas
I was just curious about the following scenario: - Unix user has their own tablespace (and corresponding database) stored in /home/someuser/mydata - Quotas are enabled on /home - /home runs out of disk space What happens to the user's tablespace? How does postgres handle this scenario? ---(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: [GENERAL] DDL from psql console?
Actually, that's a thought.. I could even create a bash wrapper script so I wouldn't have to type the database name each time. Will give it a shot. Thanks On 4/25/05, Michael Fuhr <[EMAIL PROTECTED]> wrote: > On Mon, Apr 25, 2005 at 10:44:14AM -0500, John Browne wrote: > > > > Yeah, I know about pg_dump. I just was curious if there was another > > way, since I always have two psql consoles already open at all times > > anyway. :-) > > You could do "\!pg_dump ..." > > -- > Michael Fuhr > http://www.fuhr.org/~mfuhr/ > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] DDL from psql console?
Yeah, I know about pg_dump. I just was curious if there was another way, since I always have two psql consoles already open at all times anyway. :-) On 4/25/05, John Browne <[EMAIL PROTECTED]> wrote: > Hello, > > I was curious if there was a way to get the DDL for a particular table > from the psql client console? I have two postgres boxes (development > and production) and would like to copy & paste the DDL "CREATE TABLE" > statements from the development console to the production console when > I'm moving a particular table definition over. I tried \dt+ but it > didn't appear to show it. > > Any thoughts? > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] DDL from psql console?
Hello, I was curious if there was a way to get the DDL for a particular table from the psql client console? I have two postgres boxes (development and production) and would like to copy & paste the DDL "CREATE TABLE" statements from the development console to the production console when I'm moving a particular table definition over. I tried \dt+ but it didn't appear to show it. Any thoughts? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Table modifications with dependent views - best practices?
Ok, I've been using postgres for a-while now, and am curious how you guys handle this. What is the best way to make modifications to tables that have lots of dependent objects, like views? Do you just do the obvious drop...cascade and manually re-create your views? Do you keep your "create view" statements in a text file for this purpose or just pull them from a pg_dump file? Is there a better way to handle this sort of thing? I know on a properly designed database, changes to the schema are usually not necessary. However, during development, database tweaks (at least for me) are pretty common. Any thoughts? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Query buffer editing on Win32 version of postgresql?
I was attempting to set up my psql client on the Win32 version of postgres 8.0 beta 2 to be able to use an external editor. I set the environment variable in windows like so: PSQL_EDITOR="c:\progra~1\Textpa~1\Textpad.exe" which does appear to work correctly. However, I get the following when attempting to edit the query buffer: db_merrymaids=# \e could not open temporary file ".\psqA8C.tmp": File exists db_merrymaids=# If I specify a file using db_merrymaids=# \e somefile.txt Textpad does launch and ask to create the new file. But, I don't need to create a new file. I need to edit the built-in query buffer. :-) Any ideas why I'm getting the "File exists" error on the temp file? Bug maybe? Thanks for any help ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
