5

I want to reverse geocode with PostGIS use OSM database, I use function:

<!-- language: lang-sql -->
CREATE OR REPLACE FUNCTION khoangcach(lat double precision, lon double precision) 
RETURNS text AS $BODY$ 
declare point geometry; rec record; geocode text; 
begin point := geomfromtext('POINT('||lat||' '||lon||')', 900913); 
select name, ST_Distance(way, point) as dist into rec 
from planet_osm_point 
order by dist asc; 
geocode := rec.name; 
return geocode; 
end; 
$BODY$ 
LANGUAGE plpgsql; 

I think the function is correct but I just have a result for all POINT input.

7

As I understand this function will give you all points from planet_osm_point ordered by distance from your point.

  1. If you need one answer you have to use 'limit 1'.
  2. Not every nearest point in table will be good answer so you have to use where (addr:housenumer is not null or similar condition)
  3. To increase performance you could search for your answer only in specified distance from your point - point placed 10km from given coordinates will not be a good answer.

so:

<!-- language: lang-sql -->
CREATE OR REPLACE FUNCTION khoangcach(lat double precision, lon double precision) 
RETURNS text AS $BODY$ 
declare point geometry; rec record; geocode text; 
begin 
point := geomfromtext('POINT('||lat||' '||lon||')', 900913);  

with points as 
  (
  select * 
  from planet_osm_point
  where st_dwithin(way,point,<<distance>>)
    and name is not null
  )
select name, ST_Distance(way, point) as dist into rec 
from points 
order by dist asc
limit 1; 
geocode := rec.name; 
return geocode; 
end; 
$BODY$ 
LANGUAGE plpgsql; 
2
  • Thank for your hellp, i can resolved my problem!
    – Duong
    May 21 '14 at 3:40
  • 2
    You're welcome... If this helped you to resolve problem please mark it as accepted
    – Jendrusk
    May 21 '14 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.