# Programmatically move points in front of building polygons in PostGIS

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:

Problem:

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?

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
) ,