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')
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
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:
- Best/most efficient way to get OSM road points into PostGIS (note: it's for a particular region, not the world)
- 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.