Hi Use something like
SELECT st_astext(the_geom), st_astext(st_makevalid(the_geom)) FROM ft_potencial WHERE st_geometrytype(st_makevalid(the_geom)) NOT ilike 'st_polygon' to inspect the rows that caused the initial errors. You can also use the WHERE-part in qgis, or in a DELETE statement to drop the rows (but please try the select statement first). Åsmund On Sat, May 17, 2014 at 6:52 PM, <[email protected]> wrote: > "Ivan Santiago" <[email protected]> escribió: > > Hello there. >> I know this forum is about postgis but sometimes you can use another open >> source solutions. >> You can use QGiS or even better GRASS to clean topological errors. >> Sometimes graphic tools work better for this kind of problems... >> > but the table ft_potencial is big, please how can i do it with qgis or > grass, have you the steps without it render the entire layer, because i > repeat , tha layer is big, i have qgis v1.8.0. > > thanks > > > > >> Sent from my Windows Phone >> ________________________________ >> From: [email protected] >> Sent: 5/17/2014 12:12 >> To: [email protected] >> Subject: Re: [postgis-users] problem with st_makevalid and (LineString) >> >> "Åsmund Tokheim" <[email protected]> escribió: >> >> Hi >>> >>> 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. >>> >>> 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. >>> >>> Åsmund >>> >> well, I need help because I'm not very good at this in the querys, >> following the first advice I'd like eliminate tuples than are not >> polygons and find and delete the tuples that have null-values, how can >> i do that? >> >> thanks >> >> >>> >>> On Sat, May 17, 2014 at 4:20 PM, <[email protected]> wrote: >>> >>> [email protected] escribió: >>>> >>>> >>>> "Rémi Cura" <[email protected]> escribió: >>>> >>>>> >>>>> Hm, >>>>> >>>>>> >>>>>> you have a table with polygons. >>>>>> Obviously some of the polygons are not valid. >>>>>> So when you correct some of the polygon, they are transformed to line >>>>>> (my >>>>>> guess : some of the polygons are wihtout surface, for instance >>>>>> POLYGON((0 0 >>>>>> , 1 1 , 0 0 )) ). >>>>>> Now when trying to insert line into polygon column, postgres >>>>>> complains. >>>>>> >>>>>> >>>>>> You can do 2 things : >>>>>> _solve the postgres error, this imply to be sure to update with >>>>>> polygons >>>>>> _solve the geometry problem, this imply to look a bit into your data >>>>>> to >>>>>> understand why you have invalid polygon in the first place ..; >>>>>> >>>>>> >>>>>> >>>>>> The query I gave you is going to show the invalid polygon and why they >>>>>> are >>>>>> invalid (solving 2.). >>>>>> If you just don't care, you can update only when the result is a >>>>>> polygon.(this querry is suboptimal) >>>>>> >>>>>> UPDATE ft_potencial SETthe_geom=ST_MakeValid(the_geom) >>>>>> WHERE GeometryType(ST_MakeValid(the_geom) ) ILIKE '%POLYGON%'; >>>>>> >>>>>> Cheers, >>>>>> Remi-C >>>>>> >>>>>> What you're doing is ignoring the lines. >>>>> >>>> But that does not bring problems in the future? >>>> >>>> I found this function, but do not know if it is recommended use it to >>>> correct the problems of geometry. I mean cleangeometry >>>> >>>> CREATE OR REPLACE FUNCTION cleanGeometry(geom geometry) >>>> RETURNS geometry AS >>>> $BODY$DECLARE >>>> inGeom ALIAS for $1; >>>> outGeom geometry; >>>> tmpLinestring geometry; >>>> >>>> Begin >>>> >>>> outGeom := NULL; >>>> >>>> -- Clean Process for Polygon >>>> IF (GeometryType(inGeom) = 'POLYGON' OR GeometryType(inGeom) = >>>> 'MULTIPOLYGON') THEN >>>> >>>> -- Only process if geometry is not valid, >>>> -- otherwise put out without change >>>> if not st_isValid(inGeom) THEN >>>> >>>> -- create nodes at all self-intersecting lines by union the polygon >>>> boundaries >>>> -- with the startingpoint of the boundary. >>>> tmpLinestring := st_union(st_multi(st_boundary( >>>> inGeom)),st_pointn(st_boundary(inGeom),1)); >>>> outGeom = st_buildarea(tmpLinestring); >>>> IF (GeometryType(inGeom) = 'MULTIPOLYGON') THEN >>>> RETURN st_multi(outGeom); >>>> ELSE >>>> RETURN outGeom; >>>> END IF; >>>> else >>>> RETURN inGeom; >>>> END IF; >>>> >>>> >>>> ------------------------------------------------------------ >>>> ------------------ >>>> -- Clean Process for LINESTRINGS, self-intersecting parts of linestrings >>>> -- will be divided into multiparts of the mentioned linestring >>>> ------------------------------------------------------------ >>>> ------------------ >>>> ELSIF (GeometryType(inGeom) = 'LINESTRING') THEN >>>> >>>> -- create nodes at all self-intersecting lines by union the linestrings >>>> -- with the startingpoint of the linestring. >>>> outGeom := st_union(st_multi(inGeom),st_pointn(inGeom,1)); >>>> RETURN outGeom; >>>> ELSIF (GeometryType(inGeom) = 'MULTILINESTRING') THEN >>>> outGeom := multi(st_union(st_multi(inGeom),st_pointn(inGeom,1))); >>>> RETURN outGeom; >>>> ELSE >>>> RAISE NOTICE 'The input type % is not supported',GeometryType( >>>> inGeom); >>>> RETURN inGeom; >>>> END IF; >>>> End;$BODY$ >>>> LANGUAGE 'plpgsql' VOLATILE; >>>> >>>> >>>> >>>> >>>> >>>>>> >>>>>> >>>>>> 2014-05-16 23:58 GMT+02:00 <[email protected]>: >>>>>> >>>>>> "Rémi Cura" <[email protected]> escribió: >>>>>> >>>>>>> >>>>>>> Remi i dont understand what do you mean here, >>>>>>> >>>>>>> You can try to analyze a bit to understand better >>>>>>> >>>>>>> (for the following I consider your table doesn't contains too much >>>>>>>> data) >>>>>>>> >>>>>>>> with the_data AS ( >>>>>>>> SELECT the_geom, ST_ISValid(the_geom) AS is_valid, >>>>>>>> ST_IsValidReason(the_geom) >>>>>>>> FROM ft_potencial >>>>>>>> ) >>>>>>>> SELECT *, ST_AsText(ST_MakeValid(the_geom)) AS corrected_geom, >>>>>>>> ST_AsText(the_geom) AS original_geometry, detail.* >>>>>>>> FROM the_data, *ST_IsValidDetail*(the_geom) AS detail >>>>>>>> >>>>>>>> WHERE is_valid = false >>>>>>>> AND GeometryType(the_geom) ILIKE '%POLYGON%' >>>>>>>> >>>>>>>> My guess : you have an invalid no-area polygon, so the makevalid >>>>>>>> function >>>>>>>> transform it into a line >>>>>>>> >>>>>>>> >>>>>>>> ok, but like i say above, i dont understan, please be more >>>>>>> specific, >>>>>>> please, but thanks >>>>>>> >>>>>>> >>>>>>> >>>>>>> Cheers, >>>>>>> >>>>>>>> Rémi-C >>>>>>>> >>>>>>>> >>>>>>>> 2014-05-16 18:14 GMT+02:00 <[email protected]>: >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> "Ivan Santiago" <[email protected]> escribió: >>>>>>>>> >>>>>>>>> >>>>>>>>> Hello: >>>>>>>>> >>>>>>>>> >>>>>>>>> Run >>>>>>>>>> SELECT PostGIS_full_version(); >>>>>>>>>> TO know if your installation has GEOS 3.3.0 or above. >>>>>>>>>> >>>>>>>>>> I get this >>>>>>>>>> >>>>>>>>>> >>>>>>>>> >>>>>>>>> "POSTGIS="2.0.0 r9605" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.8.0, 6 >>>>>>>>> March >>>>>>>>> 2012" GDAL="GDAL 1.9.0, released 2011/12/29" LIBXML="2.7.8" >>>>>>>>> LIBJSON="UNKNOWN" TOPOLOGY RASTER" >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> --------------------------- >>>>>>>>> >>>>>>>>> Iván Santiago >>>>>>>>>> GIS Specialist >>>>>>>>>> Information Technologies >>>>>>>>>> Office of Management and Budget >>>>>>>>>> 787.725.9420 x 2378 >>>>>>>>>> Calle Cruz 254 >>>>>>>>>> PO Box 9023228 >>>>>>>>>> San Juan, PR 00902-3228 >>>>>>>>>> http://gis.pr.gov >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -----Original Message----- >>>>>>>>>> From: [email protected] [mailto: >>>>>>>>>> [email protected]] On Behalf Of >>>>>>>>>> [email protected] >>>>>>>>>> Sent: Friday, May 16, 2014 11:24 AM >>>>>>>>>> To: [email protected] >>>>>>>>>> Subject: [postgis-users] problem with st_makevalid and >>>>>>>>>> (LineString) >>>>>>>>>> >>>>>>>>>> Hello people, i got a problem where i have no idea what can i do. >>>>>>>>>> >>>>>>>>>> I have this table >>>>>>>>>> >>>>>>>>>> CREATE TABLE ft_potencial >>>>>>>>>> ( >>>>>>>>>> id_dw_ft_potencial integer NOT NULL, >>>>>>>>>> id_dw_tipo integer NOT NULL, >>>>>>>>>> id_dw_mes integer NOT NULL, >>>>>>>>>> id_dw_municipio integer NOT NULL, >>>>>>>>>> id_dw_date integer NOT NULL, >>>>>>>>>> potencial real NOT NULL, >>>>>>>>>> area real NOT NULL, >>>>>>>>>> the_geom geometry(Polygon,4326), >>>>>>>>>> CONSTRAINT ft_potencial_pkey PRIMARY KEY (id_dw_ft_potencial, >>>>>>>>>> id_dw_tipo, id_dw_mes, id_dw_municipio, id_dw_date) >>>>>>>>>> ) >>>>>>>>>> WITH ( >>>>>>>>>> OIDS=FALSE >>>>>>>>>> ); >>>>>>>>>> >>>>>>>>>> and i want to clean the errors that it has using the function >>>>>>>>>> ST_MakeValid in this way >>>>>>>>>> >>>>>>>>>> update ft_potencial set the_geom=ST_MakeValid(the_geom); >>>>>>>>>> >>>>>>>>>> but it always return this error >>>>>>>>>> >>>>>>>>>> ERROR: Geometry type (LineString) does not match column type >>>>>>>>>> (Polygon) >>>>>>>>>> >>>>>>>>>> ********** Error ********** >>>>>>>>>> >>>>>>>>>> ERROR: Geometry type (LineString) does not match column type >>>>>>>>>> (Polygon) >>>>>>>>>> Estado SQL:22023 >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> what can i do to fix or clean the geometry without problems. >>>>>>>>>> >>>>>>>>>> please help. >>>>>>>>>> thanks >>>>>>>>>> >>>>>>>>>> ---------------------------------------------------------------- >>>>>>>>>> This message was sent using IMP, the Internet Messaging Program. >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> -- >>>>>>>>>> >>>>>>>>>> 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 >>>>>>>>>> >>>>>>>>>> Infomed: http://www.sld.cu/ >>>>>>>>>> >>>>>>>>>> _______________________________________________ >>>>>>>>>> postgis-users mailing list >>>>>>>>>> [email protected] >>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>>>>> _______________________________________________ >>>>>>>>>> postgis-users mailing list >>>>>>>>>> [email protected] >>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> >>>>>>>>>> ---------------------------------------------------------------- >>>>>>>>> This message was sent using IMP, the Internet Messaging Program. >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> -- >>>>>>>>> >>>>>>>>> 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 >>>>>>>>> >>>>>>>>> Infomed: http://www.sld.cu/ >>>>>>>>> >>>>>>>>> _______________________________________________ >>>>>>>>> postgis-users mailing list >>>>>>>>> [email protected] >>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>>> >>>>>>>> >>>>>>> ---------------------------------------------------------------- >>>>>>> This message was sent using IMP, the Internet Messaging Program. >>>>>>> >>>>>>> >>>>>>> >>>>>>> -- >>>>>>> >>>>>>> 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 >>>>>>> >>>>>>> Infomed: http://www.sld.cu/ >>>>>>> >>>>>>> _______________________________________________ >>>>>>> postgis-users mailing list >>>>>>> [email protected] >>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>>>> >>>>>>> >>>>>>> >>>>>> >>>>> >>>>> ---------------------------------------------------------------- >>>>> This message was sent using IMP, the Internet Messaging Program. >>>>> >>>>> >>>>> >>>>> -- >>>>> >>>>> 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 >>>>> >>>>> Infomed: http://www.sld.cu/ >>>>> >>>>> _______________________________________________ >>>>> postgis-users mailing list >>>>> [email protected] >>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>>> >>>>> >>>> >>>> >>>> ---------------------------------------------------------------- >>>> This message was sent using IMP, the Internet Messaging Program. >>>> >>>> >>>> >>>> -- >>>> >>>> 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 >>>> >>>> Infomed: http://www.sld.cu/ >>>> >>>> _______________________________________________ >>>> postgis-users mailing list >>>> [email protected] >>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >>>> >>>> >>> >> >> >> ---------------------------------------------------------------- >> This message was sent using IMP, the Internet Messaging Program. >> >> >> >> -- >> >> 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 >> >> Infomed: http://www.sld.cu/ >> >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> _______________________________________________ >> postgis-users mailing list >> [email protected] >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >> >> > > > ---------------------------------------------------------------- > This message was sent using IMP, the Internet Messaging Program. > > > > -- > > 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 > > Infomed: http://www.sld.cu/ > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
