Hi,<br><br>Thanks for your response Kevin. I can provide some data:<br><br>- Screenshot (over geoserver) of the "water" multipolygon. You can see the shape of Spain and the Balearic Islands, and a hole on the left (Portugal). The multipolygon is open, isn't it? --> <a href="http://www.nebulared.com/tmp_geo/water_multipolygon.jpg">http://www.nebulared.com/tmp_geo/water_multipolygon.jpg</a><br>
<br>- Zoom over Menorca (one of the Balearic Islands). As you can see, the point tested is inside the "airports" multipolygon, but ST_Within and ST_Contains returns that the point is inside both, "water" and "airports" multipolygons --> <br>
<a href="http://www.nebulared.com/tmp_geo/zoom_in_water_multipolygon.jpg">http://www.nebulared.com/tmp_geo/zoom_in_water_multipolygon.jpg</a><br><br>- The "ST_AsText" data for the "airport" multipolygon --> <br>
<a href="http://www.nebulared.com/tmp_geo/airports_multipolygon.txt">http://www.nebulared.com/tmp_geo/airports_multipolygon.txt</a><br><br>- The "ST_AsText" data for the "water" multipolygon (rar file, 2.5 MB of plain text...) --><br>
<a href="http://www.nebulared.com/tmp_geo/water_multipolygon.rar">http://www.nebulared.com/tmp_geo/water_multipolygon.rar</a><br><br>So, how can I check the validity of my multipolygons? Do someone could help me with this?<br>
<br>Now, the query. My context is the next:<br>- I have a table with polygons that I want to check (to say if they are in an airport, in a forest, in water, etc). We can call this table T1. I use the centroid of the polygons to check, not the entire polygon.<br>
- In other different table, that we'll call T2, I have all the multipolygons ("water", "airports", etc). The name of the field that says if the multipolygon is "water" or "airport" or anything is "clutter"<br>
<br>With this code, I pretend to loop over T1, checking if the centroid of its polygons are inside of one (and ONLY one) of the multipolygons of T2. (Is part of a procedure)<br><br>(...)<br>BEGIN<br> FOR s IN SELECT * FROM T1 LOOP<br>
select clutter from T2 where ST_Within(ST_Centroid(T1.wkb_geometry), T2.wkb_geometry))<br> (...)<br> END LOOP;<br> RETURN;<br>END;<br><br>Most of the times, the select inside the loop, returns more than one result ("water" and other one, normally). That's my problem<br>
<br>I have another version of the query, with Java. Again, I loop over the table T1, and check the centroid of its polygons with all the multipolygons (water, airports...) that are in T2. Then, my query, inside the loop, is:<br>
<br>SELECT clutter FROM T2 WHERE ST_Within(?, T2.wkb_geometry)<br><br>The '?' is replaced with the centroid of the polygon in T1, of course. Basically, is the same idea of the previous SQL code, but with Java.<br>
<br>Apart from this, other problem is that ST_Within takes a long time. 1 sec per query. And I have like 2 millions of polygons (queries) to check...<br><br>My multipolygons' table (T2) has only 29 rows (small table with large geometries), and I found this <a href="http://postgis.refractions.net/documentation/manual-1.3/ch05.html">http://postgis.refractions.net/documentation/manual-1.3/ch05.html</a>. Seems to be good for me. But using "SET enable_seqscan TO off", the queries take the same time (~ 1 sec). Maybe the other approach (create an
additional column that "caches" the bbox, and matching against
this) could help me. Any ideas on how to create this bbox with my huge multipolygons?<br><br>Thanks in advance<br><br>Regards<br>Jorge<br>
<br><br><div class="gmail_quote">On Wed, May 6, 2009 at 4:28 AM, Kevin Neufeld <span dir="ltr"><<a href="mailto:kneufeld@refractions.net">kneufeld@refractions.net</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;">
Jorge Arévalo wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
... If is useful, I tested the method "ST_isvalid" with the multipolygon and returns "false". Maybe the multipolygon is not closed? I loaded the data from a shapefile. Is it possible to create a "non-valid" multipolygon? Does PostGIS accept this?<br>
</blockquote>
<br>
Ah, yes. Most spatial predicates in PostGIS assume the input geometry is valid. Suppose you had defined a POLYGON with a hole or inner ring outside of the exterior ring. What is the area? Does the question even make sense? PostGIS allows invalid geometries in the database so users can make full use of the PostGIS toolset to do whatever they need (ie. breakdown a polygon to it's constituent linework and rebuild it back up again to a valid polygon)<div class="im">
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
Ok, being even more specific. I'm working with data about Spain. I have a HUGE multipolygon that represents "water" (this is, the coasts around Spain and its islands). Then, the "holes" inside this multypolygon have the shape of Spain, Balearic Islands and Canary Islands. Of course, I have more multipolygons that represent "forests", "airports", "cities", etc, that fit into these holes.<br>
<br>
Really, my problem is with some points that belong to an airport in an island. Using "ST_Within" and "ST_Contains", the result is that these points belong to the multipolygon "airport" and multipolygon "water" at same time. Obviously, the island (and its airport) is surrounded by water, but the airport's points shouldn't be part of the multopolygon "water". And, as I said, when I apply "ST_isvalid" to the multipolygon "water", returns false. Maybe is not closed?<br>
</blockquote>
<br></div>
Yeah, as mentioned before, a point that is not on the surface of a (multi)polygon (whether in a hole or completely outside) is not considered within the (multi)polygon. It does sound like your ocean polygon has validity issues.<div class="im">
<br>
<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>
Oh, btw, what's the difference between "ST_Within" and "Within". Does "ST_Within" use index instead of geometry? Am I right?<br>
</blockquote></div>
No, not instead of. Both use the actual geometry for testing within. ST_Within will also use the index to narrow down the candidate list first.<br>
As you can see, the definition of ST_Within is just a simple SQL wrapper that first invokes the index.<br>
postgis=# select prosrc from pg_proc where proname = 'st_within';<br>
prosrc ---------------------------------------<br>
SELECT $1 && $2 AND _ST_Within($1,$2)<br>
(1 row)<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>
Cheers,<br><font color="#888888">
Kevin</font><div><div></div><div class="h5"><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">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>
</div></div></blockquote></div><br>