1

I need a function to return the nearest OSM road point for any latitude/longitude. i.e. users input a latitude/longitude and postgis finds and uses the point on a road that is the nearest to it. Particularly, this needs to be fast.

Here's an extract of my current - slow - solution:

select "geom" 
from (select (ST_DumpPoints(geom_vertex)).geom 
  from "roads_2po_v") as "vertex" 
    order by ST_Distance(geom, 
      ST_SetSRID(ST_MakePoint('103.84743100000003','1.2987212'), 4326)) 
 limit '1')

Unfortunately this order by ST_Distance... limit 1 slows down the entire query about 500x. What would be an improved way to snap to nearest road point in PostGIS using OSM data?

roads_2po_y is a table constructed using osm2po like so:

java -jar osm2po-core-4.8.8-signed.jar prefix=roads cmd=c roads/data.pbf

Structure:

CREATE TABLE roads_2po_v (
  id integer NOT NULL,
  clazz integer,
  osm_id bigint,
  osm_name character varying,
  ref_count integer,
  restrictions character varying
);
SELECT AddGeometryColumn('public', 'roads_2po_v', 'geom_vertex', 4326, 'POINT', 2);

So I'd like to know:

  1. Best/most efficient way to get OSM road points into PostGIS (note: it's for a particular region, not the world)
  2. Efficient query for finding nearest road point when given a latitude and longitude

Any other questions/answers I've found on the site are wrapped in custom requirements. I'm seeking a generic function that returns nearest OSM road point for any given latitude/longitude.

  • 1
    use ST_ClosestPoint postgis.net/docs/ST_ClosestPoint.html – Mapperz Dec 31 '14 at 15:51
  • g2 of ST_ClosestPoint would be my input lat/long as a geom point, but not sure how I would construct g1? – rgareth4 Dec 31 '14 at 17:36
  • 1
    There is also ST_Line_Locate_Point postgis.refractions.net/documentation/manual-1.5/… – Mapperz Dec 31 '14 at 18:10
  • For g1 perhaps you could use ST_Collect to gather all line segments where the 'highway' attribute is not null (or is within a set of values that you're interested in) into a single geometry. postgis.net/docs/ST_Collect.html – Grant Humphries Dec 31 '14 at 18:41
  • This query returns the right answer: select ST_X(ST_ClosestPoint(r.geom_vertex, point.geom)), ST_Y(ST_ClosestPoint(r.geom_vertex, point.geom)), ST_Distance_Sphere(ST_ClosestPoint(ST_Transform(r.geom_vertex, 4326), point.geom), point.geom) from roads_2po_v r, (select ST_SetSRID(ST_MakePoint('103.8319629999999','1.304011'), 4326) as geom) point order by 3 asc limit 1 but not sure if it's efficient or not. – rgareth4 Dec 31 '14 at 18:54

Your Answer

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

Browse other questions tagged or ask your own question.