Query to find nearest road highway tag to a (latitude,longitude) point

Question

I imported an OSM file for the state of Delaware into the PostGIS Database via connecting to it through QGIS. I would like to build a query to retrieve the nearest roads to the (latitude,longitude) points I have.

When I imported to QGIS, it created 4 layers: one for linestring, one for points, one for multilinestrings, a and once for multi polygons. I believe the only ones I need would be lines. So far I have imported my table of 1000 ( latitude, longitude) points. I need to write a query that for each point, will return the highway tag of the nearest road to that point. I've written a query that casts each latitude longitude into a point geometry:

select ST_AsText(ST_MakePoint(end_latitude::float, end_longitude::float)
from gis.delaware_trip_slices

Each row output here would be for example Point(37.2,75.32) etc. Now, I just have to find a way to return the highway tag for the nearest road to each of these points. Do you have any pointers how I would go about doing this.

I saw a query like this online, but I don't know what this means:

Select *
From towns 
order by the_geom <->
SETSRID(ST_Make_Point(lat,long),4236) 
Limit 5

My guess is somehow I could make the above query a temporary table. Then I would for each value in the temporary table search for the nearest road. Does anyone have any pointers

You'll find all relevant information about the concept needed and its implementation in e.g. this answer and its links, and/or via the tag [tag:knn] on GIS.SE — geozelot, Oct 02 '23 at 18:42

LauriK · Answer 1 · 2023-10-04T07:22:49.283

1

<-> operator tells the distance between two geometries. Sorting tables using this operator is very fast if there is a spatial index created on the tables.

The following query selects one point at a time and then finds the nearest road for it. You didn't specify the schema of your tables so you have to fix all the column names etc.

SELECT
  points.name,
  roads.name,
  roads.dist
FROM
  gis.delaware_trip_slices points
  CROSS JOIN LATERAL (
    SELECT osm_lines.name, osm_lines.geom <-> ST_Transform(ST_SetSRID(ST_MakePoint(end_latitude::float, end_longitude::float), 4326)), 3857) AS dist
    FROM osm_lines
    WHERE osm_lines.tag = 'highway'
    ORDER BY dist
    LIMIT 1
  ) roads

edited Oct 04 '23 at 07:22

answered Oct 04 '23 at 06:59

LauriK

1,638
1
14
16

I used <-> in the ORDER BY statement is that ok? – Vivek Reddy Oct 04 '23 at 20:09
The last part of the query is like: CROSS JOIN LATERAL ( SELECT hlines.id, osm_lines.line_geometry, osm_lines.highway, osm_lines.geom_text FROM osm_lines ORDER BY osm_lines.line_geometry <-> hpoints.point_geometry LIMIT 1 – Vivek Reddy Oct 04 '23 at 20:10

Query to find nearest road highway tag to a (latitude,longitude) point

1 Answers1