3

I'm using QGis and would like to convert the progressive mileage of a highway into latitude/longitude coordinates, but I have no idea how to do it.

I have a Csv dataset where there are several "geo-referenced" information with the progressive mileage of the highway. The Dataset takes this aspect:

+--------------+---------------------+----------+
| Highway name | Progressive mileage | FeatureS | 
+--------------+---------------------+----------+ 
| A            |               400.1 |      ... | 
| A            |               430.2 |      ... |
| A            |               520.5 |      ... |
| B            |                22.8 |      ... |
| B            |                50.3 |      ... |
+--------------+---------------------+----------+

What I am trying to do with QGis (or welcome if there is an easier way) is to translate the progressive kilometers of the highway into latitude and longitude coordinates. This in order to subsequently represent each feature of the dataset on the map. About the progressive kilometers of the highway, I know that is known the latitude and longitude of the initial mileage (km 400).

My goal is to have the possibility to make geographical queries in which reporting the name of the highway and the progressive mileage, I get the latitude and longitude of the point

How could I fix this?

Improve this question
7
  • 1
    Do you have a vector layer of your highway lines? If so, then you can generate what you want with a PostGIS query, provided a correctly oriented linestring for each highway, a table with your progressive mileages (as shown above), and a last table containing the lat/lon of your origin points, the highway name, and km values at the origin.
    – FSimardGIS
    Nov 15, 2020 at 6:54
  • This a common case for Linear Referencing and Dynamic Segmentation. Do you know if you have a calibrated route?
    – Francisco Puga
    Nov 15, 2020 at 9:35
  • What I was able to do is: 1) Extract the highway network of interest from OSM 2) Through the function shortest route from point to point, I divided the highway into vectors 3) What I got is one vector for each highway. -- Considering that for each vector, therefore for each highway, I know the correspondence: Progressive & Initial Coordinates and Progressive & Final Coordinates is enough to query the highway vectors with queries in which I step as parameters: HighwayName & MileageProgression to get the Coordinates of the point? @FSimardGIS
    – pofferbacco
    Nov 15, 2020 at 17:40
  • What I have is a vector for each highway, generated with the shortest route function from point to point. It's enough? @FranciscoPuga
    – pofferbacco
    Nov 15, 2020 at 17:45
  • So you have a line for each highway spanning from the initial mileage to the final mileage?
    – FSimardGIS
    Nov 15, 2020 at 18:16

1 Answer 1

Reset to default
1

Here is a possible solution in PostGIS, assuming the following data structure:

  • A table HighwayEvents containing fields Highway_name, Progressive_km, Features (= your imported csv dataset)
  • A table HighwayOrigins containing fields Highway_name, Start_km, End_km
  • A table Highways containing your prepared highway lines with fields Highway_name and geom for the geometry.

Before running the query, make sure you have only 1 linestring per highway, chopped at the start and end km, and that the lines are correctly oriented, meaning the lines' direction are the same as your increasing mileages. Also check that your events all fall between your start and end km, otherwise you'll have to filter those out.

Here is the PostGIS query that will generate your event point layer:

SELECT h."Highway_name", 
       he."Features", 
       he."Progressive_km",
       ST_LineInterpolatePoint(h.geom, (he."Progressive_km" - ho."Start_km") / (ho."End_km" - ho."Start_km")) as pointgeom
FROM your_schema."Highways" as h
INNER JOIN your_schema."HighwayEvents" as he ON h."Highway_name" = he."Highway_name"
INNER JOIN your_schema."HighwayOrigins" ho ON h."Highway_name" = ho."Highway_name"

Simply change the schema, table and field names to match yours if needed.

Adding this as a QueryLayer from the DB Manager in QGIS, with a test dataset, here is the result:

enter image description here

Improve this answer

Your Answer

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

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