[postgis-users] speed of query

[Jean-François Gigand]

Hi,

Indeed: ST_Point, ST_MakeBox2D and ST_Transform are IMMUTABLE, so the
whole expression is computed once for all rows in this case. The only
function called every time is ST_Within, which normally makes use of
the spatial index.

I wonder if the '&&' operator (ST_Overlaps) would be faster or not...

JF


2011/3/1 Michael Smedberg <michael.smedberg at redfin.com>:
> 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.
>
>
>
>
> On Tue, Mar 1, 2011 at 11:40 AM, Puneet Kishor <punk.kish at gmail.com> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>