Calculate distance on a polyline of a road between 2 lat/lons

Question

This is not distance as the crow flies.

I'm looking for an API like this:

distanceMiles = calculateMilesBetweenPointsAlongRoad(LatLon1, LatLon2, RoadPolyline)

I have a road represented as a polyline.

As a vehicle moves on this road, I capture lat/lons. I want to calculate the distance the vehicle traveled between each reported LatLon.

Because of the rate of data ingestion, I'd like to do this without spatial queries, but I will be OK if the only way is to store the road polyline in BigQuery or PostGIS and query against it continually.

If your using Google's API, new google.maps.Polyline(). developers.google.com/maps/documentation/javascript/examples/… — ABC
My solution is in Google Cloud Platform, server-side only. Looked at Google roads, directions, APIs, etc. Can't seem to find what I need — Jason
@Jason - Important on SO - you can mark accepted answer by using the tick on the left of the posted answer, below the voting. See meta.stackexchange.com/questions/5234/… for why it is important! Also important to vote on answer. Vote up answers that are helpful. ... You can check about what to do when someone answers your question - stackoverflow.com/help/someone-answers. Following these simple rules you increase your own reputation score and at the same time you keep us motivated to answer your questions :o) please consider! — Mikhail Berlyant
@Mikhail: My question was about roads and the answer brought in hurricanes and area-based GIS (polygons). — Jason
@Jason - does it really matter how geopoints are labeled? can you reuse logic and code assuming that polyline's lat/lon belong to car's path instead of hurricane? of course it assumes that set of reported LatLon describes the road as close as possible. anyway - i thought better to try to help than just ignore the question. Btw, I don't see see area-based gis here, so you might misread something - it is purely polyline / path based — Mikhail Berlyant

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2019-03-30T06:06:38

As a vehicle moves on road, I capture lat/lons. I want to calculate the distance the vehicle traveled between each reported LatLon.

Let me mimic your use case with scenario of calculating distance that was made by hurricane Maria in 2017

Below query "prepares" data for this (BigQuery Standard SQL):

#standardSQL
WITH points AS (
    SELECT ROW_NUMBER() OVER(ORDER BY iso_time) pos,
        ST_GEOGPOINT(longitude, latitude) AS point
    FROM `bigquery-public-data.noaa_hurricanes.hurricanes`
    WHERE name LIKE '%MARIA%' AND season = '2017'
        AND ST_DWITHIN(ST_GEOGFROMTEXT('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'), ST_GEOGPOINT(longitude, latitude), 10)
), lines AS (
    SELECT pos, ST_MAKELINE(LAG(point) OVER(ORDER BY pos), point) line
    FROM points
) 
SELECT pos, line
FROM lines
WHERE pos > 1

above can be visualized as

Now, below calculates the distance that hurricane "traveled"

#standardSQL
WITH points AS (
  SELECT ROW_NUMBER() OVER(ORDER BY iso_time) pos,
    ST_GEOGPOINT(longitude, latitude) AS point
  FROM `bigquery-public-data.noaa_hurricanes.hurricanes`
  WHERE name LIKE '%MARIA%' AND season = '2017'
    AND ST_DWITHIN(ST_GEOGFROMTEXT('POLYGON((-179 26, -179 48, -10 48, -10 26, -100 -10.1, -179 26))'), ST_GEOGPOINT(longitude, latitude), 10)
), lines AS (
  SELECT pos, ST_MAKELINE(LAG(point) OVER(ORDER BY pos), point) line
  FROM points
) 
SELECT SUM(ST_LENGTH(line)) path_length
FROM lines
WHERE pos > 1

Note: ST_LENGTH(geography_expression) returns the total length in meters of the lines in the input GEOGRAPHY

You can read more about ST_LENGTH here

Calculate distance on a polyline of a road between 2 lat/lons

1 Answers