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

Carl Anderson carl.anderson at vadose.org
Mon Mar 22 17:28:25 PST 2004


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.




More information about the postgis-users mailing list