After a specific demand in my work that needed to create a single line from two untouched geometries (lines), I needed to develop a function and follow for contribution:

Passing as a parameter two separate non-touching geometries (lines), a single complete line is returned filling the empty space between the nearest ends.enter image description here


CREATE OR REPLACE FUNCTION public.st_mergecloselines(
    geometry, geometry)
    RETURNS geometry
    LANGUAGE 'sql'
-- This function merge two lines that don't within (dont touching) returing a single multiline. By Emilson Ribeiro Neto - emilsonribeiro@hotmail.com

select ST_LineMerge(st_union(st_union($1,       
            WHEN (st_distanceSphere(ST_StartPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_StartPoint($1),ST_EndPoint($2))
                and (st_distanceSphere(ST_StartPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_EndPoint($1),ST_StartPoint($2)))
                and (st_distanceSphere(ST_StartPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_EndPoint($1),ST_EndPoint($2)))
              ) THEN st_makeLine(ST_StartPoint($1),ST_StartPoint($2))

            WHEN   (st_distanceSphere(ST_EndPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_StartPoint($1),ST_EndPoint($2))
                and (st_distanceSphere(ST_EndPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_StartPoint($1),ST_StartPoint($2)))
                and (st_distanceSphere(ST_EndPoint($1),ST_StartPoint($2)) < st_distanceSphere(ST_EndPoint($1),ST_EndPoint($2)))
              ) THEN st_makeLine(ST_EndPoint($1),ST_StartPoint($2)) 

            WHEN   (st_distanceSphere(ST_StartPoint($1),ST_EndPoint($2)) < st_distanceSphere(ST_StartPoint($1),ST_StartPoint($2))
                and (st_distanceSphere(ST_StartPoint($1),ST_EndPoint($2)) < st_distanceSphere(ST_EndPoint($1),ST_StartPoint($2)))
                and (st_distanceSphere(ST_StartPoint($1),ST_EndPoint($2)) < st_distanceSphere(ST_EndPoint($1),ST_EndPoint($2)))
              ) THEN st_makeLine(ST_StartPoint($1),ST_EndPoint($2))
            else  st_makeLine(ST_EndPoint($1),ST_EndPoint($2))

  • 1
    You provide a code but you didn't says what didn't work. Did you get a crash, an error message, a wrong result, nothing... ? tell more (and provide any error message as text) to get answer – J.R Aug 22 at 15:43
  • The code works perfect 100%. I am writing to share functional code that can help someone with the need to have a special line resulting from two untouched lines. Use as you wish and according to your needs. – Emilson Ribeiro Neto Nov 7 at 17:44

Your Answer

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

Browse other questions tagged or ask your own question.