1

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.

closed as off-topic by Vince, lynxlynxlynx, xunilk, aldo_tapia, neogeomat May 28 '18 at 18:00

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions seeking help to debug/write/improve code must include the desired behavior, a specific problem or error and the shortest code necessary to reproduce it in the question itself. Providing a clear problem statement and evidence of a code attempt will help others to help you. See: How to create a Minimal, Complete, and Verifiable example." – Vince, lynxlynxlynx, xunilk, aldo_tapia, neogeomat
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Welcome to GIS SE. Thank you for taking the Tour. In order to prevent GIS SE from devolving to a coding service, we require coding questions to contain code. Please Edit the question to contain your attempt at a SQL solution. – Vince May 27 '18 at 17:47
  • I added a little section on my most successful previous attempt. However, I do not think that it is of much use to potential responders as I don't really know what I'm doing. – Árni Dagur May 27 '18 at 18:07
  • 1
    this should get you going. add the maxspeed column to your gps table and run the UPDATE. if you get stuck, give a shout, I'll add an adopted answer (why not now? it's basically a duplicate question) – ThingumaBob May 27 '18 at 19:44
2

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
);

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