Great. This is all great advice. I hadn't thought of the area(), st_intersection() trick.
Some parcels do cross over into more than one precinct. Eric Josh Livni-3 wrote: > > Well, and again assuming he doesn't mind getting the list of parcels > whose centroids intersect each district rather than the list of parcels > that intersect each district, checking for point-polygon intersection is > going to be faster than checking for polygon-polygon intersection. So > for speed purposes it's probably a good way to go. > > For a first pass, after ensuring there is a gist index on the centroid > (or in this case point-on-surface - tho I'm not sure why), I would > expect the following to be faster than the equivalent query w/the full > polygons rather than the points: > ... > AND election.the_geom && ST_pointonsurface(parcel2.wkb_geometry) > AND > ST_distance(election.the_geom,ST_pointonsurface(parcel2.wkb_geometry)) = 0 > ... > And if you really needed speed, adding a new geometry field with the > points precalculated and using that in the second line would make things > even faster. > > -Josh > > > > Chris Hermansen wrote: >> 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 >>> >>> >> >> >> > _______________________________________________ > 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-tp14910258p14957787.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
