[postgis-users] speed of query

[Ben Madin]

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 at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users