Finding Closest Road Segment

[Elham Peiravian]

Hello Everyone, 

I am using the following query to find the closest road segment to a given GPS location:

SELECT id, Distance(segment, GeomFromText('POINT(-79.406166 43.666075)'),1) AS Distance
FROM rs
WHERE ROWID IN (SELECT ROWID FROM SpatialIndex
WHERE f_table_name = 'rs'
AND f_geometry_column = 'segment'
AND search_frame = ST_Expand(MakePoint(--79.406166, 43.666075, 4326), .001) LIMIT 10)
ORDER BY Distance ASC LIMIT 1;

Now I am having trouble at intersections because the GPS points are literarily closer to another segment than the one the car is travelling on. Examples below:

As far as the query goes, what else can I add to it so that even at intersections it will choose the road I am actually traveling on?

[a.fu...@lqt.it]

On Mon, 18 Mar 2024 05:43:09 -0700 (PDT), Elham Peiravian wrote:
> Hello Everyone, 
>
> I am using the following query to find the closest road segment to a
> given GPS location:
>
> SELECT id, Distance(segment, GeomFromText('POINT(-79.406166
> 43.666075)'),1) AS Distance
> FROM rs
> WHERE ROWID IN (SELECT ROWID FROM SpatialIndex
> WHERE f_table_name = 'rs'
> AND f_geometry_column = 'segment'
> AND search_frame = ST_Expand(MakePoint(--79.406166, 43.666075, 4326),
> .001) LIMIT 10)
> ORDER BY Distance ASC LIMIT 1;
>
> Now I am having trouble at intersections because the GPS points are
> literarily closer to another segment than the one the car is
> travelling on. Examples below:
>

> As far as the query goes, what else can I add to it so that even at
> intersections it will choose the road I am actually traveling on?
>

Hi Elham,

It seems like you're running that query cyclically as the
vehicle moves.
and that you therefore know perfectly the street it was on
in the previous iteration.

in the examples you provided we find a potentially ambiguous
situation, because it is not clear whether the car turned at
the intersection or continued straight.
only with the subsequent iteration it will become clear
which of the two roads was actually followed.

conclusion: I really don't think you can do anything
at the SQL query level.
you should instead address the problem of road intersections
in the code that handles your queries, where it shouldn't be
too difficult to understand whether after the intersection
we are still on the old road or there has been a turn.

bye Sandro

[Nolan Darilek]

I've hit this issue as well, not only at intersections but also near
overpasses, or in situations where someone passes under or over another
road segment and temporarily becomes closer to that one.

My solution is similar to the one mentioned below. I hang on to the
previous nearest segment until a) a new segment becomes closer and b)
the distance between the old segment and current position exceeds a
certain threshold, at which point I conclude that the newer closest
segment is the *actual* closest segment.

It's been a while since I've looked at that code so I don't remember the
actual threshold values. I remember them changing based on speed,
though, so they'd be useful both for walking and vehicles. I may have
also added checks to determine if the new nearest segment has nearby
intersections in common with the old nearest segment, and if so the
threshold is lowered a bit to more quickly respond to turning onto a new
road vs. just happening to be nearer to one.

Hope that helps a bit.

[Elham Peiravian]

Thank you Sandro and Nolan for your comments, they were very helpful.

I have another question unrelated to the topic above. Are there any newer versions of Spatilite for mobile apps? The most recent version I came across is 4.3 and it's not the best for what I am trying to achieve in my work.