交差した線分を取得

2つのLINESTRINGのa,bのうち、aとbが交差しているaの線分を特定して、線分を返却するプログラム

DROP FUNCTION IF EXISTS get_line_segment(geometry(LineString,4612), geometry(LineString,4612));
CREATE OR REPLACE FUNCTION get_line_segment(base_geom geometry(LineString,4612), target_geom geometry(LineString,4612))
RETURNS geometry(LineString,4612) AS $$
declare
    is_cross boolean := false;
    geom_tmp geometry(LineString,4612);
begin
    FOR i IN 1 .. ST_NumPoints(base_geom) - 1 LOOP
        geom_tmp := ST_GeomFromText('LINESTRING(' || ST_X(ST_PointN(base_geom,i)) || ' ' || ST_Y(ST_PointN(base_geom,i)) || ',' || ST_X(ST_PointN(base_geom,i + 1)) || ' ' || ST_Y(ST_PointN(base_geom,i + 1)) || ')',4612);
        SELECT ST_Intersects(geom_tmp, target_geom)
        INTO is_cross;

        IF is_cross = true THEN
            return geom_tmp;
        END IF;
    END LOOP;
    return NULL;
end;
$$ LANGUAGE plpgsql;

さらに交差していなかった場合に、再近傍の線分を特定して、線分を返却するプログラムは以下

DROP FUNCTION IF EXISTS get_line_segment(geometry(LineString,4612), geometry(LineString,4612));
CREATE OR REPLACE FUNCTION get_line_segment(base_geom geometry(LineString,4612), target_geom geometry(LineString,4612))
RETURNS geometry(LineString,4612) AS $$
declare
    is_cross boolean := false;
    dist double precision := 0.0;
    dist_min double precision := 999999.0;
    geom_tmp geometry(LineString,4612) := NULL;
    dist_min_geom geometry(LineString,4612) := NULL;
begin
    FOR i IN 1 .. ST_NumPoints(base_geom) - 1 LOOP
        geom_tmp := ST_GeomFromText('LINESTRING(' || ST_X(ST_PointN(base_geom,i)) || ' ' || ST_Y(ST_PointN(base_geom,i)) || ',' || ST_X(ST_PointN(base_geom,i + 1)) || ' ' || ST_Y(ST_PointN(base_geom,i + 1)) || ')',4612);
        SELECT ST_Intersects(geom_tmp, target_geom), ST_Distance(geom_tmp, target_geom)
        INTO is_cross, dist;

        IF is_cross = true THEN
            return geom_tmp;
        END IF;

        IF dist < dist_min THEN
            dist_min = dist;
            dist_min_geom = geom_tmp;
        END IF;

    END LOOP;
    return dist_min_geom;
end;
$$ LANGUAGE plpgsql;