python postgis ST_ClosestPoint

Question

I am struggling with an SQL command issued from my python script. Here is what I have tried so far, the first example works fine but the rest do not.

#working SQL = "SELECT ST_Distance(ST_Transform(ST_GeomFromText(%s, 4326),27700),ST_Transform(ST_GeomFromText(%s, 4326),27700));"
#newPointSQL = "SELECT ST_ClosestPoint(ST_GeomFromText(%s),ST_GeomFromText(%s));"
#newPointSQL = "SELECT ST_As_Text(ST_ClosestPoint(ST_GeomFromText(%s), ST_GeomFromText(%s)));"
#newPointSQL = "SELECT ST_AsText(ST_ClosestPoint(ST_GeomFromEWKT(%s), ST_GeomFromText(%s)));"
#newPointSQL = "SELECT ST_AsText(ST_Line_Interpolate_Point(ST_GeomFromText(%s),ST_Line_Locate_Point(ST_GeomFromText(%s),ST_GeomFromText(%s))));"

newPointData = (correctionPathLine,pointToCorrect) - ( MULTILINESTRING((-3.16427109855617 55.9273798550064,-3.16462372283029 55.9273883602162)), POINT(-3.164667 55.92739))

My data is picked up ok because the first sql is successfull when executed. The problem is when I use the ST_ClosestPoint function. Can anyone notice a misuse anywhere? Am I using the ST_ClosetsPoint in a wrong way? In the last example, I did modify my data (in case someone notices) to run it but it still would not execute.

I don't understand what your problem is. What error message did you get? When I test your queries they run without any problem. ST_ClosestPoint should have no problems with multigeometries, collections or nested collections. — Nicklas Avén, Jan 01 '12 at 19:12

Paco Valdez · Accepted Answer · 2012-01-03T19:25:48.113

I don't know with what kind of geometries you are dealing with, but I had the same trouble before with MultiLineStrings, I realized that when a MultiLinestring can't be merged, the function ST_Line_Locate_Point doesn't work.(you can know if a MultiLineString can't be merged using the ST_LineMerge function) I've made a pl/pgSQL function based in an old maillist but I added some performance tweaks, It only works with MultiLineStrings and LineStrings (but can be easily modified to work with Polygons). First it checks if the geometry only has 1 dimension, if it has, you can use the old ST_Line_Interpolate_Point and ST_Line_Locate_Point combination, if not, then you have to do the same for each LineString in the MultiLineString. Also I've added a ST_LineMerge for pre 1.5 compatibility :

CREATE OR REPLACE FUNCTION ST_MultiLine_Nearest_Point(amultiline geometry,apoint geometry) 
  RETURNS geometry AS
$BODY$
DECLARE
    mindistance float8;
    adistance float8;
    nearestlinestring geometry;
    nearestpoint geometry;
    simplifiedline geometry;
    line geometry;
BEGIN
        simplifiedline:=ST_LineMerge(amultiline);
        IF ST_NumGeometries(simplifiedline) <= 1 THEN
            nearestpoint:=ST_Line_Interpolate_Point(simplifiedline, ST_Line_Locate_Point(simplifiedline,apoint) );
            RETURN nearestpoint;
      END IF;
--      *Change your mindistance according to your projection, it should be stupidly big*
        mindistance := 100000; 
        FOR line IN SELECT (ST_Dump(simplifiedline)).geom as geom LOOP
                adistance:=ST_Distance(apoint,line);
            IF adistance < mindistance THEN
                mindistance:=adistance;
                nearestlinestring:=line; 
            END IF;
        END LOOP;
        RETURN ST_Line_Interpolate_Point(nearestlinestring,ST_Line_Locate_Point(nearestlinestring,apoint));
    END;
    $BODY$
      LANGUAGE 'plpgsql' IMMUTABLE STRICT;

UPDATE:

As noted by @Nicklas Avén ST_Closest_Point() should work, ST_Closest_Point was added in 1.5 .

Hi, Thank you for your answer, I will try it as soon as I can and let you know of the result. Thank you for your time! — Antony, Dec 12 '11 at 09:46
Unfortunatelly I can only say THANK YOU FOR YOUR HELP!!! It worked perfectly! If there is any other way I can express my gratidute please let me know! — Antony, Dec 12 '11 at 12:40
Don't worry I've made this function some time ago. You could mark the answer as the right answer ;) — Paco Valdez, Dec 12 '11 at 16:05
@pacofvf There should be no problems using ST_ClosestPoint on Multi geoemtries or collections. If you find anything suspicious please report it to the PostGIS mailing list or file a bug. — Nicklas Avén, Jan 01 '12 at 19:15
I posted an example in my blog as a response to your comment http://www.7admite.co.cc/2011/12/how-to-find-closest-point-of.html — Paco Valdez, Jan 03 '12 at 15:42
Yes you are right I have an instance with a higher postgis version, and ST_Closest_Point work with all of my data. — Paco Valdez, Jan 03 '12 at 19:27

python postgis ST_ClosestPoint

1 Answers1