[postgis-users] Joining two GRID tables, slow query.

[adam at spatialsystems.org]

I only have two channel numbers and way more dates than channels.  So
I'm guessing my index is fine.


I'm running PostGIS 1.3.3; so I still can't use the geohash as the key?

I figure I'd create a new column called "grid_geohash" as
character_varying(255) and store the grid value there.  Is this not
possible in 1.3.3 or am I missing something?


Thanks,
Adam

>-------- Original Message --------
>Subject: Re: [postgis-users] Joining two GRID tables, slow query.
>From: Paul Ramsey <pramsey at cleverelephant.ca>
>Date: Thu, July 09, 2009 4:24 pm
>To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
>
>Two things.
>
>First, if you have a lot of distinct channels, you'll find multikey
>btree on channel,date is better than date,channel.
>Second, your join key is SnapToGrid() which is to say you're joining
>on an unindexed value. Your best bet might be to pre-calculate the
>grid value into a column, if you have 1.4 you could use the geohash of
>the grid as the key, and then index that column. That will make your
>self-join much faster.
>
>P.
>
>
>On Thu, Jul 9, 2009 at 2:17 PM, <adam at spatialsystems.org> wrote:
>> I'm have a table with 12,604,700 radio frequency measurements in a
>> PostGIS database.  Each measurement has with it the date it was taken.
>> I want to compare bin-by-bin how the signal has changed before and after
>> 2009/02/01.
>>
>> The query below is taking a long time and I want to make sure I'm doing
>> it right and there isn't a faster way to do it.
>>
>>
>> Does my SQL have a fundamental flaw or can it be made faster?
>>
>>
>> On the table called "scanner" I have index on:
>>
>> 'channel' type btree
>> 'date,channel' type btree
>> 'geometry' type gist
>>
>> SELECT
>>  X(pre_drive_test_data.grid) AS pre_x,
>>  Y(pre_drive_test_data.grid) AS pre_y,
>>  max(pre_drive_test_data.peak_rx::double precision ) as
>> pre_peak_rx_power,
>>  count(pre_drive_test_data.peak_rx) AS pre_count,
>>  X(post_drive_test_data.grid) AS post_x,
>>  Y(post_drive_test_data.grid) AS post_y,
>>  max(post_drive_test_data.peak_rx::double precision ) as
>> post_peak_rx_power,
>>  count(post_drive_test_data.peak_rx) AS post_count
>> FROM
>> (
>>  SELECT
>>    SnapToGrid(scanner.geometry, 0.001213) AS grid,
>>    scanner.peak_ec as peak_ec
>>  FROM
>>    scanner
>>  WHERE
>>    scanner.channel = 700
>>    AND date < '2009-02-01'
>> ) AS pre_drive_test_data,
>> (
>>  SELECT
>>    SnapToGrid(scanner.geometry, 0.001213) AS grid,
>>    scanner.peak_ec as peak_ec
>>  FROM
>>    scanner
>>  WHERE
>>    scanner.channel = 700
>>    AND date > '2009-02-01'
>> ) AS post_drive_test_data
>> WHERE
>>  pre_drive_test_data.grid = post_drive_test_data.grid
>> GROUP BY
>>  pre_x,
>>  pre_y,
>>  post_x,
>>  post_y
>>
>>
>> Thanks,
>> Adam
>>
>>
>> _______________________________________________
>> 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