1

I'm pretty new to PostgreSQL/PostGIS.

I have a (very large) set of records in a PostGIS enabled PostgreSQL database. It has two lat/long points per record, which I've used to define the spatial geometry as lines defined by two points: start x,y , end x,y per record (where x,y are lat/long coordinates in EPSG 4326). I want to run these records through a spatial join to a set of polygons I've imported into PostgreSQL, such that the result contain all lines whose start x,y OR end x,y are within the polygons. I'm not sure how to do this. I tried ST_WITHIN but that seems to truncate the lines in some instances. ST_INTERSECT seems to give me all lines that cross into the polygon, regardless of the start or end location. I'm not sure how to form my query to do what I need to do. I suspect I need to call out the lat/long of the start and end points explicitly in the join, but not clear on the syntax. Suggestions?

2
  • like this? gis.stackexchange.com/questions/160142/…
    – Mapperz
    Feb 9, 2023 at 18:55
  • 1
    You can certainly filter on ST_Intersects for search purposes, then AND a test for both ST_StartPoint and ST_EndPoint with ST_Within. You need to provide the SQL you have before someone can suggest an improvement.
    – Vince
    Feb 9, 2023 at 18:56

1 Answer 1

1

You can use ST_EndPoint and ST_StartPoint

SELECT *
FROM myLine l
WHERE EXISTS (
 SELECT 1
 FROM myPolygon p
 WHERE st_interescts(ST_StartPoint(l.geom),p.geom))
  OR  st_interescts(ST_EndPoint(l.geom),p.geom))
)
1
  • This worked perfectly. Thank you!
    – cj bee
    Feb 10, 2023 at 20:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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