PostgreSQL/PostGIS table of lines / spatial join to polygons

Question

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?

Answer 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))
)