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:

sample scenario


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
(SELECT  pt, ST_MakeLine(sp,ep) As line
 -- extract the endpoints for every 2-point line segment for each linestring
  ST_PointN(geom, generate_series(1, ST_NPoints(geom)-1)) as sp,
  ST_PointN(geom, generate_series(2, ST_NPoints(geom)  )) as ep
   -- 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:

result of 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?


You don't have to split the polygon into separate lines, only 1 line would do with ST_ExteriorRing. With that out of the way, the trick is to buffer your building with 1 meter first. Here is a working example:

WITH building AS (   
  SELECT 1 AS id, ST_ExteriorRing(ST_Buffer(ST_MakeEnvelope(0,0,100,100),1)) geom 
) ,
address AS (
    SELECT 1 AS id, ST_MakePoint(50,20) geom 
SELECT  ST_AsText(ST_ClosestPoint(b.geom, a.geom)) geom  
FROM address a INNER JOIN building b ON (a.id = b.id);

Result is: "POINT(50 -1)"

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.