2

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.

Improve this question
8
  • 1
    use ST_ClosestPoint postgis.net/docs/ST_ClosestPoint.html
    – Mapperz ♦
    Dec 31, 2014 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, 2014 at 17:36
  • 1
    There is also ST_Line_Locate_Point postgis.refractions.net/documentation/manual-1.5/…
    – Mapperz ♦
    Dec 31, 2014 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, 2014 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, 2014 at 18:54

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.