[postgis-users] ST_Intersects
pcreso at pcreso.com
pcreso at pcreso.com
Tue Aug 5 11:47:01 PDT 2008
Hi Danny,
As a maritime PostGIS user I have had issues with large & complex polygons/multipoygons in PostGIS (& GEOS), but not to the extent you seem to.
I'm not an expert at this, but here are some things you might try.
It looks like it is not using the index on sites, perhaps because postgres is applying a conversion to text for your constant & the attribute. Can you try modifying your SQL to
.... where a.sitecode = 'xxxx'::<same spec as the sitecode column>
(eg: where a.sitecode = 'xxxx'::varchar(10) if sitecode is varchar(10) )
To see if that enables the index to work better.
Alternatively, perhaps you could create a new index on a (sitecode::text)
Though I'm not sure if that's possible....
Where I have large complex polygons, I have found tiling them really enables the spatial index to become useful. I generate a grid in postgis, build a table of the intersections of the grid & base layer & query against that.
Essentially an index helps with long (deep) tables, ie, lots of small records, much more than fat (broad) tables, ie, few large records. Tiling geometries converts fat to deep, making things much faster.
Cheers,
Brent Wood
--- On Tue, 8/5/08, danny <whatevar89 at gmail.com> wrote:
> From: danny <whatevar89 at gmail.com>
> Subject: Re: [postgis-users] ST_Intersects
> To: "PostGIS Users Discussion" <postgis-users at postgis.refractions.net>
> Date: Tuesday, August 5, 2008, 11:14 PM
> Guido, it's a bufferzone around the contour of europe.
>
> On Tue, Aug 5, 2008 at 1:02 PM, Guido Lemoine
> <guido.lemoine at jrc.it> wrote:
>
> > Erik,
> >
> > I don't know why european_waters would be a single
> complex polygon?
> > Do you mean it is a MULTIPOLYGON in Postgis? If so, it
> is easy to
> > break it apart in single polygons. If it includes
> rivers, I can't imagine
> > why it
> > is a polygon in the first place. I would expect a
> LINESTRING (or
> > MULTILINESTRING in your case).
> >
> > This explains, of course, why performance is not much
> improved. The &&
> > operation does not really affect that.
> >
> > GL
> >
> >
> >
> > danny wrote:
> >
> >> 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<mailto:
> >> 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
> >>
> <mailto: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
> >>
> <mailto:postgis-users at postgis.refractions.net>
> >>
> http://postgis.refractions.net/mailman/listinfo/postgis-users
> >>
> >>
> >>
> ------------------------------------------------------------------------
> >>
> >> _______________________________________________
> >> 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
> >
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list