I have a geometry (polygon) which I want to extend with a buffer while testing on it in a where clause. The problem is that I would like the buffer to be only applied according to one or more directions (e.g. North and East) and not in all directions. Is there a way to do it combining operators into a single query? If not, how would it be feasible with a stored procedure?
I didn't quite understood how to use ST_Affine to complete this task, but only shifting the geometry was not enough and convexhull would distort the geometry, so I'm sorry if it is out of topic.
My solution was quite straight forward:
create Union of Original and Shifted geometry
DumpPoints from Original geom, shift them in desired direction by desired distance
- create lines which will connect the original position and shifted position of the point
- Union this directional lines with the Boundary of Unioned polygon
- and BuildArea with it
I have it in a function, where the _geom, _point_distance_m are declared:
WITH w_only_shifted AS ( SELECT ST_Union( _geom, ST_Translate(_geom, -_point_distance_m, -_point_distance_m) ) AS geom ) ,w_original_points AS ( SELECT ST_DumpPoints(geom) AS dp FROM (SELECT _geom AS geom) AS t1 ) ,w_dumped AS ( SELECT (dp).path, (dp).geom FROM w_original_points ) ,w_coordinates AS ( SELECT st_x(geom) AS x, st_y(geom) AS y, geom, path FROM w_dumped ) ,w_shift_points AS ( SELECT path, x, y, geom, x-_point_distance_m AS x_shift, y-_point_distance_m AS y_shift, ST_SetSRID(ST_MakePoint(x-_point_distance_m, y-_point_distance_m),3035) AS geom_shift FROM w_coordinates ) ,w_make_line AS ( SELECT ST_Union(ST_MakeLine(geom,geom_shift)) AS line FROM w_shift_points ) ,w_union_all AS ( SELECT ST_Union(t1.line, ST_Boundary(t2.geom)) AS all_lines FROM w_make_line AS t1, w_only_shifted AS t2 ) SELECT ST_buildarea(all_lines) FROM w_union_all;
If I understad what you are trying to do, there should be more efficient ways than dealing with buffer.
ST_Dwithin is recomended instead of buffer->intersects
If you combine ST_Dwithin with an directional operator, you should be able to do what you want ( if I don't miss you intentions) http://postgis.net/docs/manual-2.2/reference.html#Operators