Puneet, I'm thinking you are doing a lot of extra calculations here by constantly transforming the points.
Why did you turn a geometry into two points and then make the geometry again... this would invalidate the use of an index on the same column? (Neither did you define a SRID for your box in the first query) Have a look at : file:///usr/local/pgsql/share/doc/contrib/postgis-1.5/postgis.html#ST_MakeBox2D I think the query (slightly modified here) --Return all features that fall reside or partly reside in a US national atlas coordinate bounding box --It is assumed here that the geometries are stored with SRID = 2163 (US National atlas equal area) SELECT feature_id, feature_name, the_geom FROM features WHERE the_geom && st_transform(ST_SetSRID(ST_MakeBox2D(ST_Point(-91.048, 45.956), ST_Point(-90.973, 46.007)),4326),2163) is what you are after (obviously you will have to transform the lat long points (or the box. cheers Ben On 02/03/2011, at 3:40 AM, Puneet Kishor wrote: > I have a table with ~ 13.25 million points. > > CREATE TABLE ll ( > gid serial NOT NULL, > latitude double precision, > longitude double precision, > a integer, > b integer, > the_geom geometry, > CONSTRAINT ll_pkey PRIMARY KEY (gid), > CONSTRAINT enforce_dims_the_geom CHECK (st_ndims(the_geom) = 2), > CONSTRAINT enforce_geotype_the_geom CHECK ( > geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL > ), > CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2163) > ) > WITH ( > OIDS=FALSE > ); > > 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 -- > > Query 1 > > 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)) > ) > > 31 rows returned in 46125 ms > > Query 2 > > SELECT a, b FROM ll > WHERE > ST_X(ST_Transform(the_geom, 4326)) >= -91.048 AND > ST_X(ST_Transform(the_geom, 4326)) <= -90.973 AND > ST_Y(ST_Transform(the_geom, 4326)) >= 45.956 AND > ST_Y(ST_Transform(the_geom, 4326)) <= 46.007 > > 31 rows returned in 25729 ms > > Query 3 > > SELECT a, b FROM ll > WHERE > longitude >= -91.048 AND > longitude <= -90.973 AND > latitude >= 45.956 AND > latitude <= 46.007 > > 31 rows returned in 4011 ms > > Query 4 > > 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. > > What gives? > -- > Puneet Kishor > > > > _______________________________________________ > postgis-users mailing list > postgis-users@postgis.refractions.net > http://postgis.refractions.net/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
