[SQL] Point and function help
Title: Message
Hello all merry
XMAS!
I'm trying to create
a function that will return a point and having little luck in returning
results.
Basically I have a
zip code DB complete with city, state and zip pre-populated.
What I would like to
do is create this function:
CREATE OR REPLACE
FUNCTION public.map_point(pg_catalog.varchar, pg_catalog.varchar,
pg_catalog.varchar) RETURNS point AS'SELECT map_loc from zip_code
where zip = \'$3\' and lower(state) = lower(\'$2\') and lower(city) =
lower(\'$1\')' LANGUAGE 'sql' VOLATILE;
And I have no
problems creating this function however, I can't get it to return any thing from
my zip_code table.
Am I doing something
wrong?
Here's a snippet of
the zip_code table:
Table
"public.zip_code"Column
|
Type | Modifiers
---++--- city
| character varying(100) | state | character
varying(2) | zip |
character varying(10) | area_code | character
varying(3) | map_loc |
point
|
city | state | zip | area_code
| map_loc
+---+---+---+--- portsmouth
| nh | 00210 | 603 |
(43.0718,70.7634) portsmouth | nh | 00211 |
603 | (43.0718,70.7634) portsmouth
| nh | 00212 | 603 |
(43.0718,70.7634) portsmouth | nh | 00213 |
603 | (43.0718,70.7634)
And nothing returned
but an empty row:
my_db=# select
public.map_point('portsmouth','nh','00211'); map_point
--- (1 row)
I'm running 7.3.x on
Slackware.
Any ideas why this
is happening?
Thanks,
Andy
Re: [SQL] Point and function help
Thanks Tom, worked like a charm. Appreciate your time on Christmas day! Best Regards and Merry Christmas to all. Andy -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Thursday, December 25, 2003 10:44 AM To: Andy Lewis Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Point and function help "Andy Lewis" <[EMAIL PROTECTED]> writes: > CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar, > pg_catalog.varchar, pg_catalog.varchar) > RETURNS point AS > 'SELECT map_loc from zip_code where zip = \'$3\' and lower(state) = > lower(\'$2\') and lower(city) = lower(\'$1\')' > LANGUAGE 'sql' VOLATILE; You don't want to quote the parameter references --- what you've got there is simple literal constants '$3' etc. Try CREATE OR REPLACE FUNCTION public.map_point(pg_catalog.varchar, pg_catalog.varchar, pg_catalog.varchar) RETURNS point AS 'SELECT map_loc from zip_code where zip = $3 and lower(state) = lower($2) and lower(city) = lower($1)' LANGUAGE 'sql' VOLATILE; Also, I can't see any reason why this function needs to be VOLATILE; STABLE should be enough, no? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[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
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] postgreSQL editors
I would have to agree with EMS-HiTech's product also. A feature rich, product, very responsive tech support/bug report. PGManager beats the competition hands down. Andy -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rich Hall Sent: Wednesday, December 31, 2003 1:09 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] postgreSQL editors >> can anyone recommend a good editor for postgreSQl wich wil enable me >> to create functions/stored procedures? >> I am currently running version 7.4.1 on OS X 10.3 > I use PG Manager from EMS Hitech. It is not free but inexpensive. You have everything PostgreSQL-wise at your fingertips. Ver 2.0 was just released and they are very responsive to bug reports. Rick ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] sort by on two columns
Title: Message Hi All, Is it possible to sort by two columns? Using the query below? SELECT table1.name, table2.name, FROM table1, table2 WHERE table1.id = table2.id ORDER BY I want to be able to sort the names select from two different tables and two different colums(same data type). Is this possible? Thanks, Andy
Re: [SQL] sort by on two columns
Hi Michael, Yes, I understand this but, I would like to have the results of both "table1.name, table2.name" sorted as one column. Is this possible? Thanks, Andy -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Friday, January 02, 2004 8:40 PM To: Andy Lewis Cc: [EMAIL PROTECTED] Subject: Re: [SQL] sort by on two columns Hi Andy, On Jan 2, 2004, at 7:15 PM, Andy Lewis wrote: > Is it possible to sort by two columns? Using the query below? > > SELECT table1.name, table2.name, FROM table1, > table2 WHERE table1.id = table2.id ORDER BY > > I want to be able to sort the names select from two different tables > and > two different colums(same data type). If you want to order by table1.name and table2.name, just enter them in a comma-separated-list after ORDER BY, e.g., SELECT table1.name, table2.name, FROM table1, table2 WHERE table1.id = table2.id ORDER BY table1.name, table2.name Check out the following link for the online docs: <http://www.postgresql.org/docs/current/static/queries-order.html> It doesn't explicitly give you an example of sorting on more than one column, but the syntax explanation at the top includes it. Does this help? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
