« Return to Thread: I've got a silly question about performance

Re: I've got a silly question about performance

by easpengren :: Rate this Message:

OK, I think I've got that. When I imported these shape files into the database, an index was created. I added a Gist index to both to see what would happen. Will that cause a problem?

I do not understand Indices at all. I'll have to read up on them.

We'll see how these things come out. I would like to have something usable in the next day or two.

I'll likely end up creating a view of this query. Again, I'm still figuring this out. Speed is really quite important for me. I don't have the most powerful machine doing this stuff (more RAM will likely show up this week). What I'd like to do is make sure the software is working as efficiently as I can get it.

As I type this, I seem to have gotten a much quicker response from the server.

Wow.

Eric

Brent Wood-2 wrote:

--- easpengren <phat-ass@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
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users

« Return to Thread: I've got a silly question about performance