<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
<div class="moz-cite-prefix">Thanks all, so<br>
<br>
if I try like Sandro said :<br>
<br>
UPDATE ec SET geom = ST_SnapToGrid(geom, 1);<br>
UPDATE ec SET topo_geom = toTopoGeom(geom, 'ec_topo', 1, 1.0);<br>
<br>
I have this error :<br>
SQL/MM Spatial exception - curve not simple<br>
<br>
If i increase tolerance parameter, I have :<br>
ERREUR: SQL/MM Spatial exception - point not on edge<br>
<br>
With Rémi's solution :<br>
<br>
UPDATE ec SET topo_geom = rc_totopogeomwithouterrors(geom,
'ec_topo', 1, 1.0);<br>
<br>
It works with a lot (76) of NOTICE: this geometry can't be
converted to topogeom :<br>
but other geometry worked<br>
<br>
so it's better<br>
<br>
Now, I have to know why some geometry doesn't work<br>
<br>
Maybe I have to explain more what I'm trying to do<br>
<br>
My data overlap and there are not clean. Sometimes polygon should
have jointly vertex but it's not the case. (I join a image)<br>
<br>
I want to snap when the distance is smaller than tolerance and
intersect polygon when they overlap.<br>
<br>
Do you think that use topology is the best way to do that ?<br>
<br>
Thanks a lot<br>
<br>
regards<br>
<br>
Ludovic
<p style="margin-bottom: 0cm"><br>
</p>
<title></title>
<meta name="GENERATOR" content="LibreOffice 4.0.4.2 (Linux)">
<style type="text/css">
<!--
@page { margin: 2cm }
P { margin-bottom: 0.21cm }
A:link { so-language: zxx }
-->
</style><br>
<br>
<br>
Le 15/01/2014 13:49, Rémi Cura a écrit :<br>
</div>
<blockquote
cite="mid:CAJvUf_sbwX33n2hKGDRLFewEHU1TW=jgQNm5Vf01tC4bDRrw-A@mail.gmail.com"
type="cite">
<div>-- Function: public.rc_totopogeomwithouterrors(geometry,
character varying, integer, double precision)</div>
<div><br>
</div>
<div>-- DROP FUNCTION public.rc_totopogeomwithouterrors(geometry,
character varying, integer, double precision);</div>
<div><br>
</div>
<div>CREATE OR REPLACE FUNCTION
public.rc_totopogeomwithouterrors(ageom geometry, atopology
character varying, alayer integer, atolerance double precision
DEFAULT 0)</div>
<div> RETURNS topogeometry AS</div>
<div>$BODY$</div>
<div>DECLARE</div>
<div> layer_info RECORD;</div>
<div> topology_info RECORD;</div>
<div> rec RECORD;</div>
<div> tg topology.TopoGeometry;</div>
<div> elems topology.TopoElementArray = '{{0,0}}';</div>
<div> sql TEXT;</div>
<div> typ TEXT;</div>
<div> tolerance FLOAT8;</div>
<div>BEGIN</div>
<div><br>
</div>
<div>raise notice 'ageom : %',ST_AsText(ageom);</div>
<div> -- Get topology information</div>
<div> BEGIN</div>
<div> SELECT *</div>
<div> FROM topology.topology</div>
<div> INTO STRICT topology_info WHERE name = atopology;</div>
<div> EXCEPTION</div>
<div> WHEN NO_DATA_FOUND THEN</div>
<div> RAISE EXCEPTION 'No topology with name "%" in
topology.topology',</div>
<div> atopology;</div>
<div> END;</div>
<div><br>
</div>
<div> -- Get tolerance, if 0 was given</div>
<div> tolerance := COALESCE( NULLIF(atolerance, 0),
topology._st_mintolerance(atopology, ageom) );</div>
<div><br>
</div>
<div> -- Get layer information</div>
<div> BEGIN</div>
<div> SELECT *, CASE</div>
<div> WHEN feature_type = 1 THEN 'puntal'</div>
<div> WHEN feature_type = 2 THEN 'lineal'</div>
<div> WHEN feature_type = 3 THEN 'areal'</div>
<div> WHEN feature_type = 4 THEN 'mixed'</div>
<div> ELSE 'unexpected_'||feature_type</div>
<div> END as typename</div>
<div> FROM topology.layer l</div>
<div> INTO STRICT layer_info</div>
<div> WHERE l.layer_id = alayer</div>
<div> AND l.topology_id = <a moz-do-not-send="true"
href="http://topology_info.id">topology_info.id</a>;</div>
<div> EXCEPTION</div>
<div> WHEN NO_DATA_FOUND THEN</div>
<div> RAISE EXCEPTION 'No layer with id "%" in topology "%"',</div>
<div> alayer, atopology;</div>
<div> END;</div>
<div><br>
</div>
<div> -- Can't convert to a hierarchical topogeometry</div>
<div> IF layer_info.level > 0 THEN</div>
<div> RAISE EXCEPTION 'Layer "%" of topology "%" is
hierarchical, cannot convert to it.',</div>
<div> alayer, atopology;</div>
<div> END IF;</div>
<div><br>
</div>
<div><br>
</div>
<div> -- </div>
<div> -- Check type compatibility and create empty TopoGeometry</div>
<div> -- 1:puntal, 2:lineal, 3:areal, 4:collection</div>
<div> --</div>
<div> typ = geometrytype(ageom);</div>
<div> IF typ = 'GEOMETRYCOLLECTION' THEN</div>
<div> -- A collection can only go collection layer</div>
<div> IF layer_info.feature_type != 4 THEN</div>
<div>
RAISE EXCEPTION</div>
<div> 'Layer "%" of topology "%" is %, cannot hold a
collection feature.',</div>
<div> layer_info.layer_id, <a moz-do-not-send="true"
href="http://topology_info.name">topology_info.name</a>,
layer_info.typename;</div>
<div> END IF;</div>
<div> tg := topology.CreateTopoGeom(atopology, 4, alayer);</div>
<div> ELSIF typ = 'POINT' OR typ = 'MULTIPOINT' THEN -- puntal</div>
<div> -- A point can go in puntal or collection layer</div>
<div> IF layer_info.feature_type != 4 and
layer_info.feature_type != 1 THEN</div>
<div> RAISE EXCEPTION</div>
<div> 'Layer "%" of topology "%" is %, cannot hold a puntal
feature.',</div>
<div> layer_info.layer_id, <a moz-do-not-send="true"
href="http://topology_info.name">topology_info.name</a>,
layer_info.typename;</div>
<div> END IF;</div>
<div> tg := topology.CreateTopoGeom(atopology, 1, alayer);</div>
<div> ELSIF typ = 'LINESTRING' or typ = 'MULTILINESTRING' THEN --
lineal</div>
<div> -- A line can go in lineal or collection layer</div>
<div> IF layer_info.feature_type != 4 and
layer_info.feature_type != 2 THEN</div>
<div> RAISE EXCEPTION</div>
<div> 'Layer "%" of topology "%" is %, cannot hold a lineal
feature.',</div>
<div> layer_info.layer_id, <a moz-do-not-send="true"
href="http://topology_info.name">topology_info.name</a>,
layer_info.typename;</div>
<div> END IF;</div>
<div> tg := topology.CreateTopoGeom(atopology, 2, alayer);</div>
<div> ELSIF typ = 'POLYGON' OR typ = 'MULTIPOLYGON' THEN -- areal</div>
<div> -- An area can go in areal or collection layer</div>
<div> IF layer_info.feature_type != 4 and
layer_info.feature_type != 3 THEN</div>
<div> RAISE EXCEPTION</div>
<div> 'Layer "%" of topology "%" is %, cannot hold an areal
feature.',</div>
<div> layer_info.layer_id, <a moz-do-not-send="true"
href="http://topology_info.name">topology_info.name</a>,
layer_info.typename;</div>
<div> END IF;</div>
<div> tg := topology.CreateTopoGeom(atopology, 3, alayer);</div>
<div> ELSE</div>
<div> -- Should never happen</div>
<div> RAISE EXCEPTION</div>
<div> 'Unsupported feature type %', typ;</div>
<div> END IF;</div>
<div><br>
</div>
<div> -- Now that we have a topogeometry, we loop over distinct
components </div>
<div> -- and add them to the definition of it. We add them as
soon</div>
<div> -- as possible so that each element can further edit the</div>
<div> -- definition by splitting</div>
<div><br>
</div>
<div>--modified to catch errors:</div>
<div>BEGIN</div>
<div><br>
</div>
<div> FOR rec IN SELECT DISTINCT id(tg), alayer as lyr,-- geom,</div>
<div> CASE WHEN ST_Dimension(geom) = 0 THEN 1</div>
<div> WHEN ST_Dimension(geom) = 1 THEN 2</div>
<div> WHEN ST_Dimension(geom) = 2 THEN 3</div>
<div> END as type,</div>
<div> CASE WHEN ST_Dimension(geom) = 0 THEN</div>
<div> topology.topogeo_addPoint(atopology, geom,
tolerance)</div>
<div> WHEN ST_Dimension(geom) = 1 THEN</div>
<div> topology.topogeo_addLineString(atopology, geom,
tolerance)</div>
<div> WHEN ST_Dimension(geom) = 2 THEN</div>
<div> topology.topogeo_addPolygon(atopology, geom,
tolerance)</div>
<div> END as primitive</div>
<div> FROM (SELECT (ST_Dump(ageom)).geom) as f</div>
<div> WHERE NOT ST_IsEmpty(geom)</div>
<div> LOOP</div>
<div> --raise notice 'coucou, tg : %, geom %',<a
moz-do-not-send="true" href="http://rec.id">rec.id</a>,
ST_AsText(rec.geom);</div>
<div> -- TODO: consider use a single INSERT statement for the
whole thing</div>
<div>
sql := 'INSERT INTO ' || quote_ident(atopology)</div>
<div> || '.relation(topogeo_id, layer_id, element_type,
element_id) VALUES ('</div>
<div> || <a moz-do-not-send="true" href="http://rec.id">rec.id</a>
|| ',' || rec.lyr || ',' || rec.type</div>
<div> || ',' || rec.primitive || ')';</div>
<div> EXECUTE sql;</div>
<div> END LOOP;</div>
<div><br>
</div>
<div> RETURN tg;</div>
<div> </div>
<div>EXCEPTION</div>
<div> WHEN SQLSTATE 'P0001' THEN</div>
<div> RAISE NOTICE 'this geometry can''t be converted to
topogeom : %</div>
<div><span class="" style="white-space:pre"> </span>doing
nothing',ageom;</div>
<div><span class="" style="white-space:pre"> </span>RETURN NULL;<span
class="" style="white-space:pre"> </span></div>
<div>END;</div>
<div>END</div>
<div>$BODY$</div>
<div> LANGUAGE plpgsql VOLATILE STRICT</div>
<div> COST 100;</div>
<div>ALTER FUNCTION public.rc_totopogeomwithouterrors(geometry,
character varying, integer, double precision)</div>
<div> OWNER TO postgres;</div>
</blockquote>
<br>
</body>
</html>