Thread: column label
Ok I have a query in a program that looks something like this selectreference_number as foo,'(10,10)'::point <-> point(x,y) as distance from imagemapwhere index_number = '2322' and block_number = '140'order by distancelimit 1; I wanted to add a clause in where 'and distance < 30' but according to the postgres manual it says I cannot do that for WHERE or HAVING clauses, instead use the real name. However dropping the 'as distance' from my query gives me a column name of '?column?' which happens to not be worth a dang thing. How can it be done? (I suddenly have a feeling its going to require a subselect) Ryan
Uz.ytkownik Ryan napisa?: > Ok I have a query in a program that looks something like this > > select > reference_number as foo, > '(10,10)'::point <-> point(x,y) as distance > from imagemap > where > index_number = '2322' and > block_number = '140' > order by distance > limit 1; > > I wanted to add a clause in where 'and distance < 30' > but according to the postgres manual it says I cannot do that for WHERE or > HAVING clauses, instead use the real name. > > However dropping the 'as distance' from my query gives me a column name of > '?column?' which happens to not be worth a dang thing. > > How can it be done? > > (I suddenly have a feeling its going to require a subselect) Your feeling is 50% accurate. If you want to use subselect, throw "<10" into this subselect, because you will have performance loss. You can also use whole function in where clause and order by: select reference_number as foo, '(10,10)'::point <-> point(x,y)as distance from imagemap where index_number = '2322' and block_number = '140' where '(10,10)'::point <->point(x,y)<10 order by '(10,10)'::point <-> point(x,y) limit 1; BTW Did you try to use polygons in your image maps, or you have rather point oriented web page than area oriented? Regards, Tomasz Myrta
> Uz.ytkownik Ryan napisa?: >> Ok I have a query in a program that looks something like this >> >> select >> reference_number as foo, >> '(10,10)'::point <-> point(x,y) as distance >> from imagemap >> where >> index_number = '2322' and >> block_number = '140' >> order by distance >> limit 1; >> >> I wanted to add a clause in where 'and distance < 30' >> but according to the postgres manual it says I cannot do that for >> WHERE or HAVING clauses, instead use the real name. >> >> However dropping the 'as distance' from my query gives me a column >> name of '?column?' which happens to not be worth a dang thing. >> >> How can it be done? >> >> (I suddenly have a feeling its going to require a subselect) > Your feeling is 50% accurate. If you want to use subselect, throw "<10" > into this subselect, because you will have performance loss. > > You can also use whole function in where clause and order by: > select > reference_number as foo, > '(10,10)'::point <-> point(x,y) as distance > from imagemap > where > index_number = '2322' and > block_number = '140' > where '(10,10)'::point <-> point(x,y)<10 > order by '(10,10)'::point <-> point(x,y) > limit 1; > > BTW Did you try to use polygons in your image maps, or you have rather > point oriented web page than area oriented? > > Regards, > Tomasz Myrta Thats kind of a pain... I'd rather perform the calculation once and then do my order and where on the one value. Is there any reason why postgres does not recognize labels under WHERE? The reason I use points is because my customized web-based image mapping/ocr program can deal with points very aptly. Also with a distance based calc overlap is not really an issue as with polygons. The reason I am trying to limit the distance is simply to cut out clicking on far away parts of the image. Heck, I could check distance on the program side of things but I try and keep as much in SQL as possible. Ryan
> >> Ok I have a query in a program that looks something like this > >> > >> select > >> reference_number as foo, > >> '(10,10)'::point <-> point(x,y) as distance > >> from imagemap > >> where > >> index_number =3D '2322' and > >> block_number =3D '140' > >> order by distance > >> limit 1; > >> > >> I wanted to add a clause in where 'and distance < 30' > >> but according to the postgres manual it says I cannot do that for > >> WHERE or HAVING clauses, instead use the real name. > >> > >> However dropping the 'as distance' from my query gives me a column > >> name of '?column?' which happens to not be worth a dang thing. > >> > >> How can it be done? > >> > >> (I suddenly have a feeling its going to require a subselect) > > Your feeling is 50% accurate. If you want to use subselect, throw "<10" > > into this subselect, because you will have performance loss. > > > > You can also use whole function in where clause and order by: > > select > > reference_number as foo, > > '(10,10)'::point <-> point(x,y) as distance > > from imagemap > > where > > index_number =3D '2322' and > > block_number =3D '140' > > where '(10,10)'::point <-> point(x,y)<10 > > order by '(10,10)'::point <-> point(x,y) > > limit 1; > > > Thats kind of a pain... I'd rather perform the calculation once and then > do my order and where on the one value. Is there any reason why postgres > does not recognize labels under WHERE? > So how about select foo, distance from (select reference_number as foo, '(10,10)'::point <-> point(x,y) as distancefrom imagemap where index_number = '2322' and block_number = '140' ) as bar where distance < 30 order by distance limit 1; The reason why postgres does not recognize labels under WHERE is the WHERE clause is evaluated before column aliasing takes place. But there is this valuable feature of using a sub-SELECT as from_item, so this should not be a problem at all. Alternatively you could use selectreference_number,'(10,10)'::point <-> point(x,y) from imagemap alias (foo, distance) where index_number = '2322' and block_number = '140' and distance < 30 order by distancelimit 1; Does this help? Regards, Christoph