[postgis-users] Retrieve a feature having point coordinates

[Stephen Woodbridge]

On 12/27/2011 4:37 AM, Alessandro Candini wrote:
> Hi list.
> I'm new to Postgis world and I have a probable dumb question:
> I have a shape file inserted into a postgis database through shp2pgsql
> utility and I would like to retrieve a feature ID giving as input a pair
> of lat/lon coordinates.
> Something like "SELECT gid from mytable WHERE point_is_into(45.2, 23.3);"
> How can I achieve this?
>
> Thanks in advance.
>

select gid from mytable where 
st_dwithin(setsrid(makepoint(<long>,<lat>),4326), the_geom, 
<max_radius_degrees>) order by 
st_distance(setsrid(makepoint(<long>,<lat>),4326), the_geom) ASC limit 1;

<max_radius_degrees>  defines the bbox radius from <lon>,<lat>.

This assumes you have set your srid to 4326, ie: WGS84 if you have not 
set it, then don't use the setsrid function.

-Steve W