2

In Postgres with PostGIS extension, I try to figure out to find the path containing a specific point:

  • table A contains POINT
  • table B contains PATH

My code returns 0 rows when it should

select * from A join B on ST_Contains(A.coordinates::geometry, B.coordinates::geometry);

I also tried to inverse A in B, without success. For the both tables ST_IsValid(coordinate::geometry) return true

New contributor
viasisco is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Just modify ST_CONTAINS with ST_INTERSECTS. – J. Monticolo Feb 12 at 15:53
  • yes, thanks, I've also tried, without success. – viasisco Feb 12 at 15:55
  • But for me SELECT ST_INTERSECTS(ST_MAKEPOINT(10, 47), path('(10, 47)')::geometry); gives me True. – J. Monticolo Feb 12 at 15:59
3

It is in fact very hard to determine if a point is exactly - or not - on a line, because the coordinates have 15 decimals, which are just noise unless you specifically mention them.

What you could do is to look for points that are very very close to the line

SELECT * 
FROM A 
  join B on ST_DWithin(A.coordinates::geometry, B.coordinates::geometry,0.0001);

The distance specified in st_dwithin uses the source coordinate system, so you would have to adjust it (meters, degrees etc)

  • It's perfect and works fine ! – viasisco Feb 12 at 17:14

Your Answer

viasisco is a new contributor. Be nice, and check out our Code of Conduct.

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.