For each row of GPS coordinates, get value from nearest road to coordinates and copy to said row using PostGIS? [closed]

Question

I have two tables in my database; one contains a set of GPS coordinates of a car which have been converted to geom, and another containing Open Street Map road data, including the speed limit for most roads.

For each row of car GPS coordinates, I want to get the speed limit of the road the car is driving on, and add it to said row.

On other words, I am trying to go from this:

Table 1 (gps data; before)

| timestamp    | speed | lat     | lon      | geom      |
| ------------ | ----- | ------- | -------- | --------- |
| 180412235750 | 34.3  | 64.1238 | -21.8217 | 010100... |
| 180412235745 | 26.7  | 64.1331 | -21.8971 | 010100... |

+

Table 2 (osm data)

| gid | osm_id  | ... | maxspeed | ... | geom      |
| --- | ------- | --- | -------- | --- | --------- |
| 1   | 2818432 | ... | 90       | ... | 010500... |
| 2   | 2818434 | ... | 0        | ... | 010500... |

To this: (notice the maxspeed column)

Table 1 (gps data; after)

| timestamp    | speed | lat     | lon      | geom      | maxspeed |
| ------------ | ----- | ------- | -------- | --------- | -------- |
| 180412235750 | 34.3  | 64.1238 | -21.8217 | 010100... | 60       |
| 180412235745 | 26.7  | 64.1331 | -21.8971 | 010100... | 50       |

I have tried various nearest–neighbour quaries from this website and others with limited success.

Edit: Prior attempts at an SQL solution:

My most successful attemp has been the following query:

SELECT r.maxspeed 
FROM roads r, buses b 
WHERE ST_DWithin(b.geom, r.geom, 8) 
ORDER BY ST_Distance(r.geom, b.geom);

Until you realize that it seems to iterate over every road and get the nearest GPS coordinates, instead of the other way around.

It also does not tackle the problem of storing the value to the GPS coordinate row, which I have not been able to find how to do without some sort of ID.

Answer 1

You should look at this this post.

First, add the maxspeed column to table 1 as so:

ALTER TABLE buses ADD maxspeed integer;

Then try:

UPDATE table1
SET maxspeed = (
  SELECT r.maxspeed
  FROM roads r
  ORDER BY table1.geom <-> roads.geom
  LIMIT 1
);