I have a point table which contains start point and end point:

enter image description here

When I am writing this query in PostgreSQL to create line it gives me this output.

select distinct on (a.id) a.id, 
        min(st_astext(st_makeline(a.geometry, b.geometry))),
        min(st_distance(a.geometry, b.geometry)) as dist
into results 
from fpts1 as a, 
     fpts1 as b  
where not a.geometry = b.geometry 
      and not a.link_id = b.link_id 
      and ST_DWithin(a.geometry, b.geometry,0.0002) 
group BY a.id, 
         ST_Distance(a.geometry, b.geometry);

enter image description here

How I can avoid line creation like this and get output like below image.

enter image description here

  • yes, that is a great couple of answers. Basically, you just need to add ST_Azimuth logic into either of those approaches, which might be easier with the RECURSIVE graph approach. – John Powell Apr 10 at 11:30
  • 1
    you have a set of questions that seem to adress the same issue; although there are a bunch of good ideas in the linked post, I fear your specific problem is near impossible to solve to a satisfying extent (and without the need for extensive manual edits) without any topology between linestrings. if this represents a road network, or parcels, I suggest to rather look at other sources (e.g. OSM) to acquire usable data. – ThingumaBob Apr 10 at 13:29
  • @ cyril thank you for help .its almost similar to what i ma looking for but i have huge data and joining two table is very costly operation for me as it takes alot time. – poonam patel Apr 12 at 4:31
  • @ John Powell .yes, i try to use azimuth angle but i think its not useful as i have perpendicular lines too. – poonam patel Apr 12 at 4:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.