1

Consider the following image; enter image description here

I have points (shown in blue circles) stored in my database with their geometry and an attribute value. Additionally, I have another table with User Points (yellow rhomboids). I want to get the projection of user points on the path line of the vehicle points on the basis of their proximity to path line and also on those project points on line, I want to interpolate the value of attribute values of the nearby vehicle points.

So far, I have been able to get the points as shown in yellow circle using ST_ClosestPoint but currently I am unable to proceed further to get the interpolated values of the attribute and assign it to the yellow circles.

  • Some combo of ST_LineInterpolatePoint, ST_LineLocatePoint and ST_ClosestPoint. To be honest I don't really understand the question, and to how the interpolation is supposed to work. And, they are rhomboids, not rectangles :-) – John Powell May 24 '18 at 8:45
  • What part of the question are you unable to comprehend specifically? I want to get the values at the yellow circles with the help of its nearby blue circle values. – raosaeedali May 24 '18 at 9:11
  • The arrows pointing down towards "interpolated points in vehicle path line" is very confusing. Based on what you have siad, you would seem to need ST_LineInterpolatePoint. – John Powell May 24 '18 at 9:12
  • what @JohnPowellakaBarça said; ST_LineLocatePoint returns the fraction [0, 1] of linelength of the projected point (i.e. sort of one step further than ST_ClosestPoint and skipping the geometry creation), which you could use directly as percentage to interpolate between the two Vehicle Points attributes. wrap the nearest line search in a LATERAL JOIN query (i.e. (K) Nearest Neighbor (KNN). – ThingumaBob May 24 '18 at 9:20
  • do the edges have a reference attribute to link to both their connected Vehicle Points? – ThingumaBob May 24 '18 at 9:31
1

If you intend on running this over and over, have possible other/future tasks that need edges to work with or those tables are very large, I recommend to create a properly indexed edges table with src_id&trg_id and src_val&trg_val (ids and values, name them as you like of course) of their connecting Vehicle Points. Using these columns, it will be easy to interpolate from one connected point's value to the other by getting a fraction of their value's difference.

I will assume you have those edges stored as such a table and with the above naming; running

SELECT up.id,
       edg.src_val + (edg.trg_val - edg.src_val) * ST_LineLocatePoint(edg.geom, up.geom) AS int_val,
       ST_ClosestPoint(edg.geom, up.geom) AS geom
FROM <user_points> AS up
JOIN LATERAL (
  SELECT src_val,
         trg_val,
         geom
  FROM edges
  ORDER BY up.geom <-> geom
  LIMIT 1
) AS edg
ON true;

will return the <user_points>.id, the projected point geom on the closest edge of each point and the interpolated value int_val (interpolated based on the fraction of line length from the start at which the closest point was projected). Add <user_points> columns as you need in the outer query.
Using the <-> KNN operator, this will also make excellent use of the spatial index on both tables.

Update:
Based on your info, you can create the edge table with

CREATE TABLE edges AS
  SELECT ROW_NUMBER() OVER() AS id,
         edgs.*
  FROM (
    SELECT id AS src_id,
           LEAD(id) OVER(ORDER BY id) AS trg_id,
           val AS src_val,
           LEAD(val) OVER(ORDER BY id) AS trg_val,
           ST_MakeLine(geom, LEAD(geom) OVER(ORDER BY id)) AS geom
    FROM points_from_video
  ) AS edgs
  WHERE edgs.geom IS NOT NULL;

ALTER TABLE edges ADD PRIMARY KEY (id);

CREATE INDEX sidx_edges_geom
  ON edges
  USING GIST (geom);

Can't test right now, not 100% sure if ST_MakeLine returns NULL or throws an error if the last row is processed and no leading row was found. Give a shout if you encounter an error.

  • Troubling you again but could you please tell me how can I make edges table from vehicle points and its attribute value, call it val. ? – raosaeedali May 25 '18 at 6:08
  • @raosaeedali yes and no; you seem to have done it, albeit on-the-fly I take it? how did you find pairs of points? one would e.g. need an attribute/sequence number to know which two points have to be connected, or some rules to do so...could you elaborate, then I'll happily edit my answer to include that step. – ThingumaBob May 25 '18 at 9:55
  • So far, I have been able to make projection of the points on the line using this; SELECT ST_LineInterpolatePoint(foo.the_line, ST_LineLocatePoint(foo.the_line, ST_GeomFromText(ST_AsText(points_nearby_pathline.geom)))) as geom FROM (SELECT ST_MakeLine(location) the_line FROM points_from_video ) As foo, points_nearby_pathline; I don't know how can I store the attribute value from points_from_video in the newly projected points. – raosaeedali May 25 '18 at 11:56
  • 1
    basically, you can make that edge table with CREATE TABLE edges AS (SELECT ST_MakeLine(location) AS the_line FROM points_from_video). this will create one (possibly random) linestring from those points, in order of their rows passed into ST_MakeLine. – ThingumaBob May 26 '18 at 7:52
  • 1
    tried the updated query and it worked like a charm. Also, now I am able to perform the interpolation as per your first query. However could you please clarify that in the selection query 2nd line (edg.trg_val - src.src_val) you meant (edg.trg_val - edg.src_val) right? – raosaeedali May 28 '18 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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