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

tinlyx
  • 11,057
  • 18
  • 71
  • 119
Huniya Sohail
  • 11
  • 1

1 Answers1

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.

Vincent Bré
  • 4,090
  • 7
  • 23
  • 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 09 '19 at 11:02