I trying to create an UPDATE function that will ultimately return straight-line distance to the nearest node on an OSM network (which will be later added to pgrouting distances I have already calculated).

This is the basic function (with actual coordinates) that I want to replicate:

SELECT st_distance_sphere/1000 
FROM ST_Distance_Sphere((SELECT the_geom 
                        FROM ways_vertices_pgr 
                        ORDER BY the_geom <-> ST_GeometryFromText('POINT(22.685133 41.554309)')
                        LIMIT 1),
                       (SELECT * 
                        FROM ST_GeometryFromText('POINT(22.685133 41.554309 )')));

which returns distance in kilometers perfectly. When I insert variable names into the POINT(X Y) bit, it attempts to parse the variable names. I get the same when trying to calculate the constituent start and end points of the ST_Distance function. Using more or less this code:

UPDATE start_end  
SET nearest_dist = (SELECT st_distance_sphere/1000 
                    FROM ST_Distance_Sphere(
                        (SELECT the_geom 
                         FROM ways_vertices_pgr 
                         ORDER BY the_geom <-> ST_GeometryFromText('POINT(start_end.y1 start_end.x1)')
                         LIMIT 1), 
                       (SELECT * 
                        FROM ST_GeometryFromText('POINT(start_end.y1 start_end.x1)')));

Is there a ready way to adapt the first function using variable names? or does this run deeper than syntax?


Maybe I miss some point, but I don't see why you would create points that way.

You'd better use the ST_MakePoint(start_end.y1, start_end.x1)

instead of ST_GeometryFromText('POINT(start_end.y1 start_end.x1)

Note: I would expect X to be first, but your example shows Y first. Also be careful that you may have to set an SRID with ST_SetSrid

  • So, yes, Step 1 is return geometry from XY coordinates (including the nearest node). ST_MakePoint was able to accept the variable names as you have them written in both functions. At this point I am able to run each function independently and generate two columns of type geometry. Step 2 is now feed those columns into ST_Distance_Sphere(), which I should look something like this: UPDATE start_end SET st_distance_sphere = (SELECT st_distance_sphere/1000 FROM ST_Distance_Sphere(start_end.the_geom start_end.the_geom2) GROUP BY start_end.id) WHERE id <= 20; only that is returning syntax error. – patwell Feb 17 '16 at 10:57
  • I guess we have made one step forward, but returned to the original issue: how to reference a column inside of function, in regards to setting up the ST_Distance_Sphere function. – patwell Feb 17 '16 at 11:02
  • What do you meanby reference a column inside of a function. In what language? You have to be careful with the <-> operator, as it only uses an index when the geometry on the right hand side is a constant. – John Powell Feb 17 '16 at 11:06
  • If I understand correctly your operator concern should be okay, and I haven't had issues with other applications on the project. 'Column inside of a function' meaning that I want it to use a subqueries as arguments in a function, in this case ST_Distance_Sphere. Generalized example: St_Distance_Sphere(table.point1 table.point2). I need the outer function to read the results of the inner functions as geometry, not text. I hope that's clear. I am working in Postgresql. – patwell Feb 17 '16 at 11:21
  • Patwell, you have to be more verbose in your question. I see multiple possible issues (e.g. you forgot the alias for st_distance_sphere) but without detailed error messages it is difficult to help. In general I think you have to practice a bit with SQL language, since for what you are trying are plenty of examples around on the internet. – tilt Feb 17 '16 at 11:49

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.