Hi Brent,<br><br>Thanks for the tip. Apart from the use of buffer(buffer(geom, -1),1) instead of buffer(geom, 0), that was my first approach to the solution. But after fixing 3-4 polygons, I thought that it should be an automatic process to do this. My geometries have thousands of polygons, and this method may take a long time. Anyway, I'll test the use of buffer. If fixes the most of my bad-constructed polygons, should be the best solution.<br>
<br>Many thanks!<br><br><br><div class="gmail_quote">On Fri, May 8, 2009 at 12:39 AM, <span dir="ltr"><<a href="mailto:pcreso@pcreso.com">pcreso@pcreso.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
HIi<br>
<br>
I have tried a few ways of dealing with this, including ST_Simplify_PreserveTopology() to try to fix errors.<br>
<br>
I have yet to find a solution that automatically resolves such probles as I think they should be. Using some variation of buffer(buffer(geom,-1),1) instead of buffer(geom,0) sometimes fixes things that the simple buffer of zero has failed to.<br>
<br>
What I have done that works for me is to use QGIS to edit the geometries that are not fixed by the above operations.<br>
<br>
I run isvalid() limit 1, then zoom into the returned coodinates & manually fix the problems, which so far have always been pretty simple & obvious. I then rerun isvalid() & work through each error, It doesn't take long per error, 200 or so can be fixed in a morning without too much trouble.<br>
<br>
Also note that an empty geometry is regarded as an error by some applications, but passes isvalid(), so I also do a check for ST_NumGeomteries()=0 to clean up PostGIS datasets.<br>
<br>
<br>
HTH,<br>
<br>
Brent Wood<br>
<br>
--- On Fri, 5/8/09, Jorge Arévalo <<a href="mailto:jorge.arevalo@gmail.com">jorge.arevalo@gmail.com</a>> wrote:<br>
<br>
> From: Jorge Arévalo <<a href="mailto:jorge.arevalo@gmail.com">jorge.arevalo@gmail.com</a>><br>
> Subject: Re: [postgis-users] Problem with ST_Within, Polygons and Multipolygons<br>
> To: "PostGIS Users Discussion" <<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>
> Date: Friday, May 8, 2009, 9:44 AM<br>
<div class="im">> Hello,<br>
><br>
> Many thanks for your help Kevin. I can confim one thing:<br>
> isvalid only returns the first error. Just today, I realized<br>
> on the big amount of errors of my data, as you said. Indeed,<br>
> I was reading the same link that you provided me about<br>
> geometry validity, and I split my multipolygon into single<br>
> polygons to check them. My conclusions are the same as<br>
> yours: what a mess!<br>
><br>
><br>
><br>
</div><div class="im">> Oh, and thanks again for your response to my original<br>
> question. It helped me a lot... Maybe is it time to ask for<br>
> new data, without errors? If not, how should I<br>
> "clean" my data? I tried with ST_Buffer(geom, 0.0)<br>
> (I read in this link <a href="http://www.bostongis.com/postgis_extent_expand_buffer_distance.snippet" target="_blank">http://www.bostongis.com/postgis_extent_expand_buffer_distance.snippet</a><br>
> that is useful to repair data with self-interesections), but<br>
> doesn't seem to work for me...<br>
><br>
><br>
><br>
> Regards<br>
> Jorge<br>
><br>
><br>
><br>
</div><div class="im">> On Thu, May 7, 2009 at 6:58 PM,<br>
> Kevin Neufeld <<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>><br>
> wrote:<br>
><br>
><br>
> Hi Jorge,<br>
><br>
><br>
><br>
> So, I had a quick look at your data ... I'm sorry to<br>
> say that you've got a lot of problems with your ocean<br>
> polygon.<br>
><br>
> Paul can correct me since he did the work on isvalidreason,<br>
> but I think st_isvalid only returns the first thing it finds<br>
> wrong with your input geometry.<br>
><br>
><br>
><br>
> I did this and found all kinds of things:<br>
><br>
><br>
><br>
> -- Extract the POLYGONs from the MULTI<br>
><br>
> CREATE TABLE water_polys AS<br>
><br>
</div>> SELECT (ST_Dump(the_geom)).geom AS the_geom<br>
><br>
> FROM water;<br>
><br>
><br>
><br>
> SELECT ST_IsValidReason(the_geom)<br>
><br>
> FROM water_polys<br>
><br>
<div><div></div><div class="h5">> WHERE NOT ST_IsValid(the_geom);<br>
><br>
><br>
><br>
> So, looking at the individual polygons (of which only the<br>
> first error is again reported), I found over 66<br>
> self-intersections (figure 8's and so forth) and several<br>
> whose boundaries loop back and touch itself.<br>
><br>
><br>
><br>
> Also, in response to your original question about why a<br>
> point on your airport also intersects the ocean polygon: it<br>
> turns out that the island in question is not a hole in your<br>
> ocean at all ... but a second polygon on top of your ocean<br>
> polygon. This is why your point query returns true - there<br>
> are two surfaces (the island and the water) that intersect<br>
> the point (which, by the way, also makes an invalid<br>
> multipolygon).<br>
><br>
><br>
><br>
><br>
><br>
> A while back I put together some isvalid/issimple geometry<br>
> descriptions with soem pretty pictures:<br>
><br>
> <a href="http://postgis.refractions.net/documentation/manual-svn/ch04.html#OGC_Validity" target="_blank">http://postgis.refractions.net/documentation/manual-svn/ch04.html#OGC_Validity</a><br>
><br>
><br>
><br>
> You may want to have a look through it and clean up your<br>
> dataset. The invalidity will most certainly result in more<br>
> unexpected behaviour.<br>
><br>
><br>
><br>
> Cheers,<br>
><br>
> Kevin<br>
><br>
><br>
><br>
><br>
><br>
> Jorge Arévalo wrote:<br>
><br>
><br>
> Hello,<br>
><br>
><br>
><br>
> I have additional info. When running the method<br>
> "isvalid", I get this warning message:<br>
><br>
><br>
><br>
> NOTICE: Self-intersection at or near point -2749.99<br>
> 4.7955e+06<br>
><br>
><br>
><br>
> What is exactly a "self-intersection"? May this<br>
> "self-intersection" be the cause of my problem?<br>
><br>
><br>
><br>
> Thanks<br>
><br>
> Jorge<br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
> 2009/5/6 Jorge Arévalo <<a href="mailto:jorge.arevalo@gmail.com">jorge.arevalo@gmail.com</a><br>
> <mailto:<a href="mailto:jorge.arevalo@gmail.com">jorge.arevalo@gmail.com</a>>><br>
><br>
><br>
><br>
><br>
><br>
> Hi,<br>
><br>
><br>
><br>
> Thanks for your response Kevin. I can provide some<br>
> data:<br>
><br>
><br>
><br>
> - Screenshot (over geoserver) of the<br>
> "water" multipolygon. You can<br>
><br>
> see the shape of Spain and the Balearic Islands, and<br>
> a hole on the<br>
><br>
> left (Portugal). The multipolygon is open, isn't<br>
> it? --><br>
><br>
> <a href="http://www.nebulared.com/tmp_geo/water_multipolygon.jpg" target="_blank">http://www.nebulared.com/tmp_geo/water_multipolygon.jpg</a><br>
><br>
><br>
><br>
> - Zoom over Menorca (one of the Balearic Islands). As<br>
> you can see,<br>
><br>
> the point tested is inside the "airports"<br>
> multipolygon, but<br>
><br>
> ST_Within and ST_Contains returns that the point is<br>
> inside both,<br>
><br>
> "water" and "airports"<br>
> multipolygons --><br>
><br>
> <a href="http://www.nebulared.com/tmp_geo/zoom_in_water_multipolygon.jpg" target="_blank">http://www.nebulared.com/tmp_geo/zoom_in_water_multipolygon.jpg</a><br>
><br>
><br>
><br>
> - The "ST_AsText" data for the<br>
> "airport" multipolygon --><br>
><br>
> <a href="http://www.nebulared.com/tmp_geo/airports_multipolygon.txt" target="_blank">http://www.nebulared.com/tmp_geo/airports_multipolygon.txt</a><br>
><br>
><br>
><br>
> - The "ST_AsText" data for the<br>
> "water" multipolygon (rar file, 2.5<br>
><br>
> MB of plain text...) --><br>
><br>
> <a href="http://www.nebulared.com/tmp_geo/water_multipolygon.rar" target="_blank">http://www.nebulared.com/tmp_geo/water_multipolygon.rar</a><br>
><br>
><br>
><br>
> So, how can I check the validity of my multipolygons?<br>
> Do someone<br>
><br>
> could help me with this?<br>
><br>
><br>
><br>
> Now, the query. My context is the next:<br>
><br>
> - I have a table with polygons that I want to check<br>
> (to say if they<br>
><br>
> are in an airport, in a forest, in water, etc). We<br>
> can call this<br>
><br>
> table T1. I use the centroid of the polygons to<br>
> check, not the<br>
><br>
> entire polygon.<br>
><br>
> - In other different table, that we'll call T2, I<br>
> have all the<br>
><br>
> multipolygons ("water",<br>
> "airports", etc). The name of the field that<br>
><br>
> says if the multipolygon is "water" or<br>
> "airport" or anything is<br>
><br>
> "clutter"<br>
><br>
><br>
><br>
> With this code, I pretend to loop over T1, checking<br>
> if the centroid<br>
><br>
> of its polygons are inside of one (and ONLY one) of<br>
> the<br>
><br>
> multipolygons of T2. (Is part of a procedure)<br>
><br>
><br>
><br>
> (...)<br>
><br>
> BEGIN<br>
><br>
> FOR s IN SELECT * FROM T1 LOOP<br>
><br>
> select clutter from T2 where<br>
><br>
> ST_Within(ST_Centroid(T1.wkb_geometry),<br>
> T2.wkb_geometry))<br>
><br>
> (...)<br>
><br>
> END LOOP;<br>
><br>
> RETURN;<br>
><br>
> END;<br>
><br>
><br>
><br>
> Most of the times, the select inside the loop,<br>
> returns more than one<br>
><br>
> result ("water" and other one, normally).<br>
> That's my problem<br>
><br>
><br>
><br>
> I have another version of the query, with Java.<br>
> Again, I loop over<br>
><br>
> the table T1, and check the centroid of its polygons<br>
> with all the<br>
><br>
> multipolygons (water, airports...) that are in T2.<br>
> Then, my query,<br>
><br>
> inside the loop, is:<br>
><br>
><br>
><br>
> SELECT clutter FROM T2 WHERE ST_Within(?,<br>
> T2.wkb_geometry)<br>
><br>
><br>
><br>
> The '?' is replaced with the centroid of the<br>
> polygon in T1, of<br>
><br>
> course. Basically, is the same idea of the previous<br>
> SQL code, but<br>
><br>
> with Java.<br>
><br>
><br>
><br>
> Apart from this, other problem is that ST_Within<br>
> takes a long time.<br>
><br>
> 1 sec per query. And I have like 2 millions of<br>
> polygons (queries) to<br>
><br>
> check...<br>
><br>
><br>
><br>
> My multipolygons' table (T2) has only 29 rows<br>
> (small table with<br>
><br>
> large geometries), and I found this<br>
><br>
> <a href="http://postgis.refractions.net/documentation/manual-1.3/ch05.html" target="_blank">http://postgis.refractions.net/documentation/manual-1.3/ch05.html</a>.<br>
><br>
> Seems to be good for me. But using "SET<br>
> enable_seqscan TO off", the<br>
><br>
> queries take the same time (~ 1 sec). Maybe the other<br>
> approach<br>
><br>
> (create an additional column that "caches"<br>
> the bbox, and matching<br>
><br>
> against this) could help me. Any ideas on how to<br>
> create this bbox<br>
><br>
> with my huge multipolygons?<br>
><br>
><br>
><br>
><br>
><br>
> Thanks in advance<br>
><br>
><br>
><br>
> Regards<br>
><br>
> Jorge<br>
><br>
><br>
><br>
><br>
><br>
> On Wed, May 6, 2009 at 4:28 AM, Kevin Neufeld<br>
><br>
> <<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a><br>
> <mailto:<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</a>>><br>
> wrote:<br>
><br>
><br>
><br>
> Jorge Arévalo wrote:<br>
><br>
><br>
><br>
> ... If is useful, I tested the method<br>
> "ST_isvalid" with the<br>
><br>
> multipolygon and returns<br>
> "false". Maybe the multipolygon is<br>
><br>
> not closed? I loaded the data from a<br>
> shapefile. Is it<br>
><br>
> possible to create a<br>
> "non-valid" multipolygon? Does PostGIS<br>
><br>
> accept this?<br>
><br>
><br>
><br>
><br>
><br>
> Ah, yes. Most spatial predicates in PostGIS<br>
> assume the input<br>
><br>
> geometry is valid. Suppose you had defined a<br>
> POLYGON with a<br>
><br>
> hole or inner ring outside of the exterior<br>
> ring. What is the<br>
><br>
> area? Does the question even make sense?<br>
> PostGIS allows<br>
><br>
> invalid geometries in the database so users can<br>
> make full use of<br>
><br>
> the PostGIS toolset to do whatever they need<br>
> (ie. breakdown a<br>
><br>
> polygon to it's constituent linework and<br>
> rebuild it back up<br>
><br>
> again to a valid polygon)<br>
><br>
><br>
><br>
><br>
><br>
> Ok, being even more specific. I'm<br>
> working with data about<br>
><br>
> Spain. I have a HUGE multipolygon that<br>
> represents "water"<br>
><br>
> (this is, the coasts around Spain and its<br>
> islands). Then,<br>
><br>
> the "holes" inside this<br>
> multypolygon have the shape of<br>
><br>
> Spain, Balearic Islands and Canary<br>
> Islands. Of course, I<br>
><br>
> have more multipolygons that represent<br>
> "forests",<br>
><br>
> "airports", "cities",<br>
> etc, that fit into these holes.<br>
><br>
><br>
><br>
> Really, my problem is with some points<br>
> that belong to an<br>
><br>
> airport in an island. Using<br>
> "ST_Within" and "ST_Contains",<br>
><br>
> the result is that these points belong to<br>
> the multipolygon<br>
><br>
> "airport" and multipolygon<br>
> "water" at same time. Obviously,<br>
><br>
> the island (and its airport) is<br>
> surrounded by water, but the<br>
><br>
> airport's points shouldn't be<br>
> part of the multopolygon<br>
><br>
> "water". And, as I said, when I<br>
> apply "ST_isvalid" to the<br>
><br>
> multipolygon "water", returns<br>
> false. Maybe is not closed?<br>
><br>
><br>
><br>
><br>
><br>
> Yeah, as mentioned before, a point that is not<br>
> on the surface of<br>
><br>
> a (multi)polygon (whether in a hole or<br>
> completely outside) is<br>
><br>
> not considered within the (multi)polygon. It<br>
> does sound like<br>
><br>
> your ocean polygon has validity issues.<br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
> Oh, btw, what's the difference<br>
> between "ST_Within" and<br>
><br>
> "Within". Does<br>
> "ST_Within" use index instead of geometry? Am<br>
><br>
> I right?<br>
><br>
><br>
><br>
> No, not instead of. Both use the actual<br>
> geometry for testing<br>
><br>
> within. ST_Within will also use the index to<br>
> narrow down the<br>
><br>
> candidate list first.<br>
><br>
> As you can see, the definition of ST_Within is<br>
> just a simple SQL<br>
><br>
> wrapper that first invokes the index.<br>
><br>
> postgis=# select prosrc from pg_proc where<br>
> proname = 'st_within';<br>
><br>
> prosrc<br>
> ---------------------------------------<br>
><br>
> SELECT $1 && $2 AND _ST_Within($1,$2)<br>
><br>
> (1 row)<br>
><br>
><br>
><br>
> <a href="http://postgis.refractions.net/documentation/manual-svn/ST_Within.html" target="_blank">http://postgis.refractions.net/documentation/manual-svn/ST_Within.html</a><br>
><br>
><br>
><br>
> Cheers,<br>
><br>
> Kevin<br>
><br>
><br>
><br>
><br>
><br>
><br>
> _______________________________________________<br>
><br>
</div></div><div class="im">> postgis-users mailing list<br>
><br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
><br>
</div>> <mailto:<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a>><br>
<div class="im">><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
><br>
</div>> ------------------------------------------------------------------------<br>
<div class="im">><br>
><br>
><br>
> _______________________________________________<br>
><br>
> postgis-users mailing list<br>
><br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
> _______________________________________________<br>
><br>
> postgis-users mailing list<br>
><br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
><br>
><br>
><br>
</div>> -----Inline Attachment Follows-----<br>
<div><div></div><div class="h5">><br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
><br>
</div></div></blockquote></div><br>