交差した線分を取得

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

Copied!

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;

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

Copied!

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;