Sql to view points of intersection with line not working

Question

I have two tables one called test lines and other points called testepoint with some geometries drawn in QGIS to test my sql. Are as in the image below:

QGIS

I want to select the points that inteceptam a row, the result should return me the id of these three points:

Selected

This is my sql that returns no records:

 Select testepoint."id" 
 FROM
 testepoint
inner JOIN
 teste
 ON ST_Intersects(teste.geom, testepoint.geom)

Can anyone help me on what I might be doing wrong? I drew the shapes in QGIS using Google Streets layer based on EPSG: 3857 and carried in PostGIS

---

I ran the sql:

 Select testepoint."id"
FROM
testepoint
inner JOIN
teste
ON ST_DWithin(teste.geom, testepoint.geom, 1)
WHERE
teste."id"= 1 

The result:

result

But I need this order: 1,2,4 The order of alignment.

order

Answer 1

It's probably because the points don't exactly intersect the line. I'd modify your query to use a distance based search with a very small search distance. Something like this should work:

Select testepoint."id" 
FROM
testepoint
inner JOIN
teste
ON ST_DWithin(teste.geom, testepoint.geom, 1)

Where the "1" represents the largest acceptable distance from the line you want, in whichever units the table's coordinate system uses.

• csf: Very good ndawson! Your tip worked, but I noticed something: I need the points listed in order of the direction in which the line was drawn. Below is the image that best illustrates my goal.
• ndawson: Add an "ORDER BY ST_Line_Locate_Point(teste.geom, testepoint.geom)". That should order it by ascending distance from the start of the line.
• csf: The error: [Err] ERROR: line_locate_point: 1st arg is not a line So I consulted the geometry type of table test (which contains the lines) using ST_GeometryType (Geomagnetic) function and returned ST_MultiLineString.