[SQL] Problem with function...
Hi, I hope my question is appropriate for this list. I'm trying to create a function that calculates the distance between a pair of latitude/longitude points. This is what I have: /* latlon_distance.pgsql * by Jamu Kakar <[EMAIL PROTECTED]>, Dec 18, 2000. * * Calculates the distance between 2 lat/lon pairs. Syntax: * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. */ CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' DECLARE radius constant float8 := 6378; distance float8; lat1 ALIAS FOR $1; lon1 ALIAS FOR $2; lat2 ALIAS FOR $3; lon2 ALIAS FOR $4; BEGIN distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + (cos (radians (lat1)) * cos (radians (lat2)) * cos (radians (lon1) - radians (lon2; RETURN ''distance''; END; ' LANGUAGE 'plpgsql'; When I try a: select distance(49.0,-122.0,50.0,-123.0) as distance; I get: ERROR: Bad float8 input format 'distance' I've tried a variety of ways of specifying the values and I've hunted through the mailing lists but haven't turned up anything useful. Any help would be appreciated. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 988-6999 North Vancouver, BC, V7M 2J5
[SQL] Bounds checking on an alias
Hi, Removing the double single-quotes from the distance function fixed the problem, thanks. I've now encountered another problem... I have the following (ugly) query: SELECT DISTINCT tbl_restaurant.restaurant, tbl_restaurant_location.postal_code, tbl_restaurant_location.latitude AS latitude, tbl_restaurant_location.longitude AS longitude, distance (49.24894, -122.90419, latitude, longitude) AS distance FROM tbl_restaurant, tbl_restaurant_location, tbl_restaurant_cuisine_link WHERE tbl_restaurant.restaurant_id = tbl_restaurant_location.restaurant_id AND tbl_restaurant.restaurant_id = tbl_restaurant_cuisine_link.restaurant_id AND tbl_restaurant_cuisine_link.cuisine_id = 14 AND tbl_restaurant.price_id = 1 AND tbl_restaurant_location.latitude IS NOT NULL AND tbl_restaurant_location.latitude > 49.113804 AND tbl_restaurant_location.latitude < 49.384075 AND tbl_restaurant_location.longitude > -123.03932 AND tbl_restaurant_location.longitude < -122.76906 AND distance <= 15.0 ORDER BY distance; What I'm doing is selecting all restaurants of a particular cuisine and within a particular price range. Out of the restaurants that meet those criteria I only want the ones within a certain range of my location- in this case, 49.24894, -122.90419. The way I narrow down the search results is to select only those restaurants within certain lat/lon points; basically, I'm selecting restaurants that fall in a square region with my location in the middle. Then, I'm applying my distance formula to filter these results. When I try and do a "distance <= 15.0" I get: ERROR: Attribute 'distance' not found If I remove that particular clause it works fine but my results aren't as accurate as they need to be. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 988-6999 North Vancouver, BC, V7M 2J5
[SQL] How to trim values?
Hi, I'm trying to figure out how to take a value like 3.68009074974387 (that is calculated from values in my database) and have PostgreSQL hand me 3.68. Any suggestions would be appreciated. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
[SQL] Weird problem with script...
Hi, I'm building a script to create the tables in my database. Everything works fine except one thing with this part of my script: create table tbl_resume_free_text_type ( type_id int, type text ); -- insert into tbl_resume_free_text_type (type_id, type) values (1, 'Hobbies'); -- insert into tbl_resume_free_text_type (type_id, type) values (2, 'Special Talents'); If I uncomment the two insert statements I get an error message stating that tbl_resume_free_text_type doesn't exist. With the lines commented out the table gets created (and the rest of the script runs error-free) and I can manually insert these rows afterwards without a problem... I'm running PSQL 7.0.3. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
[SQL] Weird script problems solved...
Hi, About a week back I'd posted a message asking for help with a script that as far as I could tell was well formed. The problem was with 2 INSERT statements directly after the CREATE TABLE statement of the table that I was trying to insert data into. I'd used C single/multi line comments of the form: /* woo */ and /* foo * bar * baz */ throughout the script. In order to determine what the problem was I ran the script through a C preprocessor to remove the comments. Without comments the script worked as expected. I replaced the C style comments with SQL '--' comments and the script also worked as expected. Unfortunately, I didn't maintain a copy of the C commented script to help PSQL developers but thought this important to mention anyway. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6999 North Vancouver, BC, V7M 2J5
[SQL] Automated scripting...
Hi, I'm building a script to create a relatively large database. At some point in the script I would like to be able to save values into variables so that I can use them to populate rows. Is this possible with SQL? My understanding is that it is not possible but thought I'd ask anyway. Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
[SQL] timestamp- milliseconds since epoch output
Hi, I'm using a timestamp field called date_created. Whenever I select it I get: select date_created from tbl_user; date_created 2001-02-05 17:23:26-08 2001-02-05 17:45:39-08 2001-02-03 03:58:53-08 (3 rows) I've tried using variations of to_char and to_timestamp but can't seem to get the timestamp as a value of milliseconds since the Epoch (Jan 1, 1970). I've been looking through the user manual but can't seem to find anything... might be nice to put it in there as this is probably a very common operation. Any ideas? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5
[SQL] Re: Normalization is always good?
Hi Josh, > normal form, most of the time. And I do a few things (such as > polymorhic sub-tables) that would give Fabian Pascal fits :-) Polymorphic sub-tables? =) Do you mean using, for example, one user table that stores different types of users and has some fields specific to only some kinds of users and other fields that are shared by all users? > However, there is (in my mind) no question as to whether a database > should be normalized, just how much effort is spent on normalization as > opposed to other considerations (UI, performance, development time). I agree. Anyone here familiar with "Extreme Programming"?- they suggest that you should architect your software only for the functionality you need NOW. While some aspects of extreme progrmming have proven themselves true in the face of my skepticism I see it as my moral obligation as programmer to write programs that are A) as portable as possible and B) extensible. With that in mind, I like normalization because it increases the likelihood of scalability being relatively painless. > What do you use? I've given up on OODB interfaces, myself, as I did not > find them helpful, but that's modtly because I was working in MS-land. I've been working in Java with JDBC... I've actually shot myself in the foot a bit by not making my application object oriented enough. In hindsight, and if time permitted more refactoring, I would have written custom data objects to manage all interactions with the database so that any management of data happened in one and only one place and anything that needed to access those objects would do so through the common interface. > Yup. http://www.databasedebunking.com/ Dig through the archives. Couldn't find the server... I wonder if it's still there. Regards, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] serial type; race conditions
Hi, I'm using serial fields to generate IDs for almost all object in my database. I insert an empty row, get the CURRVAL() of the sequence and then update to that value. I had understood (and now, I can't find the reference to back this up) that serial is implemented in such a way that race conditions between DB connections can't happen. Is this true? Thanks, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Need to do an ALTER TABLE.
Hi, I've got a live database running PSQL 7.0.3. I need to do a couple of changes to some of the table schema's but need to preserve the data that currently exists in the tables. I've used pg_dump to make backups and have verified that I can indeed restore into an empty database from those backups; I'm glad I did this as I found out I have to use 'pg_dump -d' to get a useful backup. =) I need to perform three slightly different ALTER TABLE type jobs: 1. I need to change a column from numeric(8,2) to text. 2. I need to add a column or two to a couple of tables. 3. I need to drop a column or two from a couple of tables. Initially my thought was to create a new temporary table, SELECT INTO it from my original table, drop the original table, re-create it as I need it and do a SELECT INTO from the temporary table back to the new table. The thing I'm unsure of is what will happen to referential integrity? The tables I need to modify are referenced by other tables- will those other tables realise that they should re-establish foreign key references? If not automatically, will VACUUM ANALYZE do this for me? I'm going to experiment in my test database but figured this might be an interesting topic to discuss anyway. Also, if any good advice/answers exist perhaps they should go in the FAQ? Any suggestions would be appreciated. Cheers, Jamu. -- Jamu Kakar (Developer) Expressus Design Studio, Inc. [EMAIL PROTECTED]708-1641 Lonsdale Avenue V: (604) 903-6994 North Vancouver, BC, V7M 2J5 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
