1

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

2

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.

  • 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. – csf May 7 '14 at 12:29
  • 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. – ndawson May 7 '14 at 12:35
  • 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. – csf May 7 '14 at 13:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.