[postgis-users] Creating a boundary of an aggregate of buildings

[Luca Bertoncello]

Am 19.01.2021 09:01, schrieb Hugues François:

Hi Hugues

> The attached function could help you to solve your issues and test
> several thresholds when adapted to your own database. I'm sorry it's
> written in a weird French-English style but the queries should help
> you to understand the variable names meaning. Of course, feel free to
> ask any question.
> 
> The hole issue is directly adressed line 55 to line 85. It uses
> st_dumprings along with st_exterrioring and st_buildarea and compares
> hole surface to the "surf_trou" threshold.

Thank you very much. I'll try to adapt your functions to the 
OpenStreetMaps' data.

Now I'm trying to reduce the "little spots" on the map joining the table 
with the "city's building boundaries" with a table with the "city's 
administrative boundaries" and select only the building in a city, but 
it does not work...

My query:

           SELECT
                city_boundaries.way
            FROM city_boundaries, city_admin_boundaries
            WHERE ST_Within(city_boundaries.way, 
city_admin_boundaries.way) AND
                  (city_admin_boundaries.population >= 18000 OR 
city_admin_boundaries.km2 >= 50);

Unfortunately I get many little spots outsides the cities and inside the 
cities I have few spots with buildings.
If I just use:

           SELECT
                city_boundaries.way
            FROM city_boundaries
            WHERE (ST_Area(ST_Transform(way, 4326)::geography) / 1000000) 
 > 3.5;

I get many spots outside the city and great building's spots inside the 
cities.

city_admin_boundaries was created as:

CREATE TABLE city_admin_boundaries AS
SELECT
     planet_osm_polygon.way,
     planet_osm_polygon.admin_level,
     planet_osm_point.name,
     planet_osm_point.place,
     CASE
      WHEN (planet_osm_point.tags->'population' ~ '^[0-9]{1,8}$') THEN 
(planet_osm_point.tags->'population')::INTEGER ELSE 0
     END as population,
     (ST_Area(ST_Transform(planet_osm_polygon.way, 4326)::geography) / 
1000000) AS km2
   FROM planet_osm_polygon
   JOIN (
     SELECT name, MAX(admin_level) AS al
     FROM planet_osm_polygon
     WHERE boundary = 'administrative' AND admin_level IN ('4', '6', '8') 
AND osm_id < 0 GROUP BY name
   ) size USING(name)
   JOIN planet_osm_point USING (name)
WHERE planet_osm_polygon.boundary = 'administrative' AND
planet_osm_polygon.admin_level = size.al AND
(
   (
     planet_osm_polygon.admin_level IN ('6', '8') AND
     planet_osm_point.place IN ('city', 'town')
   ) OR
   (
     planet_osm_polygon.admin_level = '4' AND
     planet_osm_point.place = 'city'
   )
) AND
planet_osm_polygon.osm_id < 0;

and contains correct data, since I display the administrative boundaries 
on the map, too and they are correct.

Could someone explain me where is my error?

Thanks
Luca Bertoncello
(lucabert at lucabert.de)