postgresql alias iekš where

[Wuu]

select u.id,
st_distance_sphere(
                ST_GeomFromText(concat('POINT(',u.longitude,' ',u.latitude,')'), 4326),
                ST_GeomFromText(concat('POINT(22.286756 56.624693)'), 4326)
) as closeness
from users u
where u.longitude is not null and u.latitude is not null
order by closeness

Kā man iekš kverija ielikti pārbaudi uz distanci? closeness <= 50000

 

Where nevar ielikt imho "ERROR:  column "closeness" does not exist" alias nestrādā. 

[waplet]

Ieliec subkvērijā, vai arī iekš where raksti to pašu ko iekš select.

[jurchiks]

http://stackoverflow.com/a/10099006/540394

Edited November 17, 2016 by jurchiks

[PheliX]

HAVING closeness <= 50000

[waplet]

Having vai tad nav uz agregētajiem datiem?

[jurchiks]

Es sākotnēji arī biju uzrakstījis "having", bet tad papētīju un sanāk, ka tas neder.

[Wuu]

Njā, subquery it is... Domāju ka ir kāds "skaistāks" variants. 

 

Paldies

[Wuu]

Atgriežoties tēmas. Tiešām nav labāka varianta?

select u.id,
st_distance_sphere(
    ST_SetSrid(ST_MakePoint(u.longitude::numeric, u.latitude::numeric), 4326),
    ST_SetSrid(ST_MakePoint(60.02402367490043, 30.437835716692238), 4326)) 
as closeness
from users u
where u.is_online = true and and u.longitude is not null and u.latitude is not null
and 10000 < (select st_distance_sphere(
    ST_SetSrid(ST_MakePoint(u.longitude::numeric, u.latitude::numeric), 4326),
	ST_SetSrid(ST_MakePoint(60.02402367490043, 30.437835716692238), 4326)))
order by closeness
limit 10

[xPtv45z]

Man liekas, ka iesakot subquery, domāja ko šādu

select sub.*
from (select u.id,
        st_distance_sphere(
          ST_SetSrid(ST_MakePoint(u.longitude::numeric, u.latitude::numeric), 4326),
          ST_SetSrid(ST_MakePoint(60.02402367490043, 30.437835716692238), 4326)) 
        as closeness
        from users u
        where u.is_online = true and and u.longitude is not null and u.latitude is not null
) as sub
where 10000 < sub.closeness
order by sub.closeness
limit 10

[jurchiks]

Tieši tā, dubultot sarežģītus condition nekad nav laba ideja.

[Wuu]

Paldies!

 

Ir te tāda pa-jocīga datubāzes struktūra. Tos pašus GEO punktus var glabāt normālā veidā, nevis teksta formātā... Doh...

[daGrevis]

Nevar nomigrēt uz pareizo datatipu lai būtu sakarīgāk?