[postgis-users] ST_Intersects

Tue Aug 5 04:09:43 PDT 2008

Have you tried ST_DWithin instead of intersects.  On some occassions it
does perform a little better than ST_Intersects.  I think in 1.3.4 Paul
Ramsey had put in a distance short-cut optimization for ST_DWithin that
makes it perform even better but I haven't had a chance to test that out
yet with my set.  

Also you really should put the b.site_code check before your
ST_Intersects clause or ST_Intersects in your inner join (unless of
cause you are going to get rid of that site code check anyway).  The
costing metrics on the postgis functions are not set yet for 8.3 which
sometimes makes them get applied before regular old stuff.

So would be something like 
SELECT a.gid, b.the_geom
FROM sites a, europe_waters b
WHERE a.sitecode = 'xxxx' AND ST_DWithin(a.the_geom, b.the_geom, 0.001)

The other problem with your code is that your europe_waters is huge
which makes the index not all that useful.

You might want to dice that up a bit using intersection clipping.
You'll get more rows, but overall I think the performance would be
better.

Hope that helps,
Regina

________________________________

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
danny
Sent: Tuesday, August 05, 2008 6:40 AM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] ST_Intersects

Thanks Guido!

The sample you gave performed slightly better: 304 seconds. I'll still
have to wait a few months to let it run on all my data though.  
Yes, sitecode has a "normal" index, not a spatial one. Both the_geom's
have spatial ones. 
The european_waters polygon is one single complex polygon, scale
1/100000.
What's annoying is that when I do an intersect in Arc View it's almost
instantaneous. Of course I don't get exaclty what I wan't but with some
tweaking I can maybe get closer. That would be a great disappointment
though. I was really looking forward to using ST_Within intensively! 

Thanks Jean David, no performance gain though by using it in the WHERE
clause, where I intended to use it in the first place. I put it in the
Select for testing purposes. 

explain select  sitecode from sites a,  europe_waters b 
where st_intersects(a.the_geom,b.the_geom) and a.sitecode = 'xxxx';

"Nested Loop  (cost=0.00..461.71 rows=1 width=10)"
"  Join Filter: _st_intersects(a.the_geom, b.the_geom)"
"  ->  Seq Scan on sites a  (cost=0.00..453.43 rows=1 width=35764)"
"        Filter: ((sitecode)::text = 'xxxx'::text)"
"  ->  Index Scan using idx_europe_waters on europe_waters b
(cost=0.00..8.27 rows=1 width=32)"
"        Index Cond: (a.the_geom && b.the_geom)"
"        Filter: (a.the_geom && b.the_geom)"

On Tue, Aug 5, 2008 at 10:53 AM, Guido Lemoine <guido.lemoine at jrc.it>
wrote:

	Erik

	Try this, and see if it is any faster:

	select  st_intersects(a.the_geom,b.the_geom) from sites a,
europe_waters b where a.the_geom && b.the_geom and
	a.sitecode = 'xxxx';

	I guess you mean that sitecode is indexed (not spatially
indexed), because it seems to be a varchar.
	Both a and b should have a spatial index on the_geom, but I
reckon that is already the case. Also,
	b (europe_layer) should not be a single polygon, but rather a
set of polygons.

	This is lesson 1 in the PostGIS tutorial, more or less. In
return for my 2 minutes effort, you are
	obliged to report the new performance report, so that future
users will benefit.
	After all, I just saved you half a year...

	GL

	danny wrote:

		Hello,

		I'm wondering if it's normal to have a 420 second
response time for the following query.
		A spatial index has been set on the spatial field
(the_geom) and other important fields (like sitecode). I've generously
tweaked the memory options for postgresql.
		With such a response time I would have to let my query
run for half a year before getting the answer I'm interested in! :)

		Anybody know how I can boost up the process or is it
doomed to always be so slow? The europe_layer is indeed a complex
polygon....

		select  st_intersects(a.the_geom,b.the_geom) from sites
a,  europe_waters b where a.sitecode = 'xxxx';

		"Nested Loop  (cost=0.00..496.18 rows=1310 width=35786)"
		"  ->  Seq Scan on sites a  (cost=0.00..453.43 rows=1
width=35754)"
		"        Filter: ((sitecode)::text = 'xxxx'::text)"
		"  ->  Seq Scan on europe_waters b  (cost=0.00..23.10
rows=1310 width=32)"

		My true objective would be to join thses two tables
through an st_intersects but for the time it is unconceivable. 
		Many thanks,

		Erik

------------------------------------------------------------------------

		_______________________________________________
		postgis-users mailing list
		postgis-users at postgis.refractions.net

http://postgis.refractions.net/mailman/listinfo/postgis-users

	-- 
	----------------------------------------------------------------
	Guido Lemoine
	Joint Research Centre, European Commission
	Institute for the Protection and Security of the Citizen (IPSC)
	Support to External Security
	Via E. Fermi, 2749 TP 267 Ispra 21027 (VA), Italy
	Tel. +39 0332 786239 (direct line) Fax. +39 0332 785154
	WWW: http://ses.jrc.it
	----------------------------------------------------------------
	Disclaimer:
	Views expressed are those of the individual and do not represent
the views of the European Commission

	_______________________________________________
	postgis-users mailing list
	postgis-users at postgis.refractions.net
	http://postgis.refractions.net/mailman/listinfo/postgis-users

-----------------------------------------
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080805/5e4b477d/attachment.html>