Clipping polygons and lines at exact borders within PostGIS

Question

I have OSM data of the whole world in a PostGIS db. For some reasons, I want to work with tables which have only data from a given bounding box inside.

I create this tables with following command:

CREATE TABLE export.osm_adminareas_a AS 
  SELECT * 
  FROM public.osm_adminareas_a 
  WHERE geom && ST_MakeEnvelope(12,47,13,48, 4326)

But only when doing this with point geometry, the result is what I expect, e.g. only the points inside the bounding box. When doing this with lines or polygon, I will get data not only outside the box, but also from regions which were absolutely far away from my box.

(For example my box is in the border region of Austria and Germany and I will get admin borders from France, and even Russia or the USA,....)

What are your recommendations for clipping polygon and line geometry exactly at the borders of my bounding box?

Answer 1

@Vince gave an educational comment, @Cyril named it directly, and I like to avoid further confusion; the simple answer is: clip those grometries!

All queries present in this post do one thing, one way or another, that is selecting every row whose geometries minimum bounding rectangle, the bbox, intersects the created envelope, another bbox.

And that's good, the bbox intersection search will drastically reduce the actual (costly) clipping operation that is now needed to retrieve only those parts of the found geometries that actually intersect:

CREATE TABLE export.osm_adminareas_a AS
  SELECT <col_1>,
         <col_n>,
         ST_Intersection(a.geom, ST_MakeEnvelope(12,47,13,48, 4326)) AS geom
  FROM   public.osm_adminareas_a AS a
  WHERE  a.geom && ST_MakeEnvelope(12,47,13,48, 4326)
    -- AND  ST_Intersects(a.geom, ST_MakeEnvelope(12,47,13,48, 4326))
;

The ST_Intersection is the crucial part here, as it recreates a geometry that represents the intersecting part only. To not retrieve the original geometry along you have to name all columns except the geom column.

I included the conditional ST_Intersects check; use whatever combo you like, I doubt there will be any noticeable performance gains with whatever condition.

Answer 2

Nearly perfect answer from @ThingumaBob

Only thing to extend ist that in his script the geometry type will only by geometry instead of Multipolygon. So i extended your example with the ST_Multi() function:

CREATE TABLE export.osm_adminareas_a_clip5 AS
  SELECT a.fclass,
         ST_Multi(ST_Intersection(a.geom, ST_MakeEnvelope(12, 47, 13, 48, 4326)))::geometry(Multipolygon,4326) AS geom
  FROM   public.osm_adminareas_a AS a
  WHERE  geom && ST_MakeEnvelope(12, 47, 13, 48, 4326)
      AND  ST_Intersects(a.geom, ST_MakeEnvelope(12, 47, 13, 48, 4326)) 

Answer 3

Try the following with both the overlap condition and the intersects

CREATE TABLE export.osm_adminareas_a AS 
    SELECT * 
    FROM public.osm_adminareas_a 
    WHERE geom && ST_MakeEnvelope(12, 47, 13, 48, 4326)
    AND ST_INTERSECTS(geom, ST_MakeEnvelope(12,47,13,48, 4326))

The reason why I include the overlap condition is that the ST_INTERSECTS is costly, and thus running it only on overlapping boundings boxes speeds up the process