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
