Thread: Need help with attributes...

I created a latitude/longitude function (it's Richard Lynch's, actually) that returns the distance between to points.  What I don't know how to do is use this value as a filter in the select statement:

PostgreSQL Error: 1 (ERROR: Attribute 'distance' not found )
select distinct zip_code, gpsdistance(34.2865, 118.435, latitude, longitude) as distance from zipcodes where distance
<= 10.0

The value for distance is actually available, however:
 

select distinct zip_code, gpsdistance(34.2865, 118.435, latitude, longitude) as distance from zipcodes where zip_code
like '913%'

91301 16.3049416821461,
91302 12.858968990111,
91303 8.71667347958831,
91304 8.67013652211651,
91305 8.45399567001425,
91306 .....

Any assistance on this question of syntax would be gratefully accepted

-- 
---------------------------------------------------------------------
Scott Holmes                       http://www.pacificnet.net/~sholmes
                                   sholmes@pacificnet.net

  Independent Programmer/Analyst                  Passport 4GL        
  HTML Composer                                   Informix 4GL, SQL
---------------------------------------------------------------------
      There are more things in heaven and earth, Horatio,
             than are dreamt of in your philosophy 
---------------------------------------------------------------------

 

Scott Holmes wrote:
>
> I created a latitude/longitude function (it's Richard Lynch's, actually)
> that returns the distance between to points.  What I don't know how to do
> is use this value as a filter in the select statement:
>
> PostgreSQL Error: 1 (ERROR: Attribute 'distance' not found )
> select distinct zip_code, gpsdistance(34.2865, 118.435, latitude,
> longitude) as distance from zipcodes where distance
> <= 10.0
>
> The value for distance is actually available, however:
>
>
> select distinct zip_code, gpsdistance(34.2865, 118.435, latitude,
> longitude) as distance from zipcodes where zip_code
> like '913%'
>
> 91301 16.3049416821461,
> 91302 12.858968990111,
> 91303 8.71667347958831,
> 91304 8.67013652211651,
> 91305 8.45399567001425,
> 91306 .....
>
> Any assistance on this question of syntax would be gratefully accepted

Double-quote distance to make it an alias rather than a column name.  Then,
I'm not sure you can use the double-quoted version in the where clause or
not...

Regards,
Ed Loehr

You cannot use a "name" in a WHERE clause and aliases are specifically for
aliasing table names only.  (An alias reference would look like "WHERE
alias.column_name > 10".) The documentation for "AS name" usage specifically
says a "name" cannot be used in a WHERE clause.  I believe you will have to
duplicate the gpsdistance() invocation in the WHERE clause as well.

    SELECT DISTINCT
        zip_code, gpsdistance(34.2865, 118.435, latitude, longitude)
AS distance
    FROM
        zipcodes
    WHERE
        gpsdistance(34.2865, 118.435, latitude, longitude) <= 10.0

Phil Culberson
DAT Services

-----Original Message-----
From: Ed Loehr [mailto:eloehr@austin.rr.com]
Sent: Tuesday, May 02, 2000 1:21 PM
To: Scott Holmes
Cc: pgsql-general
Subject: Re: [GENERAL] Need help with attributes...


Scott Holmes wrote:
>
> I created a latitude/longitude function (it's Richard Lynch's, actually)
> that returns the distance between to points.  What I don't know how to do
> is use this value as a filter in the select statement:
>
> PostgreSQL Error: 1 (ERROR: Attribute 'distance' not found )
> select distinct zip_code, gpsdistance(34.2865, 118.435, latitude,
> longitude) as distance from zipcodes where distance
> <= 10.0
>
> The value for distance is actually available, however:
>
>
> select distinct zip_code, gpsdistance(34.2865, 118.435, latitude,
> longitude) as distance from zipcodes where zip_code
> like '913%'
>
> 91301 16.3049416821461,
> 91302 12.858968990111,
> 91303 8.71667347958831,
> 91304 8.67013652211651,
> 91305 8.45399567001425,
> 91306 .....
>
> Any assistance on this question of syntax would be gratefully accepted

Double-quote distance to make it an alias rather than a column name.  Then,
I'm not sure you can use the double-quoted version in the where clause or
not...

Regards,
Ed Loehr