[postgis-users] problem with st_makevalid and (LineString)
Roxanne Reid-Bennett
rox at tara-lu.com
Sat May 17 11:13:55 PDT 2014
"Islanis" never did state why he was running ST_MakeValid to begin
with... only that it results in Linestrings trying to be inserted into a
Polygon column. He already stated that he isn't that comfortable with
the SQL side of things. So, unless he knows that MakeValid is what he
really needs, then it might be beneficial to know the result of:
SELECT DISTINCT St_IsValidReason(the_geom)
FROM ft_potential
and whether that error condition is "best" fixed through MakeValid...
[because Remi is right there is no magic function]
Given a really large table - I'd normally build a work table first with
the keys and geometries of interest (e.g. those that fail the ST_IsValid
test)... and manipulate only those geometries until they were valid,
then update the master table.
Process choices aside... back to the original question...
I know that MakeValid can result in Collections. We use it against
Linestrings and it results in a Collection with Linestrings and Points.
So...
SELECT DISTINCT ST_GeometryType(ST_MakeValid(the_geom))
from ft_potential
WHERE not ST_IsValid(the_geom) AND
St_GeometryType(St_MakeValid(the_geom)) not ilike '%poly%'
would verify the kind of results where it isn't a polygon. If there are
collections in all or some then...
SELECT id_dw_ft_potencial,
array_agg(ST_GeometryType((ST_Dump(ST_MakeValid(the_geom))).geom))
from ft_potential
WHERE St_GeometryType(St_MakeValid(the_geom)) ilike '%collection%'
group by id_dw_ft_potencial
Will identify what is in them. If there are both Linestrings and
Polygons... then
He can dump them and filter the Linestrings from that...the following
will remove anything except Polygons from any Collections that come out
of ST_MakeValid
SELECT id_dw_ft_potencial, ST_Collect(geom) as poly
FROM (
SELECT id_dw_ft_potencial,
(ST_Dump(ST_MakeValid(the_geom))).geom as geom
FROM ft_potential
WHERE not ST_IsValid(the_geom)
AND ST_GeometryType(ST_MakeValid(the_geom)) ilike
'%collection%'
) a
WHERE ST_GeometryType(geom) ilike '%poly%'
GROUP BY id_dw_ft_potencial
<caution/quidado>
However, unless he knows that what is being deleted really is not vital
to the resulting polygon the above potentially destroys the business
validity of his geometries... It depends really on why they were
invalid to start with.
</caution/quidado>
For this especially... I'd build a work table with the results of the
above, verify it contains what I want, then update the master table...
measure twice, cut once...
But this is just speculation. I don't know what kind of geometry is
actually coming out of MakeValid. (other than some of them contain
Linestrings).
Roxanne
On 5/17/2014 1:01 PM, Åsmund Tokheim wrote:
> 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
> <mailto:islanis at infomed.sld.cu>> wrote:
>
> "Ivan Santiago" <isantiago at ogp.pr.gov
> <mailto: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 <mailto:islanis at infomed.sld.cu>
> Sent: 5/17/2014 12:12
> To: postgis-users at lists.osgeo.org
> <mailto:postgis-users at lists.osgeo.org>
> Subject: Re: [postgis-users] problem with st_makevalid and
> (LineString)
>
> "Åsmund Tokheim" <asmundto at gmail.com
> <mailto: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
> <mailto:islanis at infomed.sld.cu>> wrote:
>
> islanis at infomed.sld.cu <mailto:islanis at infomed.sld.cu>
> escribió:
>
>
> "Rémi Cura" <remi.cura at gmail.com
> <mailto: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
> <mailto:islanis at infomed.sld.cu>>:
>
> "Rémi Cura" <remi.cura at gmail.com
> <mailto: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
> <mailto:islanis at infomed.sld.cu>>:
>
>
>
> "Ivan Santiago"
> <isantiago at ogp.pr.gov
> <mailto: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
> <tel:787.725.9420%20x%202378>
> 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>
> [mailto:
> postgis-users-bounces at lists.osgeo.org
> <mailto:postgis-users-bounces at lists.osgeo.org>]
> On Behalf Of
> islanis at infomed.sld.cu
> <mailto:islanis at infomed.sld.cu>
> Sent: Friday, May 16, 2014
> 11:24 AM
> To:
> postgis-users at lists.osgeo.org
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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
> <mailto: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 <mailto: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/0e6d23c9/attachment.html>
More information about the postgis-users
mailing list