1

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?

  • Your SQL doesn't attempt to clip the found geometries; it just returns all objects with envelope overlap with your search polygon. The US is in that list because parts of Alaska are west of 180E, so the US bounding box is from northern Alaska to southern Hawaii and from -180 to +180 longitude (most of the Northern Hemisphere). Russia also spans -180 to +180. – Vince Sep 12 at 15:00
  • for lines and polygons, use the ST_Intersection and ST_Intersects functions in addition; the site provides a lot of answers to your question ... – Cyril Sep 12 at 16:26
  • The %% is an overlap operator using the bounding boxes and not the actual geometries – Daniel Demonceau Sep 12 at 22:43
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.

0

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

0

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

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.