2

I have a polygon and I would like to find all polygons from the table planet_osm_polygon which my polygon lies in or touches. I tried to use st_intersects function but it doesn't work as I would expect. See my command below..

WITH bbox AS
    (SELECT st_transform(st_makeenvelope(9.3183939, 48.8282700, 9.5182078, 48.9375419, 4326), 3857) AS geom)
SELECT p.*
FROM 
    (SELECT * FROM planet_osm_polygon WHERE boundary IS NOT NULL AND admin_level IS NOT NULL) p,
     bbox b
WHERE st_intersects(p.way, b.geom);

It seems that by my query I receive only polygons which are crossing my bbox (see image below) however I would need to receive also polygons which are not just crossing my bbox but also polygons which are completely around my bbox.

enter image description here

Improve this question
4
  • can you clarify which of the polygons in your image are not selected that you would like to select?
    – Ian Turton
    Commented Sep 26, 2019 at 12:09
  • @IanTurton The all polygons on the image are selected (these are counties and regions) but I would like to select also country which my bbox lies in -> this country is not on the image but you can imagine it as a bit polygon which is around of all of selected polygons.
    – bambuste
    Commented Sep 26, 2019 at 12:22
  • 2
    It should be here if it's in your table and it's a valid polygon. Are you sure that you have your country polygon ? I know that when you import osm data of a country from geofabrik for exemple the boundary for the country is not in it because it's this boundary that was used to cut the data. Try to list polygon with admin_level=2
    – robin loche
    Commented Sep 26, 2019 at 12:44
  • @robinloche You are right. This polygon is not presented there. Probably somebody accidentially deleted this polygon from the database, because I already worked with it before. Thanks for the hint!
    – bambuste
    Commented Sep 26, 2019 at 14:12

0

Reset to default

Browse other questions tagged or ask your own question.