Check if the clipping for diff works

[kartoza]

Check if the clipping for diff works

[Gustry]

Work in progress :

CREATE OR REPLACE FUNCTION clean_tables() RETURNS void AS $$
DECLARE osm_tables CURSOR FOR 
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='public'
    AND table_type='BASE TABLE'
    AND table_name LIKE 'osm_%';
BEGIN
    FOR osm_table IN osm_tables LOOP
        EXECUTE 'DELETE FROM ' || quote_ident(osm_table.table_name) || ' USING clip WHERE ST_Disjoint(geom,geometry);';
    END LOOP;
END;
$$ LANGUAGE plpgsql;

select clean_tables();

Or better with multi rows :

CREATE OR REPLACE FUNCTION clean_tables() RETURNS void AS
$BODY$
DECLARE osm_tables CURSOR FOR 
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='public'
    AND table_type='BASE TABLE'
    AND table_name LIKE 'osm_%';
BEGIN
    FOR osm_table IN osm_tables LOOP
    EXECUTE 'DELETE FROM ' || quote_ident(osm_table.table_name) || ' WHERE osm_id IN (
            SELECT DISTINCT osm_id
            FROM ' || quote_ident(osm_table.table_name) || ' 
            LEFT JOIN clip ON ST_Intersects(geometry, geom)
            WHERE clip.id IS NULL)
        ;';
    END LOOP;
END;
$BODY$
LANGUAGE plpgsql;

select clean_tables();

[timlinux]

Maybe its not necessary these days but it might be worth checking if vacuuming the db after doing bulk deletes is a good idea.

[NyakudyaA]

Not useful now as imposm3 natively supports this. You can use a GeoJSON file to limit the geometries on import

[Gustry]

Do you mean you are going to make the clip shapefile deprecated and add the note about this new geojson file?

[NyakudyaA]

Hi @Gustry it is already deprecated and we have added it to the description

https://github.com/kartoza/docker-osm#clipping