[postgis-users] Obtaining an address closest based on x/y coordinate pair

[Carl Anderson]

On 03/22/2004 01:19:04 PM, Tyler Mitchell wrote:
> > I have been trying desperately but unsuccessfully to get the  
> address
> 
> closest
> 
> Unless I'm missing something, this should be straightforward with the
> use
> of a couple more functions.  Don't be scared of them, they'll help  
> you
> a
> lot :)  Specifically, you want to use the Distance() function in
> PostGIS.
> It takes in two geometry fields and will compute the distance for  
> you.
> 
> If there are way you can have the coordinates that the user enters  
> get
> put
> into a postgis table in a geometry column?  If you can get that setup
> it
> will make discussion even easier.  Let's pretend that you get that
> working
> and that your point is in a table called UserPoint and it has a
> the_geom
> field.  Here is how you would do your query:
> 
> SELECT
> distance(UserPoint.the_geom, address.the_geom),
> str_num ||" "|| name AS address
> FROM
> UserPoint,
> address
> WHERE
> Distance(UserPoint.the_geom, address.the_geom) < 1000;
>

how about the closest address first (limit to those with 1000 )

SELECT
  distance(UserPoint.the_geom, address.the_geom),
  (str_num ||' '|| name ) AS address
FROM
  (SELECT 'POINT(2254654.00 1236233.00)'::geometry as the_geom) as  
UserPoint,
  address
WHERE
-- bounding box search uses GIST index
  address.the_geom && expand(UserPoint.the_geom,1000.00)
-- order results by closeness
ORDER BY 1
-- only return the first result (all potential results are evaluated)
LIMIT 1;


I you want the same from centerline / range data
you will need to scale the min and max adresses according to the  
percent along the line.  (I'll send that if you wish)


C.