PostGIS: Find closest geometry to given point

The logic can be wrapped into a function, but I'd recommend using queries to test out the logic. The difficulties you will encounter are with linear distances (miles) combined with angular coordinates (degrees of latitude and longitude). Also the geometry type specified above does not have an SRID or geometry type. I'm guessing it should be geometry(Point,4326).

There are a few strategies you can try, such as using the geography type, which automagically uses metres for functions like ST_Distance, or ST_DWithin. The example below just uses ST_Distance_Sphere with a conversion from miles to metres to get you going. Or, if you need performance, you can try the <-> function for an indexed nearest neighbour search.

Here is something you can try:

CREATE OR REPLACE FUNCTION WhatAmINear(lat float8, lon float8,                                       radius_mi float8, num int DEFAULT 10)    RETURNS SETOF "cities-usa" AS$body$SELECT *FROM "cities-usa"WHERE ST_Distance_Sphere(geom, ST_MakePoint(lon, lat)) <= radius_mi * 1609.34LIMIT num;$body$LANGUAGE sql VOLATILE;

And then:

SELECT WhatAmINear(44.9, -93.1, 100);