I have an address and building table in my PostgreSQL 9.5 (x64) database on Windows 7 (x64) machine. The address points (more than one million) are inside building polygons. The sample scenario is given below:
I want to move (perpendicularly) all address points 1 meter in front of building polygons using PostGIS script.
What I have tried:
I used this link to extract individual linsetrings from polygons and used
ST_ClosestPoint() to project address point to the closest line (since address points geometry is more closer to the building facade side). The code is given below:
Select pt, line, ST_ClosestPoint(line,pt) as close_pt From (SELECT pt, ST_MakeLine(sp,ep) As line FROM -- extract the endpoints for every 2-point line segment for each linestring (SELECT pt, ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp, ST_PointN(geom, generate_series(2, ST_NPoints(geom) )) as ep FROM -- extract the individual linestrings (SELECT (ST_Dump(ST_Boundary(building.geom))).geom, ap.geom As pt FROM ap Left Join building on ST_Contains(building.geom, ap.geom) ) AS linestrings ) AS segments ) As foo;
The approach might not be realistic and there could be more better and efficient way to achieve this task. So far, I ended up with this scenario using above code:
I only need the blue point in highlighted shape and this point needs to be moved orthogonally one meter in front of building polygon. Can anyone suggest me how to proceed further from this point?