[postgis-users] closest point

[Jaime Casanova]

Hi,

I'm trying to get the closest point to a given one, complete SQL is as follows:
"""
select DISTINCT fl.stime, ll.latitude, ll.longitude,  
                (select (case when data_origin = 'SNT' then lic_name else lic_tx_name end) as lic_tx_name
                   from transmitter_mv
                  where tx_freq::decimal = fl.frequency::decimal

--                    and st_dwithin(punto, st_makepoint(ll.longitude, ll.latitude), 50, false)
--                  order by st_distance(punto, st_makepoint(ll.longitude, ll.latitude))

                    and st_distance_sphere(transmitter_mv.punto, ll.punto) < 50
                  order by st_distance_sphere(transmitter_mv.punto, ll.punto)
                  limit 1
                ) as estacion,
                fl.level, fl.frequency / 1000000 as frecuencia
  from rowfreqlevel fl
       join rowlatlong ll ON ll.row_id = fl.row_id
 WHERE stime >= '2011-05-24 23:39' AND stime <= '2011-05-24 23:41'
"""

as you see, for every set of long/lat i need to find the closest
transmitter. i tried it creating a point in the query and also adding a
point column in the table (in both cases i created gist indexes, the
first time over the st_makepoint() function then over the geometry
column) nevertheless i always get a seq scan. any idea how to improve
this query? just in case here is the plan: http://explain.depesz.com/s/UY3

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL 
Soporte 24x7, desarrollo, capacitación y servicios