OK, next guess. It looks like your SQL might be pretty inefficient. For example, consider this SQL statement:<div><br></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">SELECT a, b FROM ll<br>
WHERE<br>ST_Within(<br>ST_Point(<br>ST_X(ST_Transform(the_geom, 4326)),<br>ST_Y(ST_Transform(the_geom, 4326))<br>),<br>ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007))<br>)</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; "><br>
</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">I think that says something like:</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">Look through the II table</span></div>
<div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">For each row, transform the_geom to 4326, and take the X</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">For each row, transform the_geom to 4326, and take the Y</span></div>
<div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">Make a point from that X,Y</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">Check if that point is within a box</span></div>
<div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; "><br></span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">I don't think that'll use an index, and I think it will do a bunch of transformation work for every row.</span></div>
<div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; "><br></span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; ">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:<br>
<br><div>SELECT</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>a,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>b </div><div>FROM</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ll</div>
<div>WHERE</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ST_Within(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>the_geom,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ST_Transform(</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>ST_MakeBox2D(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ST_Point(</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>-91.048,</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>45.956</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ST_Point(</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>-90.973,</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>46.007</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>),</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>2163</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>)</div><div><br></div><div><br></div><div>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.</div>
</span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; "><br></span></div><div><span class="Apple-style-span" style="border-collapse: collapse; font-family: arial, sans-serif; font-size: 13px; "><br>
</span></div><div><font class="Apple-style-span" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;"><br></span></font></div><div><font class="Apple-style-span" face="arial, sans-serif"><span class="Apple-style-span" style="border-collapse: collapse;"><br>
</span></font><br><div class="gmail_quote">On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor <span dir="ltr"><<a href="mailto:punk.kish@gmail.com">punk.kish@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
I have a table with ~ 13.25 million points.<br>
<br>
CREATE TABLE ll (<br>
gid serial NOT NULL,<br>
latitude double precision,<br>
longitude double precision,<br>
a integer,<br>
b integer,<br>
the_geom geometry,<br>
CONSTRAINT ll_pkey PRIMARY KEY (gid),<br>
CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2),<br>
CONSTRAINT enforce_geotype_the_geom CHECK (<br>
geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL<br>
),<br>
CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163)<br>
)<br>
WITH (<br>
OIDS=FALSE<br>
);<br>
<br>
I want to select the columns a,b for the rows that lie within a box made by points [-91.048, 45.956] and [-90.973, 46.007]. Here are my results --<br>
<br>
Query 1<br>
<br>
SELECT a, b FROM ll<br>
WHERE<br>
ST_Within(<br>
ST_Point(<br>
ST_X(ST_Transform(the_geom, 4326)),<br>
ST_Y(ST_Transform(the_geom, 4326))<br>
),<br>
ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007))<br>
)<br>
<br>
31 rows returned in 46125 ms<br>
<br>
Query 2<br>
<br>
SELECT a, b FROM ll<br>
WHERE<br>
ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND<br>
ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND<br>
ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND<br>
ST_Y(ST_Transform(the_geom, 4326)) <= 46.007<br>
<br>
31 rows returned in 25729 ms<br>
<br>
Query 3<br>
<br>
SELECT a, b FROM ll<br>
WHERE<br>
longitude >= -91.048 AND<br>
longitude <= -90.973 AND<br>
latitude >= 45.956 AND<br>
latitude <= 46.007<br>
<br>
31 rows returned in 4011 ms<br>
<br>
Query 4<br>
<br>
I also have the same data in a SQLite database with an R*Tree index on lat/lon. A query analogous to Query 3 returns fast enough to not even register a time... a few milliseconds; effectively 0 seconds.<br>
<br>
What gives?<br>
--<br>
Puneet Kishor<br>
<br>
<br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br></div>