[postgis-users] identfying a nearest point

[Stephen Woodbridge]

Supunmali Ahangama wrote:
> 
> Daniel Kastl-2 wrote:
>> Hi,
>>> This is the SQL I tried with:
>>>
>>> select v.*, c.gid 
>>> from vertices_tmp v, customer_location c 
>>> where c.gid=1 and 
>>> st_expand(setsrid(((select the_geom from customer_location where
>>> gid=1)),4326), 10000000) && setsrid(v.the_geom,4326)
>>> order by distance(setsrid((select the_geom from customer_location where
>>> gid=1),4326),setsrid(v.the_geom,4326))
>>> asc limit 1; 
>>>
>>>   
>> Without having tested this "100000000" looks very suspicious.
>> This value is in the same projection as your geometry is set: 4326
>> 4326 is in degree, so you could try it with 0.1 for example.
>>
>> Daniel
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>>
> 
> 
> No, it does not work. It doesnt show anything when used 0.1 as radius.
> 
> and I tried this just considering distance (full table scan):
> 
> select v.*, c.gid 
> from vertices_tmp v, customer_location c 
> where c.gid=1
> order by distance(setsrid((select the_geom from customer_location where
> gid=1),4326),setsrid(v.the_geom,4326)) asc 
> limit 1;  
>  
> This SQL do not consider the radius, it just consider the distance and scan
> the table fully; but it still gives the same answer (which is incorrect). 
> Please provide me with some suggestions.

What is the response to the following queries:

select full_postgis_version();
select astext(the_geom) from customer_location where
 > gid=1;
select astext(the_geom) from vertices_tmp limit 1;

-Steve