1

I have a column of geometry of type multi-linestring in a database as in the following format

enter image description here

Now I want to add a new column of speed by mapping GPS points against geometry column (as described below that has been listed on separate CSV file).
enter image description here

Tell me how I query to merge the speed column (captured against GPS coordinate points) with existing geometries

Improve this question

1 Answer 1

Reset to default
1

First, you can identify for each point the nearest line by using the postgis ST_Distance function (geometry_point,geometry_line) and a LIMIT 1 to keep only the nearest line.

In a second step, thanks to an attribute join, you will be able to add speed to your lines.

If you need help in executing these requests, don't hesitate to ask.

Improve this answer
1
  • 1
    even better: don't use ST_Distance, use the 'KNN' operator <->: check this answer for an optimized nearest-neighbor search in an UPDATE statement (@HuniyaSohail change accordingly to select the speed column of your points).
    – geozelot
    Dec 9, 2019 at 11:02

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.