If you want a list of parcels in each precinct, why are you first creating points? That's just going to add extra - unnecessary - geoprocessing.
Try st_intersects() and st_intersection(). If you are concerned your parcels overlap more than one precinct, get the area() of each intersection(), get the max() of that, and group by your other variables. Gregory Williamson wrote: > > The GIST index is the one you want / need for spatial operations. Make > sure that you ANALYZE <tablename>; after creating the index or > changing the data much (rule of thumb is maybe 10% but analyze is a > fast and fairly low impact operation). > > The ST_ functions usually (always?) invoke the && operator (which is > the spatial operator which will use the GIST indexes); they are > wrapper for the underlying functions. > > Posting your information [type of hardware, OS, postgreSQL and postGIS > versions, table structure and indexes, tables sizes, the query itself > and the results of EXPLAIN ANALYZE <query>] to the PostgreSQL > performance mail list might be worthwhile if this list doesn't help > enough. > > Greg Williamson > Senior DBA > Globexplorer LLC, a company owned by DigitalGlobe > > > -----Original Message----- > From: [EMAIL PROTECTED] on behalf of > easpengren > Sent: Thu 1/17/2008 12:02 AM > To: [email protected] > Subject: Re: [postgis-users] I've got a silly question about performance > > > 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 <[EMAIL PROTECTED]> 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 > > [email protected] > > http://postgis.refractions.net/mailman/listinfo/postgis-users > > > > > > -- > View this message in context: > http://www.nabble.com/I%27ve-got-a-silly-question-about-performance-tp14910258p14913339.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > > ------------------------------------------------------------------------ > > _______________________________________________ > postgis-users mailing list > [email protected] > http://postgis.refractions.net/mailman/listinfo/postgis-users > -- Regards, Chris Hermansen · mailto:[EMAIL PROTECTED] tel:+1.604.714.2878 · fax:+1.604.733.0631 Timberline Natural Resource Group · http://www.timberline.ca 401 · 958 West 8th Avenue · Vancouver BC · Canada · V5Z 1E5 C'est ma façon de parler. _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
