[postgis-users] How to get an interpolated point on a linestring and make sure ST_Intersects returns True?

[Alessandro Pasotti]

Hi all,

This is my first post on this list: a warm hello to everybody :)

I'm trying to get a point on a linestring such as ST_Intersects with the 
original linestring will return True but it is failing in some real life 
situations (postgres 9.3, postgis version: 2.1 USE_GEOS=1 USE_PROJ=1 
USE_STATS=1)

Is this due to rounding errors?
Is there another (possibly working) way to do that without using buffers?

This is my test case:


DROP TABLE IF EXISTS trenches;
CREATE TABLE trenches
(
id serial NOT NULL,
the_geom geometry(LineString,27700) NOT NULL,
CONSTRAINT trenches_pkey PRIMARY KEY (id)
);


DROP TABLE IF EXISTS closures;
CREATE TABLE closures
(
id serial NOT NULL,
the_geom geometry(Point,27700) NOT NULL,
CONSTRAINT closures_pkey PRIMARY KEY (id)
);

INSERT INTO trenches (id, the_geom) VALUES (1,
ST_GeomFromText('LineString (456155.9028 233348.049, 456159.086 
233347.094, 456171.74 233339.13, 456173.65 233338.179, 456175.802 
233340.965, 456193.711 233324.886)', 27700));

-- insert point interpolated on the trench
INSERT INTO closures (id, the_geom) VALUES (1, (SELECT 
ST_LineInterpolatePoint(t.the_geom , 0.1)
FROM trenches t WHERE id = 1));

-- insert something straight

INSERT INTO trenches (id, the_geom) VALUES (2,
ST_GeomFromText('LINESTRING(459000 224700, 459100 224700)', 27700));

-- insert a point interpolated on the trench
INSERT INTO closures (id, the_geom) VALUES (2, (SELECT 
ST_LineInterpolatePoint(t.the_geom , 0.1)
FROM trenches t WHERE id = 2));

-- test
SELECT c.id, t.id, ST_Intersects(c.the_geom, t.the_geom) FROM closures 
c, trenches t WHERE t.id = c.id;




Kind regards.

-- 
Alessandro Pasotti
w3: www.itopen.it