I have points (addresses) and multiline strings (streets) i.e.,
street tables in my PostgreSQL 9.5 db. Table
ap contains 3 rows (sample points) while
street contains 85 rows. The sample scenario is shown below.
What I need:
For each point
ap, I need to identify whether it is on "left side" or "right side" of the
On GIS SE, I found some relevant questions already. For example:
- Detecting if point is on left or right side of line in PostGIS? and
- Determining what side of line point is on using PostGIS?
The following code is adapted from the first link.
With cp as ( Select a.gid ap_id, a.geom ap, ST_SetSRID(ST_LineMerge(b.geom), 3044) line, ST_SetSRID(ST_ClosestPoint(b.geom, a.geom), 3044) p from ap a Left Join street b on ST_Dwithin(ST_SetSRID(b.geom, 3044), ST_SetSRID(a.geom, 3044), 25) Order by a.gid, ST_Distance(b.geom, a.geom) ) , h as ( Select ap_id, ap, line, p, ST_MakeLine(p, ap) vec, ST_MakeLine(p, ST_LineInterpolatePoint(line, ST_LineLocatePoint(line, p) * 1.01)) seg From cp Order by ap_id ) Select ap_id, degrees(ST_Azimuth(ST_StartPoint(vec), ST_EndPoint(vec))) - degrees(ST_Azimuth(ST_StartPoint(seg), ST_EndPoint(seg))) diff from h order by ap_id
The above code (for the sample scenario) works and the output is:
ap_id diff 1 -269.999999988824 2 89.9999999946886 3 269.999999980956
However, the code for another point dataset (87 rows), throws following error:
ERROR: line_interpolate_point: 2nd arg isn't within [0,1]
Also, the above code doesn't seem to work for cases when street is east-west having bearing of 90 degrees (Reference, here). I need to perform this task of identifying points for a large dataset. Thus, I am looking for suggestions to get through this problem. Is there any alternative way to identify whether points are on left side or right side of the street?