<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7652.24">
<TITLE>RE: [postgis-users] Large geometry issue</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Will do -- thanks for the suggestion.<BR>
<BR>
Greg W.<BR>
<BR>
<BR>
-----Original Message-----<BR>
From: postgis-users-bounces@postgis.refractions.net on behalf of Mark Leslie<BR>
Sent: Fri 7/6/2007 2:15 PM<BR>
To: PostGIS Users Discussion<BR>
Subject: Re: [postgis-users] Large geometry issue<BR>
<BR>
Greg,<BR>
I've noticed that you're using a Geos release candidate. I've tried<BR>
this query against PostGIS 1.2.1 with the official Geos 3.0.0 and was<BR>
given the 241 results for my trouble. Are you able to upgrade the Geos<BR>
on that machine and give it a try?<BR>
Mark<BR>
<BR>
Gregory Williamson wrote:<BR>
> I think the zipped file I first sent should have all of that data ... this was a test against the original full tables but I have reproduced it against the smaller sample, which has all of the small tiles with the bounding box of the large geometry.<BR>
><BR>
> Using the intersection in JUMP seems to come up with a number very similar to using the distance(x,y) = 0 test; a rough count by hand seems to believe the 241 count.<BR>
><BR>
> I can resend the data if need be; let me know if there is any other info I can provide ... maybe we have a busted build ? Both servers are postgres 8.2.4 on slightly different flavors of linux.<BR>
><BR>
> Thanks!<BR>
><BR>
> Greg W.<BR>
><BR>
> -----Original Message-----<BR>
> From: postgis-users-bounces@postgis.refractions.net on behalf of Paul Ramsey<BR>
> Sent: Thu 7/5/2007 10:29 PM<BR>
> To: PostGIS Users Discussion<BR>
> Cc: Mark Leslie<BR>
> Subject: Re: [postgis-users] Large geometry issue<BR>
> <BR>
> That is indeed concerning, can you send us the big shape and a few of <BR>
> the small shapes that 2.2.3 is turning up as containments and 3.0.0 <BR>
> is not?<BR>
><BR>
> P<BR>
><BR>
> On 5-Jul-07, at 6:11 PM, Gregory Williamson wrote:<BR>
><BR>
>> Having has a little time to look at this in detail I can see why <BR>
>> this is ugly ... a very long sinuous shape vs a ton of small <BR>
>> rectangles, so the bounding box does get a lot of candidates which <BR>
>> then have to be winnowed.<BR>
>><BR>
>> I do find myself wondering at this result:<BR>
>><BR>
>> POSTGIS="1.2.1" GEOS="3.0.0rc4-CAPI-1.3.3" USE_STATS<BR>
>> select count(*) from wsc_candidates w, oli_req x WHERE w.geometry <BR>
>> && x.bbox AND contains(x.oli_req_geom,w.geometry) AND x.oli_req_id <BR>
>> = 114672;<BR>
>> count<BR>
>> -------<BR>
>> 0<BR>
>> (1 row)<BR>
>><BR>
>> Time: 4347.776 ms<BR>
>><BR>
>> versus<BR>
>> POSTGIS="1.2.1" GEOS="2.2.3-CAPI-1.1.1" PROJ="Rel. 4.4.9, 29 Oct <BR>
>> 2004" USE_STATS<BR>
>> # select count(*) from wsc_candidates w, oli_req x WHERE w.geometry <BR>
>> && x.bbox AND contains(x.oli_req_geom,w.geometry) AND x.oli_req_id <BR>
>> = 114672;<BR>
>> count<BR>
>> -------<BR>
>> 241<BR>
>> (1 row)<BR>
>><BR>
>> Time: 15956.425 ms<BR>
>><BR>
>> Not the time so much -- the box is a bit older -- but the disparity <BR>
>> in rows. 241 seems more likely from eyeballing the data in JUMP ... <BR>
>> or have I overlooked something obvious ?<BR>
>><BR>
>> Thanks,<BR>
>><BR>
>> Greg W.<BR>
>><BR>
>><BR>
>> -----Original Message-----<BR>
>> From: postgis-users-bounces@postgis.refractions.net on behalf of <BR>
>> Martin Davis<BR>
>> Sent: Thu 7/5/2007 5:32 PM<BR>
>> To: PostGIS Users Discussion<BR>
>> Subject: Re: [postgis-users] Large geometry issue<BR>
>><BR>
>> No problem. Actually it's me being blind - I just saw the attachment<BR>
>> with the test data.<BR>
>><BR>
>> 2530 vertices isn't a very big polygon. So this isn't a problem with<BR>
>> lots of holes or shells, it simply reveals that we're not <BR>
>> extracting the<BR>
>> maximum possible performance from a querying involving comparing many<BR>
>> small geoms against a single large one. Stay tuned - hopefully we'll<BR>
>> have a better story for this shortly.<BR>
>><BR>
>> Martin<BR>
>><BR>
>> Gregory Williamson wrote:<BR>
>>> Martin,<BR>
>>><BR>
>>> Sorry for the lack of coherence -- 2530 vertices in this test <BR>
>> polygon<BR>
>>> ... it should be in the original posting I sent, an attached zip <BR>
>> file<BR>
>>> with two tables; this is the single entry in the oli_req table. The<BR>
>>> other table has some 13000 rows of polygons which are either<BR>
>>> completely within or overlapping with the large poly.<BR>
>>><BR>
>>> If you can't get at that let me know and I'll send you a copy ...<BR>
>>><BR>
>>> Greg W.<BR>
>>><BR>
>>><BR>
>>> -----Original Message-----<BR>
>>> From: postgis-users-bounces@postgis.refractions.net on behalf of<BR>
>>> Martin Davis<BR>
>>> Sent: Thu 7/5/2007 5:09 PM<BR>
>>> To: PostGIS Users Discussion<BR>
>>> Subject: Re: [postgis-users] Large geometry issue<BR>
>>><BR>
>>> Greg,<BR>
>>><BR>
>>> Can you elaborate on your statement "2530 in a sinlge polygon"? <BR>
>> Do you<BR>
>>> mean holes or subPolygons in a MultiPolygon?<BR>
>>><BR>
>>> Either way, currently JTS is not optimized for working with very <BR>
>> large<BR>
>>> numbers of holes and sub-polygons. As Paul says, we are currently<BR>
>>> working on improving this. Hopefully we can at least match the<BR>
>>> performance of the Other DMBS(es) for this situation!<BR>
>>><BR>
>>> Would it be possible for me to obtain this geometry for testing?<BR>
>>><BR>
>>> Martin<BR>
>>><BR>
>>><BR>
>>><BR>
>>> Gregory Williamson wrote:<BR>
>>>> Dear peoples,<BR>
>>>><BR>
>>>> I have a problem with a query that uses an absurdly large geometry<BR>
>>>> (2530 in a single polygon). This is srid -1 (part of a large <BR>
>> test of<BR>
>>>> postgres vs some other database product). Everything has been <BR>
>> vacuumed<BR>
>>>> and analyzed.<BR>
>>>><BR>
>>>> The initial search to find candidates in a target table is <BR>
>> quite fast:<BR>
>>>> catest=# select count(*) from wtm_sub_cell w, order_line_item x <BR>
>> WHERE<BR>
>>>> x.bbox && w.geometry AND x.id_as_int = 114672;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 13168<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 9.472 ms<BR>
>>>><BR>
>>>> Trying to get the list narrowed to geometries that are completely<BR>
>>>> contained by the requested shape is slow:<BR>
>>>> catest=# select count(*) from wtm_sub_cell w, order_line_item x <BR>
>> WHERE<BR>
>>>> x.bbox && w.geometry AND distance(x.geometry,w.geometry) = 0 and<BR>
>>>> x.id_as_int = 114672;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 1112<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 69277.780 ms<BR>
>>>><BR>
>>>> So I have two questions:<BR>
>>>> a) anything better to use than "distance(x,y) = 0) ? I tried<BR>
>>>> st_within -- it is about the same speed but returns no polys, <BR>
>> which is<BR>
>>>> strange to me, but I also haven't looked at these in detail <BR>
>> yet. For<BR>
>>>> example:<BR>
>>>> catest=# select count(*) from wtm_sub_cell w, order_line_item x <BR>
>> WHERE<BR>
>>>> x.bbox && w.geometry AND st_within(x.geometry,w.geometry) and<BR>
>>>> x.id_as_int = 114672;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 0<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 1173.185 ms<BR>
>>>> (same results with st_within(w.geometry,x.geometry):<BR>
>>>> catest=# select count(*) from wtm_sub_cell w, order_line_item x <BR>
>> WHERE<BR>
>>>> x.bbox && w.geometry AND st_within(w.geometry,x.geometry) and<BR>
>>>> x.id_as_int = 114672;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 0<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>><BR>
>>>> b) anything I can do to speed things up ? I have tried <BR>
>> boosting work<BR>
>>>> mem to 16 megs (from 1) and it made no apparent difference.<BR>
>>>><BR>
>>>><BR>
>>>><BR>
>>>> I have a self contained test case that shows the same behavior <BR>
>> -- the<BR>
>>>> one large poly and all the candidates in another table. <BR>
>> Apologies for<BR>
>>>> the size; hopefully it's not been mangled in the transfers.<BR>
>>>><BR>
>>>> Explain analyze of the sample (the sequential is sensible since <BR>
>> there<BR>
>>>> is only one row in the table):<BR>
>>>> catest=# explain analyze select count(*) from wsc_candidates w,<BR>
>>>> oli_req x WHERE w.geometry && x.bbox AND<BR>
>>>> distance(w.geometry,x.oli_req_geom) > 0 AND x.oli_req_id = 114672;<BR>
>>>><BR>
>>>> QUERY PLAN<BR>
>>>><BR>
>>> <BR>
>> ----------------------------------------------------------------------<BR>
>> ----------------------------------------------------------------------<BR>
>> -----------<BR>
>>>> Aggregate (cost=20.28..20.29 rows=1 width=0) (actual<BR>
>>>> time=77232.858..77232.859 rows=1 loops=1)<BR>
>>>> -> Nested Loop (cost=0.00..9.30 rows=4389 width=0) (actual<BR>
>>>> time=6.389..77221.506 rows=12056 loops=1)<BR>
>>>> Join Filter: (distance(w.geometry, x.oli_req_geom) ><BR>
>>>> 0::double precision)<BR>
>>>> -> Seq Scan on oli_req x (cost=0.00..1.01 rows=1<BR>
>>>> width=40602) (actual time=0.007..0.009 rows=1 loops=1)<BR>
>>>> Filter: (oli_req_id = 114672)<BR>
>>>> -> Index Scan using wsc_c_spatial_ndx on <BR>
>> wsc_candidates w<BR>
>>>> (cost=0.00..8.27 rows=1 width=109) (actual time=0.022..25.991<BR>
>>>> rows=13168 loops=1)<BR>
>>>> Index Cond: (w.geometry && x.bbox)<BR>
>>>> Filter: (w.geometry && x.bbox)<BR>
>>>> Total runtime: 77232.901 ms<BR>
>>>> (9 rows)<BR>
>>>><BR>
>>>> Time: 77233.773 ms<BR>
>>>><BR>
>>>><BR>
>>>> And for the real thing:<BR>
>>>> catest=# explain analyze select count(*) from wtm_sub_cell w,<BR>
>>>> order_line_item x WHERE w.geometry && x.bbox AND<BR>
>>>> distance(w.geometry,x.geometry) = 0 AND x.id_as_int = 114672;<BR>
>>> <BR>
>>> <BR>
>> QUERY<BR>
>>>> PLAN<BR>
>>>><BR>
>>> <BR>
>> ----------------------------------------------------------------------<BR>
>> ----------------------------------------------------------------------<BR>
>>>> Aggregate (cost=141.83..141.84 rows=1 width=0) (actual<BR>
>>>> time=77457.587..77457.588 rows=1 loops=1)<BR>
>>>> -> Nested Loop (cost=5.99..141.83 rows=1 width=0) (actual<BR>
>>>> time=15.682..77456.541 rows=1112 loops=1)<BR>
>>>> Join Filter: (distance(w.geometry, x.geometry) = <BR>
>> 0::double<BR>
>>>> precision)<BR>
>>>> -> Index Scan using oli_id_ndx on order_line_item x<BR>
>>>> (cost=0.00..8.30 rows=1 width=383) (actual time=0.012..0.018 <BR>
>> rows=1<BR>
>>>> loops=1)<BR>
>>>> Index Cond: (id_as_int = 114672)<BR>
>>>> -> Bitmap Heap Scan on wtm_sub_cell w <BR>
>> (cost=5.99..132.97<BR>
>>>> rows=32 width=109) (actual time=2.988..21.796 rows=13168 loops=1)<BR>
>>>> Filter: (w.geometry && x.bbox)<BR>
>>>> -> Bitmap Index Scan on wsc_geom_idx1<BR>
>>>> (cost=0.00..5.98 rows=32 width=0) (actual time=2.828..2.828 <BR>
>> rows=13168<BR>
>>>> loops=1)<BR>
>>>> Index Cond: (w.geometry && x.bbox)<BR>
>>>> Total runtime: 77457.633 ms<BR>
>>>> (10 rows)<BR>
>>>><BR>
>>>> Time: 77458.458 ms<BR>
>>>><BR>
>>>><BR>
>>>> The tables involved by size:<BR>
>>>> catest=# select count(*) from wsc_candidates;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 13168<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 2.586 ms<BR>
>>>> catest=# select count(*) from oli_req;<BR>
>>>> count<BR>
>>>> -------<BR>
>>>> 1<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 0.193 ms<BR>
>>>> catest=# select count(*) from wtm_sub_cell;<BR>
>>>> count<BR>
>>>> ---------<BR>
>>>> 6399928<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 1776.508 ms<BR>
>>>> catest=# select count(*) from order_line_item;<BR>
>>>> count<BR>
>>>> --------<BR>
>>>> 395921<BR>
>>>> (1 row)<BR>
>>>><BR>
>>>> Time: 176.083 ms<BR>
>>>><BR>
>>>><BR>
>>>> Many thanks for your time and bandwidth!<BR>
>>>><BR>
>>>> Greg Williamson<BR>
>>>> Senior DBA<BR>
>>>> GlobeXplorer LLC, a DigitalGlobe company<BR>
>>>><BR>
>>>> Confidentiality Notice: This e-mail message, including any<BR>
>>>> attachments, is for the sole use of the intended recipient(s) <BR>
>> and may<BR>
>>>> contain confidential and privileged information and must be <BR>
>> protected<BR>
>>>> in accordance with those provisions. Any unauthorized review, use,<BR>
>>>> disclosure or distribution is prohibited. If you are not the <BR>
>> intended<BR>
>>>> recipient, please contact the sender by reply e-mail and <BR>
>> destroy all<BR>
>>>> copies of the original message.<BR>
>>>><BR>
>>>> (My corporate masters made me say this.)<BR>
>>>><BR>
>>>> <BR>
>> ----------------------------------------------------------------------<BR>
>> --<BR>
>>>> _______________________________________________<BR>
>>>> postgis-users mailing list<BR>
>>>> postgis-users@postgis.refractions.net<BR>
>>>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
>>>><BR>
>>> --<BR>
>>> Martin Davis<BR>
>>> Senior Technical Architect<BR>
>>> Refractions Research, Inc.<BR>
>>> (250) 383-3022<BR>
>>><BR>
>>> _______________________________________________<BR>
>>> postgis-users mailing list<BR>
>>> postgis-users@postgis.refractions.net<BR>
>>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
>>><BR>
>>> <BR>
>> ----------------------------------------------------------------------<BR>
>> --<BR>
>>> _______________________________________________<BR>
>>> postgis-users mailing list<BR>
>>> postgis-users@postgis.refractions.net<BR>
>>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
>>><BR>
>> --<BR>
>> Martin Davis<BR>
>> Senior Technical Architect<BR>
>> Refractions Research, Inc.<BR>
>> (250) 383-3022<BR>
>><BR>
>> _______________________________________________<BR>
>> postgis-users mailing list<BR>
>> postgis-users@postgis.refractions.net<BR>
>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
>><BR>
>><BR>
>> _______________________________________________<BR>
>> postgis-users mailing list<BR>
>> postgis-users@postgis.refractions.net<BR>
>> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
><BR>
><BR>
><BR>
><BR>
> ------------------------------------------------------------------------<BR>
><BR>
> _______________________________________________<BR>
> postgis-users mailing list<BR>
> postgis-users@postgis.refractions.net<BR>
> <A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
_______________________________________________<BR>
postgis-users mailing list<BR>
postgis-users@postgis.refractions.net<BR>
<A HREF="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR>
<BR>
</FONT>
</P>
</BODY>
</HTML>