Hi!
After a successfully import of the new Europe’s data, I’m trying to render the new maps.
Unfortunately, the queries I used last years don’t return the same data…
I explain: I created a table city_admin_boundaries as:
CREATE TABLE city_admin_boundaries AS
SELECT
NEXTVAL('city_admin_boundaries_seq') AS id,
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;
ALTER TABLE city_admin_boundaries OWNER TO _renderd;
ALTER TABLE city_admin_boundaries ADD PRIMARY KEY (id);
CREATE INDEX idx_bigtowns ON city_admin_boundaries (admin_level, km2) WHERE (admin_level = '4' AND km2 >= 100);
CREATE INDEX idx_mintowns ON city_admin_boundaries (admin_level, km2) WHERE (admin_level = '6' AND km2 >= 50);
CREATE INDEX idx_littletowns ON city_admin_boundaries (admin_level, km2) WHERE (admin_level = '8' AND km2 >= 25);
CREATE INDEX idx_mixtowns ON city_admin_boundaries (admin_level, population, km2) WHERE (population > 10000 AND city_admin_boundaries.admin_level = '8' AND km2 >= 100);
After that I created a table city_boundaries to get the physical boundaries of the cities (see this thread) as:
CREATE TABLE city_boundaries AS
SELECT NEXTVAL('city_boundaries_seq') AS id,
city_admin_boundaries.id AS city_admin_boundaries,
ST_CollectionExtract(unnest(ST_ClusterWithin(planet_osm_polygon.way, 200)), 3)::geometry(MultiPolygon, 3857) as way
FROM planet_osm_polygon, city_admin_boundaries
WHERE landuse IN ('residential', 'retail', 'retail;residential', 'commercial', 'school', 'university', 'industrial',
'asphalt', 'cemetery', 'civic', 'civic_admin', 'concrete_surface', 'construction', 'education',
'educational', 'institutional', 'village', 'city', 'town') AND
ST_Within(planet_osm_polygon.way, city_admin_boundaries.way) AND
(
(city_admin_boundaries.admin_level = '4' AND km2 >= 100) OR
(
population > 18000 AND
(
(city_admin_boundaries.admin_level = '6' AND km2 >= 50) OR
(city_admin_boundaries.admin_level = '8' AND km2 >= 25)
)
) OR
(
population > 10000 AND city_admin_boundaries.admin_level = '8' AND km2 >= 100
)
)
GROUP BY city_admin_boundaries.id;
ALTER TABLE city_boundaries OWNER TO _renderd;
ALTER TABLE city_boundaries ADD PRIMARY KEY (id);
ALTER TABLE city_boundaries ADD FOREIGN KEY (city_admin_boundaries) REFERENCES city_admin_boundaries(id) ON DELETE CASCADE;
DELETE FROM city_boundaries WHERE ST_Area(ST_Buffer(way, -50)) < 50;
ALTER TABLE city_boundaries
ADD COLUMN wayn1 geometry(MultiPolygon,3857),
ADD COLUMN wayn2 geometry(MultiPolygon,3857),
ADD COLUMN wayn3 geometry(MultiPolygon,3857),
ADD COLUMN wayn4 geometry(MultiPolygon,3857);
UPDATE city_boundaries SET wayn1 = ST_Multi(ST_SimplifyPreserveTopology(way, 100));
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(wayn1, 50, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -50, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(way, 100, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -100, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(way, 150, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -150, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(way, 200, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -200, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(way, 250, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -250, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
UPDATE city_boundaries SET wayn2 = ST_Multi(ST_Buffer(way, 300, 'join=miter'));
UPDATE city_boundaries SET wayn3 = ST_Multi(ST_Buffer(wayn2, -300, 'join=miter'));
UPDATE city_boundaries SET wayn4 = ST_Makevalid(wayn3);
UPDATE city_boundaries SET way = wayn4;
ALTER TABLE city_boundaries
DROP COLUMN wayn1,
DROP COLUMN wayn2,
DROP COLUMN wayn3,
DROP COLUMN wayn4;
Last year it worked so I had a map with the physical (geografic) boundaries of the cities. This year it returns no data on the map…
I tried this query to check the data (ID 2950 is Dresden):
select id, city_admin_boundaries, ST_Contains(ST_Transform(way, 4326), ST_SetSRID(ST_MakePoint(13.68491287828796, 51.04863253880486), 4326)) from city_boundaries where city_admin_boundaries = 2950;
No entry contains the given point (which is surely in Dresden).
Can someone explain me the reason and maybe help me to correct the tables?
Thanks a lot
Luca
