[postgis-users] Closest point problem AND How to find the nearest road (geom) for the point which is not exactly located on the road.

[Ben Madin]

G'day all

inspired by Stephen's suggestion I set off with my laptop and the  
postgres manual, and spent a trip to South Africa learning how to use  
PL/pgsql

I don't think my attempt is rocket science, but if this helps anyone,  
I hope that is of some use - if anyone can see anything that would  
make it faster, I'd be stoked!

cheers

Ben




-- This function requires a point geometry and a nominated return value,
-- and will return the gid, source or target value for the closest  
line segment
-- work on the un-ambiguity principle, you can choose to spell out the 
-- return value you want, or just the first letter.
-- Some of the conditions are :
--   both geometries in the same srid
--   geometry in the th_geom column
--   geometry table already contains a gid, source and target field
-- obviously dynamic nomination of the table would be nice, but I was  
on a plane,
-- and vaguely under the impression that it stops caching and slows it  
down,
-- and I was looking through 150 000 rows
-- this is my first ever plpgsql function, so don't expect too much.

CREATE OR REPLACE FUNCTION find_nearest_road(
     point geometry, sf varchar(6), OUT value int
) AS
$BODY$
DECLARE
     max_search_radius real := 5.0; -- this is assuming working in  
degrees I guess!
     search_radius real := 0.01;
     rec mrwa_net%ROWTYPE; -- this has to match your lookup table
BEGIN
LOOP
     SELECT * INTO rec
     FROM mrwa_net m -- and you might want to change this
     WHERE st_expand(point, search_radius) && m.the_geom
     ORDER BY st_distance(point, m.the_geom)
     LIMIT 1;
     IF FOUND THEN -- you could really simplify this, it might speed  
it up, but I kind of like it.
         IF substring(sf from 1 for 1) iLIKE 's' --start or Source
             THEN
             value := rec.source;
         ELSIF substring(sf from 1 for 1) iLIKE 't' --target
            OR substring(sf from 1 for 1) iLIKE 'f' --finish
            THEN
             value := rec.target;
         ELSIF substring(sf from 1 for 1) iLIKE 'g' --gid
            OR substring(sf from 1 for 1) iLIKE 'i' --id
             THEN
             value := rec.gid;
         END IF;
         EXIT;
     END IF;
     search_radius := search_radius * 2.0;
     EXIT WHEN search_radius > max_search_radius;
END LOOP;
END;
$BODY$ LANGUAGE plpgsql;







On 04/08/2009, at 4:35 PM, Stephen Woodbridge wrote:

> Thurber, Fred wrote:
>> I have a database full of point data.  When our user stops at a  
>> certain lat/lon, how do I find the closest point in our database?
>> We have at least 10,000 points, maybe a lot more so the search has  
>> to be efficient, but I have not found a way.  It seems a little  
>> surprising considering how common this query must be.
>>  The obvious way is to scan the entire table and find the closest  
>> point.  Let’s say our user stops at (40,70), I could do this:
>> SELECT * FROM foo
>> ORDER BY distance(point_geom, GeomFromText('POINT(70.0 40.1)', 4326))
>> LIMIT 1
>> When I run this through EXPLAIN ANALYZE, it shows a full-table  
>> scan.  Is there a way I can use a GIST or is there some other way  
>> to speed it up?
>
> Thilani also wrote:
>> Hi,
>> I’m developing a shortest path navigation application on GIS. I  
>> came across
>> a problem when we need to find a shortest path from the location  
>> which is
>> not exactly located on the road. In that case I need to find what  
>> is the
>> nearest road (geom.) to that particular point 1st. Is there any  
>> available
>> post GIS function to do that? If so can you kindly explain that  
>> using an
>> example.
>> Thanks and Best Regards,
>> Thilani
>
> Hi Fred and Thilani,
>
> This question is asked often on the list You can check the archives  
> for more comments. Search for "Nearest Neighbor"
>
> Basically, you can not currently do this efficiently with a single  
> query. Most people solve this problem if a plpgsql function. The key  
> to using an index requires a radius. Typical you should start with a  
> small radius and if you fail to get a result, double the radius and  
> try again. So something like this:
>
> mac_search_radius := 1.0;
> raduis := .0013;
> loop
>  select into rec * from mytable
>   where expand(setsrid(makepoint(70.0, 40.1),4326), radius) &&  
> the_geom
>   order by distance(setsrid(makepoint(70.0, 40.1),4326),the_geom)
>   limit 1;
>  if found then
>    exit;
>  end if;
>  radius := radius * 2.0;
>  if radius > max_search_radius then
>    return null;
>  end if;
> end loop;
> -- do something with rec
> return some_result;
>
> -Steve W
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 

Ben Madin
REMOTE INFORMATION

t : +61 8 9192 5455
f : +61 8 9192 5535
m : 0448 887 220
Broome   WA   6725

ben at remoteinformation.com.au



							Out here, it pays to know...