[postgis-users] I've got a silly question about performance

[Brent Wood]

--- easpengren <phat-ass at thinkheavyindustries.com> wrote:

> 
> I'm still getting the hang of some of the finer points of creating queries in
> PostGIS, as is probably obvious with my last post.
> 
> I've two tables, parcel2 that is a collection of parcels in a county and a
> table election, which is a table of voting precincts in the same county. I'd
> like to select all of the parcels in each precinct.
> 

That query looks OK, but if parcels can be split across precincts, then it
won't necessarily give the correct answer.
 
> I have this query:
> 
> select precinct, sit_st_num, sit_st_dir, sit_st_nam, sit_st_typ, city_code
> from election, parcel2 where ST_contains(election.the_geom,
> ST_pointonsurface(parcel2.wkb_geometry));
> 
> This gets the job done, but it's very slow. What can I do to speed this up?

See the PostGIS docs about creating spatial indices on the geometry columns in
your two tables.

http://postgis.refractions.net/docs/ch04.html#id2761842
http://postgis.refractions.net/docs/ch04.html#id2761985

If you have, or if you create them, then you need to modify your query to use
them:

.... where election.the_geom && parcel2.wkb_geometry and ST_contains ...

as described in:
http://postgis.refractions.net/docs/ch04.html#id2762121



Hope this helps...

  Brent Wood