How do I conditionally increment buffer in PLPGSQL/PostGIS?

Question

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:

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.

Answer 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