Happy PostGIS Day everyone!
One of most annoying things about PostGIS is that sometimes spatial analysis functions fail due to GEOS TopologyException. This happens because of one of participating geometries doesn’t conform to validity rules. One of most common validity error is a self-intersection – a “hourglass polygon”. It’s a well known issue and has well-known, but someteimes weird (zero-sized buffer, I’m looking at you!) solutions. But the documentation states that LINESTRINGs are always valid, so there won’t be problems when working with linear geometries, right?
Imagine that you have some network (road, rail, electric, water…) and want to perform some network analysis on it. The way to go is to use the pgRouting extension, which is built on top on PostGIS. To convert a set of LINESTRINGs to a network topology suitable for analysis, you will want to use the pgr_nodeNetwork function first. And then you may encounter an error…
ERROR: line_interpolate_point: 3rd arg isn't within [0,1] CONTEXT: SQL statement "insert INTO public.my_network_noded (old_id,sub_id,geom) ( with cut_locations AS ( SELECT l1id AS lid, locus FROM inter_loc -- then generates start AND end locus for each line that have to be cut buy a location point UNION ALL SELECT i.l1id AS lid, 0 AS locus FROM inter_loc i left join public.my_network b on (i.l1id = b.id) UNION ALL SELECT i.l1id AS lid, 1 AS locus FROM inter_loc i left join public.my_network b on (i.l1id = b.id) ORDER BY lid, locus ), -- we generate a row_number index column for each input line -- to be able to self-join the table to cut a line between two consecutive locations loc_with_idx AS ( SELECT lid, locus, row_number() over (partition by lid ORDER BY locus) AS idx FROM cut_locations ) -- finally, each original line is cut with consecutive locations using linear referencing functions SELECT l.id, loc1.idx AS sub_id, st_linesubstring(l.geom, loc1.locus, loc2.locus) AS geom FROM loc_with_idx loc1 join loc_with_idx loc2 using (lid) join public.my_network l on (l.id = loc1.lid) WHERE loc2.idx = loc1.idx+1 -- keeps only linestring geometries AND geometryType(st_linesubstring(l.geom, loc1.locus, loc2.locus)) = 'LINESTRING') " PL/pgSQL function pgr_nodenetwork(text,double precision,text,text,text,text,boolean) line 229 at EXECUTE
But why? Well, linestrings are always valid, but not always simple. A common example of non-simple linestring is a closed ring – a roundabout junction, for example. The another one is a self-intersecting line. Closed rings are OK for pgRouting, but self-intersecting lines are not:
So, if you ever encounter the “line_interpolate_point: 3rd arg isn’t within [0,1] ” error when trying to node a network of linestrings in pgRouting, check your geometries using the ST_IsSimple function:
SELECT * FROM my_network WHERE ST_IsSimple(geom) = FALSE;
If the self-intersections are perfectly valid from business standpoint, you will have to split that lines into smaller pieces for pgRouting purposes.