Hey, I gave you the 2 queries that eitehr inspect your data or insert only polygon into your column.
As said previously, there is no "magic function". Cheers, Rémi-C 2014-05-17 19:01 GMT+02:00 Åsmund Tokheim <[email protected]>: > 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 >
_______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
