PostGIS – Clipping Polygons and Lines at Exact Borders

clippolygonpostgis

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

Example Screesnhot of part of the admin boundarys

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

Best Answer

@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.

Related Question