Re: [postgis-users] problem with st_makevalid and (LineString)

[islanis]

I did this

UPDATE ft_potencial SET the_geom=cleanGeometry(the_geom);
and then

DELETE ft_potencial as p where ST_IsEmpty(p.the_geom);

and to check again did this

UPDATE ft_potencial SET the_geom=ST_MakeValid(the_geom);

and gave no error this time.

it reuturns this

"La consulta retornó con éxito: 370296 filas efectadas,  920469 ms de  
tiempo de ejecución."

A question.
Is right how did i check it. or there is another better solution to  
check a layer?


thanks for all





"Rémi Cura" <remi.c...@gmail.com> escribió:

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 <asmun...@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, <isla...@infomed.sld.cu> wrote:

"Ivan Santiago" <isanti...@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: isla...@infomed.sld.cu
Sent: 5/17/2014 12:12
To: postgis-users@lists.osgeo.org
Subject: Re: [postgis-users] problem with st_makevalid and (LineString)

"Åsmund Tokheim" <asmun...@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, <isla...@infomed.sld.cu> wrote:

 isla...@infomed.sld.cu escribió:


 "Rémi Cura" <remi.c...@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 <isla...@infomed.sld.cu>:

 "Rémi Cura" <remi.c...@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 <isla...@infomed.sld.cu>:



 "Ivan Santiago" <isanti...@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-boun...@lists.osgeo.org [mailto:
postgis-users-boun...@lists.osgeo.org] On Behalf Of
isla...@infomed.sld.cu
Sent: Friday, May 16, 2014 11:24 AM
To: postgis-users@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@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@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@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@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@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
postgis-users@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
postgis-users@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@lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users