[postgis-users] problem with st_makevalid and (LineString)
Rémi Cura
remi.cura at gmail.com
Sat May 17 10:21:00 PDT 2014
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 <asmundto at gmail.com>:
> 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
>>
>
>
> _______________________________________________
> 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/bdc37e50/attachment.html>
More information about the postgis-users
mailing list