1

I have gone through some relevant questions but my problem is slightly different. I have two tables in my PostgreSQL 9.5 database i.e., line geometry (1 row) and point geometry (2 rows). The scenario is as follows:

scenariohere

As shown above, point 1 is closer to the line as compared to point 2. I would like to generate a small buffer around line and search for points on both sides by conditionally incrementing buffer until both points are found. To make it more clear,

  • start with buffer of 1 meter around line and search for points
  • if point is not found then increment buffer to 2 meter
  • if point on one side is found keep track of it and increment the buffer to 1 meter more to search point on the other side
  • if both points are found, stop buffering and return line with both points with their distances

I am specifically looking for, but not restricted to, PL/PgSQL solution. Can someone suggest me to have some pointers towards my desired output?

Update:

The main purpose to do this is to calculate the distance between opposite points (schools for example) on the same line (street). These schools could be multiple sometimes in the real world scenario and can be on the uneven distances from the street. But, distance between only opposite points (schools) is desired. I did not want to put a limit on search distance from street that is the why I chose for the conditional increment of buffer from street until schools on both sides are found. Search distance could be around 50 meters or 100 meters but this will select schools on other streets as well which is not desired. Table line contains more than 50,000 rows and the process needs to be automated for all lines.

  • Jibran, I feel there might be a better solution for the issue behind your question. Can you explain a bit more on why you want to do this? – tilt Jun 20 '17 at 18:06
  • I need to calculate the distance between these points but with respect to the line. Perhaps, this is as simple as ST_Distance() if take out line from the scenario. – khajlk Jun 20 '17 at 18:10
  • And what do you mean with 'but with respect to the line' ? Do you need the distance from the point to the line as well or only from point to point? What is your real-world example to do this? – tilt Jun 20 '17 at 18:13
  • For each line, I need to calculate the distance between points (on both sides of line). Maybe 'with reference to line' was not the good explanation. In real world, these points could be two schools on both sides of road and I need to calculate the distance between two schools. I am sorry if it was a bad example. – khajlk Jun 20 '17 at 18:18
  • In that case I think you answered it yourself. ST_Distance(point1, point2) would indeed be the right function to use. Unless you have a lot of these points and you only want the distance of points on the same street for instance. – tilt Jun 20 '17 at 18:21
1

You can do it as you wrote using ST_Buffer and some loop increasing buffer value until your query return more than one row... but this will not resolve your problem.

  1. Your points are not perfectly opposite from the road, and in real life they will (almost) never be.
  2. In straight line it's simple but you have also special cases 4 eg. when school is near crossing - it has address from street A but nearest street is B

Good resolution could be to use addresses and firstly discard schools that are only one on the street. Than for every school you have to find nearest point (ST_NearestPoint) on related street, than for pairs with distance between this nearest points lower than x you can count distance.

Other resolution is basing only on distances so it's buggy by definition, but better than none - you have to find nearest point on nearest street

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.