<div dir="ltr">Hi<div><br></div><div>I don't think that you can expect the st_makevalid function to correct the underlying problem with your data. As Rèmi said, some geometries in your original table might have incorrectly been labeled as polygons when the coordinates suggests that they are linestrings. In my eyes, the most correct solution would be to remove the Polygon-constraint on the data. In some applications it might be better to use st_buffer to generate a small polygon around those linestrings, or even more simply discard those rows. You have to use your knowledge of the application and the source of the data to decide how these geometries should best be handled. Inspecting the geometries that are turned into linestrings might aid you in finding a good solution.</div>
<div><br></div><div>As for the cleanGeometry function, I'm not familiar with it, but it seems to me like the aforementioned line-polygons would be turned into null-values by the st_buildarea function. So just like st_makevalid, it isn't a one-liner you can use to make all your problems go away.</div>
<div><br></div><div>Åsmund</div></div><div class="gmail_extra"><br><br><div class="gmail_quote">On Sat, May 17, 2014 at 4:20 PM, <span dir="ltr"><<a href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><a href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a> escribió:<div class=""><br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
"Rémi Cura" <<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>> escribió:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Hm,<br>
<br>
you have a table with polygons.<br>
Obviously some of the polygons are not valid.<br>
So when you correct some of the polygon, they are transformed to line (my<br>
guess : some of the polygons are wihtout surface, for instance POLYGON((0 0<br>
, 1 1 , 0 0 )) ).<br>
Now when trying to insert line into polygon column, postgres complains.<br>
<br>
<br>
You can do 2 things :<br>
_solve the postgres error, this imply to be sure to update with polygons<br>
_solve the geometry problem, this imply to look a bit into your data to<br>
understand why you have invalid polygon in the first place ..;<br>
<br>
<br>
<br>
The query I gave you is going to show the invalid polygon and why they are<br>
invalid (solving 2.).<br>
If you just don't care, you can update only when the result is a<br>
polygon.(this querry is suboptimal)<br>
<br>
UPDATE ft_potencial SETthe_geom=ST_MakeValid(the_<u></u>geom)<br>
WHERE GeometryType(ST_MakeValid(the_<u></u>geom) ) ILIKE '%POLYGON%';<br>
<br>
Cheers,<br>
Remi-C<br>
</blockquote></blockquote>
What you're doing is ignoring the lines.<br>
But that does not bring problems in the future?<br>
<br></div>
I found this function, but do not know if it is recommended use it to correct the problems of geometry. I mean cleangeometry<br>
<br>
CREATE OR REPLACE FUNCTION cleanGeometry(geom geometry)<br>
RETURNS geometry AS<br>
$BODY$DECLARE<br>
inGeom ALIAS for $1;<br>
outGeom geometry;<br>
tmpLinestring geometry;<br>
<br>
Begin<br>
<br>
outGeom := NULL;<br>
<br>
-- Clean Process for Polygon<br>
IF (GeometryType(inGeom) = 'POLYGON' OR GeometryType(inGeom) = 'MULTIPOLYGON') THEN<br>
<br>
-- Only process if geometry is not valid,<br>
-- otherwise put out without change<br>
if not st_isValid(inGeom) THEN<br>
<br>
-- create nodes at all self-intersecting lines by union the polygon boundaries<br>
-- with the startingpoint of the boundary.<br>
tmpLinestring := st_union(st_multi(st_boundary(<u></u>inGeom)),st_pointn(st_<u></u>boundary(inGeom),1));<br>
outGeom = st_buildarea(tmpLinestring);<br>
IF (GeometryType(inGeom) = 'MULTIPOLYGON') THEN<br>
RETURN st_multi(outGeom);<br>
ELSE<br>
RETURN outGeom;<br>
END IF;<br>
else<br>
RETURN inGeom;<br>
END IF;<br>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>------------------<br>
-- Clean Process for LINESTRINGS, self-intersecting parts of linestrings<br>
-- will be divided into multiparts of the mentioned linestring<br>
------------------------------<u></u>------------------------------<u></u>------------------<br>
ELSIF (GeometryType(inGeom) = 'LINESTRING') THEN<br>
<br>
-- create nodes at all self-intersecting lines by union the linestrings<br>
-- with the startingpoint of the linestring.<br>
outGeom := st_union(st_multi(inGeom),st_<u></u>pointn(inGeom,1));<br>
RETURN outGeom;<br>
ELSIF (GeometryType(inGeom) = 'MULTILINESTRING') THEN<br>
outGeom := multi(st_union(st_multi(<u></u>inGeom),st_pointn(inGeom,1)));<br>
RETURN outGeom;<br>
ELSE<br>
RAISE NOTICE 'The input type % is not supported',GeometryType(<u></u>inGeom);<br>
RETURN inGeom;<br>
END IF;<br>
End;$BODY$<br>
LANGUAGE 'plpgsql' VOLATILE;<div class="HOEnZb"><div class="h5"><br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
<br>
<br>
<br>
2014-05-16 23:58 GMT+02:00 <<a href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a>>:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
"Rémi Cura" <<a href="mailto:remi.cura@gmail.com" target="_blank">remi.cura@gmail.com</a>> escribió:<br>
<br>
Remi i dont understand what do you mean here,<br>
<br>
You can try to analyze a bit to understand better<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
(for the following I consider your table doesn't contains too much data)<br>
<br>
with the_data AS (<br>
SELECT the_geom, ST_ISValid(the_geom) AS is_valid,<br>
ST_IsValidReason(the_geom)<br>
FROM ft_potencial<br>
)<br>
SELECT *, ST_AsText(ST_MakeValid(the_<u></u>geom)) AS corrected_geom,<br>
ST_AsText(the_geom) AS original_geometry, detail.*<br>
FROM the_data, *ST_IsValidDetail*(the_geom) AS detail<br>
<br>
WHERE is_valid = false<br>
AND GeometryType(the_geom) ILIKE '%POLYGON%'<br>
<br>
My guess : you have an invalid no-area polygon, so the makevalid function<br>
transform it into a line<br>
<br>
</blockquote>
<br>
ok, but like i say above, i dont understan, please be more specific,<br>
please, but thanks<br>
<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Cheers,<br>
Rémi-C<br>
<br>
<br>
2014-05-16 18:14 GMT+02:00 <<a href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a>>:<br>
<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
"Ivan Santiago" <<a href="mailto:isantiago@ogp.pr.gov" target="_blank">isantiago@ogp.pr.gov</a>> escribió:<br>
<br>
<br>
Hello:<br>
<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
Run<br>
SELECT PostGIS_full_version();<br>
TO know if your installation has GEOS 3.3.0 or above.<br>
<br>
I get this<br>
</blockquote>
<br>
<br>
"POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 March<br>
2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8"<br>
LIBJSON="UNKNOWN" TOPOLOGY RASTER"<br>
<br>
<br>
<br>
<br>
---------------------------<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
Iván Santiago<br>
GIS Specialist<br>
Information Technologies<br>
Office of Management and Budget<br>
<a href="tel:787.725.9420%20x%202378" value="+17877259420" target="_blank">787.725.9420 x 2378</a><br>
Calle Cruz 254<br>
PO Box 9023228<br>
San Juan, PR 00902-3228<br>
<a href="http://gis.pr.gov" target="_blank">http://gis.pr.gov</a><br>
<br>
<br>
-----Original Message-----<br>
From: <a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.<u></u>osgeo.org</a> [mailto:<br>
<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.<u></u>osgeo.org</a>] On Behalf Of<br>
<a href="mailto:islanis@infomed.sld.cu" target="_blank">islanis@infomed.sld.cu</a><br>
Sent: Friday, May 16, 2014 11:24 AM<br>
To: <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
Subject: [postgis-users] problem with st_makevalid and (LineString)<br>
<br>
Hello people, i got a problem where i have no idea what can i do.<br>
<br>
I have this table<br>
<br>
CREATE TABLE ft_potencial<br>
(<br>
id_dw_ft_potencial integer NOT NULL,<br>
id_dw_tipo integer NOT NULL,<br>
id_dw_mes integer NOT NULL,<br>
id_dw_municipio integer NOT NULL,<br>
id_dw_date integer NOT NULL,<br>
potencial real NOT NULL,<br>
area real NOT NULL,<br>
the_geom geometry(Polygon,4326),<br>
CONSTRAINT ft_potencial_pkey PRIMARY KEY (id_dw_ft_potencial,<br>
id_dw_tipo, id_dw_mes, id_dw_municipio, id_dw_date)<br>
)<br>
WITH (<br>
OIDS=FALSE<br>
);<br>
<br>
and i want to clean the errors that it has using the function<br>
ST_MakeValid in this way<br>
<br>
update ft_potencial set the_geom=ST_MakeValid(the_<u></u>geom);<br>
<br>
but it always return this error<br>
<br>
ERROR: Geometry type (LineString) does not match column type (Polygon)<br>
<br>
********** Error **********<br>
<br>
ERROR: Geometry type (LineString) does not match column type (Polygon)<br>
Estado SQL:22023<br>
<br>
<br>
what can i do to fix or clean the geometry without problems.<br>
<br>
please help.<br>
thanks<br>
<br>
------------------------------<u></u>------------------------------<u></u>----<br>
This message was sent using IMP, the Internet Messaging Program.<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de correo electronico<br>
que<br>
ofrece Infomed para respaldar el cumplimiento de las misiones del<br>
Sistema<br>
Nacional de Salud. La persona que envia este correo asume el compromiso<br>
de<br>
usar el servicio a tales fines y cumplir con las regulaciones<br>
establecidas<br>
<br>
Infomed: <a href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
<br>
<br>
</blockquote>
<br>
------------------------------<u></u>------------------------------<u></u>----<br>
This message was sent using IMP, the Internet Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de correo electronico que<br>
ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema<br>
Nacional de Salud. La persona que envia este correo asume el compromiso<br>
de<br>
usar el servicio a tales fines y cumplir con las regulaciones<br>
establecidas<br>
<br>
Infomed: <a href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
<br>
</blockquote>
<br>
</blockquote>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>----<br>
This message was sent using IMP, the Internet Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de correo electronico que<br>
ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema<br>
Nacional de Salud. La persona que envia este correo asume el compromiso de<br>
usar el servicio a tales fines y cumplir con las regulaciones establecidas<br>
<br>
Infomed: <a href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
<br>
</blockquote>
<br>
</blockquote>
<br>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>----<br>
This message was sent using IMP, the Internet Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de correo electronico que ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema Nacional de Salud. La persona que envia este correo asume el compromiso de usar el servicio a tales fines y cumplir con las regulaciones establecidas<br>
<br>
Infomed: <a href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a><br>
</blockquote>
<br>
<br>
<br>
------------------------------<u></u>------------------------------<u></u>----<br>
This message was sent using IMP, the Internet Messaging Program.<br>
<br>
<br>
<br>
--<br>
<br>
Este mensaje le ha llegado mediante el servicio de correo electronico que ofrece Infomed para respaldar el cumplimiento de las misiones del Sistema Nacional de Salud. La persona que envia este correo asume el compromiso de usar el servicio a tales fines y cumplir con las regulaciones establecidas<br>
<br>
Infomed: <a href="http://www.sld.cu/" target="_blank">http://www.sld.cu/</a><br>
<br>
______________________________<u></u>_________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-<u></u>bin/mailman/listinfo/postgis-<u></u>users</a></div></div></blockquote></div><br></div>