0

I have 2 polygons and 2 points stored in postgres tables and Im trying to find the front and back of one of the the polygons (green dots) based on a line from the 2 red dots.

I'm trying to find the green dot that is intersected by the line between the 2 red dots.

select
    st_astext(ST_Intersection(p.poly_geog,st_makeline(st_makepoint( c1.longitude, c1.latitude),
                            st_makepoint( c2.longitude,c2.latitude))))
from
    polygonfence p
join index i on
    p.index_id = i.id
join coordinate c1 on
    i.start_coordinate_id = c1.id
join coordinate c2 on
    i.centre_coordinate_id = c2.id

enter image description here

  • 3
    You need to ask one question per question, but at the moment you seem to have two. Can you also explain what you mean by front and back, I see the dots, but front and back don't spring to mind to describe them. – nmtoken Sep 1 at 14:04
  • 1
    Welcome to GIS SE. As a new user, please take the Tour, which emphasizes the importance of asking One question per Question. Please Edit your question to focus on your most pressing issue. – Vince Sep 1 at 14:05
  • Thanks. Ive eddited my question to only have one now and once I get it resolved I will open a second question If im still struggling. I want to find the location of the green dot where the lines intersect but in not getting a point from my query Im getting a line string – briansexton Sep 1 at 14:10
  • 1
    Try : (ST_Dump(ST_Intersection(p.poly_geog, st_makeline(st_makepoint(c1.longitude, c1.latitude), st_makepoint( c2.longitude, c2.latitude))))).geom – J. Monticolo Sep 1 at 14:32
  • No its giving me an error SQL Error [42883]: ERROR: function st_dump(geography) does not exist Ive tried casting to ::geometry as well but its the same error – briansexton Sep 1 at 14:48
0

The ST_Intersection function was returning a linestring for all of the line that fell within the polygon. Adding some explicit typecasts and ST_StartPoint returned the point i was looking for

(st_astext(ST_StartPoint((ST_Intersection(p.poly_geog::geometry, st_makeline(st_makepoint(c1.longitude, c1.latitude)::geography::geometry, st_makepoint( c2.longitude, c2.latitude)::geography::geometry))))))
| improve this answer | |
  • You should apply ST_Boundary(your_polygon_geom) on your polygon geometry before using ST_Intersection(). So you will obtain the right point and not a line. – obchardon Sep 2 at 8:37

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.