From: | Jaime Casanova <jaime(at)2ndquadrant(dot)com> |
---|---|
To: | Foros Postgre Ecuador <ecpug(at)postgresql(dot)org> |
Subject: | Re: DISTINCT ON con columna hstore |
Date: | 2013-05-17 23:38:57 |
Message-ID: | CAJKUy5hHLH8-eR4Q8xLe5-p68MPoghr=geYrd=TaKStGvL+c2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | ecpug |
> ---------- Forwarded message ----------
> From: Tonny <tonny(dot)lists(at)gmail(dot)com>
> Date: 2013/1/24
> Subject: Re: [ecpug] DISTINCT ON con columna hstore
> To: Jaime Casanova <jaime(at)2ndquadrant(dot)com>
>
> Lo solucioné así no sé como mejorar mas esa consulta :) o ponerla mas clara.
>
> SELECT DISTINCT ON (nombre) nombre, distancia FROM
> (
> SELECT tags->'name' AS nombre, st_distance(linestring,
> ST_GeomFromText('POINT(-78.10563426666833 0.3187742717967793)',4326))
> * 111177.473352 as distancia
> FROM osm.ways
> WHERE exist(tags, 'name') AND exist(tags, 'highway')
> ORDER BY distancia
> ) AS t1
> WHERE distancia < 200
>
>
Y ya que estamos en eso, puedes usar la función st_dwithin() para solo
obtener los puntos que se encuentran dentro de 200m... y esa función
puede aprovechar los índices.
http://postgis.org/docs/ST_DWithin.html
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157
From | Date | Subject | |
---|---|---|---|
Next Message | Tonny | 2013-05-20 04:01:29 | Re: Fwd: DISTINCT ON con columna hstore |
Previous Message | Jaime Casanova | 2013-05-17 23:36:39 | Fwd: DISTINCT ON con columna hstore |