On 01/03/11 21:48, Michael Smedberg wrote:
OK, next guess. It looks like your SQL might be pretty inefficient.
For example, consider this SQL statement:
SELECT a, b FROM ll
WHERE
ST_Within(
ST_Point(
ST_X(ST_Transform(the_geom, 4326)),
ST_Y(ST_Transform(the_geom, 4326))
),
ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007))
)
I think that says something like:
Look through the II table
For each row, transform the_geom to 4326, and take the X
For each row, transform the_geom to 4326, and take the Y
Make a point from that X,Y
Check if that point is within a box
I don't think that'll use an index, and I think it will do a bunch of
transformation work for every row.
I think that instead of transforming every row in II to 4326, you'd
probably be better served by transforming your bounding box to 2163 one
time. I think the SQL would look something like this:
SELECT
a,
b
FROM
ll
WHERE
ST_Within(
the_geom,
ST_Transform(
ST_MakeBox2D(
ST_Point(
-91.048,
45.956
),
ST_Point(
-90.973,
46.007
)
),
2163
)
)
In any case, you should probably try looking at the output of EXPLAIN or
EXPLAIN ANALYZE to understand whether your index is being used, etc.
Hi Michael,
Yes indeed - I think you summarised this brilliantly :) If your
geometries are stored as SRID 2163, then the index bounding boxes will
also be stored in SRID 2163 - hence the index can only be used for
queries involving other SRID 2163 bounding boxes. Otherwise PostgreSQL
assumes it has to convert your entire geometry column to SRID 4326 first
in order to calculate the intersection, which involves scanning the
entire table and converting all the geometries on the fly...
ATB,
Mark.
--
Mark Cave-Ayland - Senior Technical Architect
PostgreSQL - PostGIS
Sirius Corporation plc - control through freedom
http://www.siriusit.co.uk
t: +44 870 608 0063
Sirius Labs: http://www.siriusit.co.uk/labs
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
