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

Chris Hermansen chris.hermansen at timberline.ca
Thu Jan 17 09:59:33 PST 2008


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: postgis-users-bounces at postgis.refractions.net on behalf of
> easpengren
> Sent: Thu 1/17/2008 12:02 AM
> To: postgis-users at postgis.refractions.net
> 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 <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
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > 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
> 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
>   


-- 
Regards,

Chris Hermansen · mailto:clh at timberline.ca
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.




More information about the postgis-users mailing list