The trip data contains the origin-destination of each trip, and both origin and destination are stored as geometries. How could I retrieve the origin and destination polygons that the points are located?

The following code works fine to find the polygon which the origin is located. I need to determine the destination_polygon as well.

SELECT p.id as origin_polygon
FROM trips t, polygons p
WHERE ST_Contains(p.geom, t.origin) 

You can use INNER JOIN to get the polygons for the origin and target at the same time:

SELECT p1.id as original_polygon , p2.id as destination_polygon
FROM trips t 
INNER JOIN polygons p1 ON ST_Contains(p1.the_geom, t.origin)
INNER JOIN polygons p2 ON ST_Contains(p2.the_geom, t.destination)

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.