Thanks, Sandro!
It worked with your LOOP! Great!
The problem I can see on the result (I'm simplifying country polygons) is that
the large polygon of Finland, Germany, Italy and Egypt is missing.
I've tried to change the different tolerances (Simplify tolerance and your loop
snap-tolerance). If I use simplifying tolerance 2500 m the missing polygons are
there, but not when using 5000 m. The countries included in the original layer
is from Sweden, Norway and Finland down to Jordan, Egypt, Libya and Tunisia.
Ref sys =WGS 84_UTM-33N
If you have the time and want to look at the SQL sequence, here it is;
Thank you,
Paul
CREATE TABLE "simple_countries2nd" as (
SELECT "NAME_ZH", "OGR_STYLE", fid, (st_dump(the_geom)).*
FROM "countries2nd");
CREATE INDEX "simple_countries2nd_geom_gist" ON "simple_countries2nd" USING
gist(geom);
ALTER TABLE "simple_countries2nd" ADD COLUMN simple_geom geometry(POLYGON,
32633);
SELECT topology.CreateTopology('topo1', 32633);
DO $$
DECLARE
rec RECORD;
BEGIN
FOR rec in SELECT "NAME_ZH", "OGR_STYLE", "fid", (ST_Dump(the_geom)).geom
FROM "countries2nd"
LOOP
BEGIN
IF GeometryType(rec.geom) = 'POLYGON' THEN
PERFORM topology.TopoGeo_AddPolygon('topo1', rec.geom, 400);
ELSIF GeometryType(rec.geom) = 'LINESTRING' THEN
PERFORM topology.TopoGeo_AddLinestring('topo1', rec.geom, 400);
ELSIF GeometryType(rec.geom) = 'POINT' THEN
PERFORM topology.TopoGeo_AddPoint('topo1', rec.geom, 400);
END IF;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'For geometry % we got exception % (%)', rec.id, SQLERRM,
SQLSTATE;
END;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
SELECT topology.CreateTopology('topo2', 32633);
SELECT topology.ST_CreateTopoGeo('topo2', "the_geom")
FROM (
SELECT ST_Collect(st_simplifyPreserveTopology(geom, 5000)) as "the_geom"
FROM topo1.edge_data
) AS foo;
with simple_face AS (
SELECT topology.st_getFaceGeometry('topo2', face_id) as "the_geom"
FROM topo2.face
WHERE face_id > 0
) UPDATE "simple_countries2nd" d SET geom = sf."the_geom"
FROM simple_face sf
WHERE st_intersects(d.geom, sf."the_geom")
AND st_area(st_intersection(sf."the_geom", d.geom))/st_area(sf."the_geom") >
0.5;
-----Ursprungligt meddelande-----
Från: postgis-users [mailto:postgis-users-boun...@lists.osgeo.org] För Sandro
Santilli
Skickat: den 25 februari 2020 15:38
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] ST_CreateTopoGeo
On Tue, Feb 25, 2020 at 01:22:39PM +0000, paul.m...@lfv.se wrote:
> Hi,
> Where do you mean I can play with the tolerance?
In TopoGeo_addLinestring
https://postgis.net/docs/manual-3.1/TopoGeo_AddLineString.html
> This is what I have done before the ST_createTopoGeo
> SELECT topology.CreateTopology('topo1', 4326)";
> SELECT topology.ST_CreateTopoGeo('topo1', ST_Collect(geom)) from
> "countries_first";
> Btw, I'm intending to simplify later on in my SQL command list.
You could do something like this:
DO $$
DECLARE
rec RECORD;
tol FLOAT8;
BEGIN
tol := 0;
FOR rec in SELECT gid, (ST_Dump(geom)).geom FROM countries_first
LOOP
BEGIN
IF GeometryType(rec.geom) = 'POLYGON' THEN
PERFORM topology.TopoGeo_AddPolygon('topo1', rec.geom, tol);
ELSIF GeometryType(rec.geom) = 'LINESTRING' THEN
PERFORM topology.TopoGeo_AddLinestring('topo1', rec.geom, tol);
ELSIF GeometryType(rec.geom) = 'POINT' THEN
PERFORM topology.TopoGeo_AddPoint('topo1', rec.geom, tol);
END IF;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'For geometry % we got exception % (%)', rec.id,
SQLERRM, SQLSTATE;
END;
END LOOP;
END;
$$ LANGUAGE 'plpgsql';
You can tweak the above to do something different rather than raising
a WARNING (for example store ID of offending geoms in a table).
Then you can look at the offending geometries in isolation, possibly
tweaking the "tol" variable.
--strk;
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
