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?
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.