[postgis-users] problem with st_makevalid and (LineString)
Åsmund Tokheim
asmundto at gmail.com
Sat May 17 10:01:27 PDT 2014
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, <islanis at infomed.sld.cu> wrote:
> "Ivan Santiago" <isantiago at ogp.pr.gov> 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: islanis at infomed.sld.cu
>> Sent: 5/17/2014 12:12
>> To: postgis-users at lists.osgeo.org
>> Subject: Re: [postgis-users] problem with st_makevalid and (LineString)
>>
>> "Åsmund Tokheim" <asmundto at gmail.com> 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, <islanis at infomed.sld.cu> wrote:
>>>
>>> islanis at infomed.sld.cu escribió:
>>>>
>>>>
>>>> "Rémi Cura" <remi.cura at gmail.com> 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 <islanis at infomed.sld.cu>:
>>>>>>
>>>>>> "Rémi Cura" <remi.cura at gmail.com> 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 <islanis at infomed.sld.cu>:
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> "Ivan Santiago" <isantiago at ogp.pr.gov> 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: postgis-users-bounces at lists.osgeo.org [mailto:
>>>>>>>>>> postgis-users-bounces at lists.osgeo.org] On Behalf Of
>>>>>>>>>> islanis at infomed.sld.cu
>>>>>>>>>> Sent: Friday, May 16, 2014 11:24 AM
>>>>>>>>>> To: postgis-users at lists.osgeo.org
>>>>>>>>>> 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
>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>>>>>>>>>> _______________________________________________
>>>>>>>>>> postgis-users mailing list
>>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>>> 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
>>>>>>>>> postgis-users at lists.osgeo.org
>>>>>>>>> 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
>>>>>>> postgis-users at lists.osgeo.org
>>>>>>> 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
>>>>> postgis-users at lists.osgeo.org
>>>>> 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
>>>> postgis-users at lists.osgeo.org
>>>> 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
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> 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
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20140517/7bc31de6/attachment.html>
More information about the postgis-users
mailing list