I have a polygon and known 2 points. One is the center of the polygon and another is outside the polygon. I'm trying to find the opposite point of the polygon where the line string of the 2 known points intersects the polygon.
Below is the SQL I'm using to find that point by taking the line string and extending it out past the polygon then getting the last point of intersection.
st_astext(st_endpoint(ST_Intersection(pg.poly_geog::geometry, ( select st_makeline(a, st_translate(b, sin(az2) * len, cos(az2) * len)) from ( select a,b, ST_Azimuth(b,a) as az2, ST_Distance(a,b) + 0.00001 as len from (select st_makepoint(c1.longitude, c1.latitude)::geography::geometry as a, ST_Centroid(st_astext(pg.poly_geog))::geography::geometry as b )a )b))))
This seemed to work until I started running more tests and found that it only works if the linestring between the points is in a certain heading. If the linestring has a heading of right to left it works fine and I can get the first intersection point and the above calculation of the opposite intersection point. If the line string is heading in the oposite direction for another polygon then I'm getting the the point returned from the same side as the intitial intersection.
If I switch around st_endpoint to st_start point then it just changes around the direction that the function works for.
Ive also tried ST_GeometryN as well but that only works in a certain direction also.
How can I get this to function regardless of the heading on the linestring or how can I determine whether I need to use startpoint or endpoint