Wuu Posted November 17, 2016 Report Share Posted November 17, 2016 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ā. Quote Link to post Share on other sites
waplet Posted November 17, 2016 Report Share Posted November 17, 2016 Ieliec subkvērijā, vai arī iekš where raksti to pašu ko iekš select. Quote Link to post Share on other sites
jurchiks Posted November 17, 2016 Report Share Posted November 17, 2016 (edited) http://stackoverflow.com/a/10099006/540394 Edited November 17, 2016 by jurchiks Quote Link to post Share on other sites
PheliX Posted November 17, 2016 Report Share Posted November 17, 2016 HAVING closeness <= 50000 Quote Link to post Share on other sites
waplet Posted November 18, 2016 Report Share Posted November 18, 2016 Having vai tad nav uz agregētajiem datiem? Quote Link to post Share on other sites
jurchiks Posted November 18, 2016 Report Share Posted November 18, 2016 Es sākotnēji arī biju uzrakstījis "having", bet tad papētīju un sanāk, ka tas neder. Quote Link to post Share on other sites
Wuu Posted November 22, 2016 Author Report Share Posted November 22, 2016 Njā, subquery it is... Domāju ka ir kāds "skaistāks" variants. Paldies Quote Link to post Share on other sites
Wuu Posted December 12, 2016 Author Report Share Posted December 12, 2016 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 Quote Link to post Share on other sites
xPtv45z Posted December 12, 2016 Report Share Posted December 12, 2016 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 Quote Link to post Share on other sites
jurchiks Posted December 12, 2016 Report Share Posted December 12, 2016 Tieši tā, dubultot sarežģītus condition nekad nav laba ideja. Quote Link to post Share on other sites
Wuu Posted December 12, 2016 Author Report Share Posted December 12, 2016 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... Quote Link to post Share on other sites
daGrevis Posted December 12, 2016 Report Share Posted December 12, 2016 Nevar nomigrēt uz pareizo datatipu lai būtu sakarīgāk? Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.