Brian,
I think i've heard people talk about ways to get the most out of indexes
on the list earlier.
The gist index used in postgis uses the extents (bounding box) of the
geometries as the indexed value.
Geospatial functions involving the support libraries (like st_within, I
imagine) are more expencive (no surprise, given the math involved) than
vanilla where statements.
To get the most bang for the buck, cpu-wise, one has to try to use of
procedures, operators and filters in an optimized way.
Try reduce amount of geospatial processing that needs to be done.
There is a trick to help a little here: knowing that index is bounding
box and that the spatial operators makes heavy use of the indexes.
To med your query looks like what I call a spatial join condition.
General hints on performance can be found here:
http://postgis.refractions.net/docs/ch05.html
I'd like to see the execution plan of your query, along with this one:
select name
from gectable
where geom_pts2 && (select the_geom from just_ca where city = 'Berkeley' )
and st_within(geom_pts2, (select the_geom from just_ca
where city = 'Berkeley' ))
http://postgis.refractions.net/docs/ch05.html
Anyways, its a little black magic involved in query tuning, and
especially so with spatial queries.
Good luck,
Brian Hamlin skrev:
ok, no one bothered to point out my obvious mistake in the order
lon/lat in the Polygon query...
Aside from that, I just double checked the SRIDs, dropped and reloaded
the GIST indexes for both the POINT and MULTIPOLYGON tables, both in
4326, vacuumed and vacuumed again.
The basic question still stands - if I dont SetSRID(), I get a mixed
SRID error msg
with the SetSRID(), the Points in Polys takes 10 seconds to execute.
That seems like a long time.
Is this expected performance?
Mac OS 10.10, Powerbook 1ghz g4, decent 54gb internal drive
Why the mixed SRIDs error without the explicit calls?
SELECT gectable.name
FROM just_ca, gectable
WHERE
just_ca.city = 'Berkeley'
AND
st_within( SetSRID(gectable.geom_pts2,4326),
SetSRID( just_ca.the_geom,4326))
this takes 10 seconds on 110,000 points (and 12 polys in city Berkeley).
(I added a GIST index on geom_pts2 and the_geom, and the query took
the same amount of time as without indexes. just_ca.city =
'Berkeley' alone
takes just 4ms)
The following query gets a mixed SRID error
SELECT gectable.name
FROM just_ca, gectable
WHERE
just_ca.city = 'Berkeley'
AND
st_within( gectable.geom_pts2, just_ca.the_geom )
why??
_______________________________________________
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
