2

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

2
  • 2
    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 knn on GIS.SE
    – geozelot
    Commented Oct 2, 2023 at 18:42
  • 1) see the operator doc, 2) coordinates should be in the longitude;latitude format, 3) beware that <-> uses the Cartesian distance, which is meaningless with geometries in 4236
    – JGH
    Commented Oct 2, 2023 at 19:44

1 Answer 1

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
2
  • I used <-> in the ORDER BY statement is that ok? Commented Oct 4, 2023 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 Commented Oct 4, 2023 at 20:10

Your Answer

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

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