[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Fast spatial join point to raster
From: David Haynes <haynesd2 () gmail ! com>
Date: 2021-04-18 20:22:46
Message-ID: CAM4C04ph9Ujj--FcRDZGHZv=T3PodwEBO+DHsEKJK=-SoctXnA () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
Ok thanks
I aligned my query to the functional indices and it is much faster.
inner join mn_dasymetric r on ST_intersects(st_envelope(r.rast),
ST_Transform(p.geom, 26915))
On Sun, Apr 18, 2021 at 1:21 PM Paul Ramsey <pramsey@cleverelephant.ca>
wrote:
> The indexing of rasters is (and remains a pain in the butt) (maybe
> something I should look at shortly) because the index is bound to geometry
> and almost always declraed functionally. You've built functional indexes,
>
> GIST (ST_Transform(ST_Envelope(rast),26915))
>
> But you aren't calling them with queries that use the SAME functional
> signature. The rule of functional indexes:
>
> * An index like this: CREATE INDEX foo_x ON (function(bar))
> * must be called like this: SELECT * FROM foo WHERE function(bar) = 'baz'
>
> Note that function(bar) appears in the CREATE and in the SELECT.
>
> P
>
> > On Apr 18, 2021, at 10:53 AM, David Haynes <haynesd2@gmail.com> wrote:
> >
> > Hello,
> >
> > I'm following up on this blog post to see if there is a fast way to join
> points to rasters.
> > http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html
> >
> > I'm attempting to do a dasymetric mapping process, which is assigning
> areal unit values to raster pixels. For this example, I have created two
> tables. Table 1, mn_smokers has are geom (x,y) coordinates and spatial
> indices on 4326 and 26915. The resulting raster will be stored in
> mn_dasymetric and it also has indices at 4326 and 26915. When running the
> explain query it seems that index isn't really being used.
> >
> >
> > CREATE INDEX mn_smokers_gist_4326 ON mn_smokers USING GIST (geom);
> > CREATE INDEX mn_smokers_gist_26915 ON mn_smokers USING GIST
> (ST_Transform(geom,26915));
> > CREATE INDEX mn_dasymetric_gist_4326 ON mn_dasymetric USING GIST
> (ST_Transform(ST_Envelope(rast),4326));
> > CREATE INDEX mn_dasymetric_gist_26915 ON mn_dasymetric USING GIST
> (ST_Transform(ST_Envelope(rast),26915));
> >
> > Type: Nested Loop (Inner); ; Cost: 6.22 - 1594516.11
> > Type: Limit; ; Cost: 5.11 - 6.22
> > Type: Nested Loop (Inner); ; Cost: 5.11 - 471.95
> > Type: Seq Scan; Rel: minnesota_counties ; Cost: 0.00 - 34.09
> > Type: Bitmap Heap Scan; Rel: mn_smokers ; Cost: 5.11 - 437.50
> > Type: Bitmap Index Scan; Rel: mn_smokers_gist_4326 ; Cost: 0.00 - 5.10
> > Type: CTE Scan; Rel: points ; Cost: 0.00 - 4.00
> > Type: Materialize; ; Cost: 0.00 - 59.57
> > Type: Seq Scan; Rel: mn_dasymetric ; Cost: 0.00 - 47.38
> >
> >
> > with points as
> > (
> > select sp_id as point_id, smoker as point_value, ms.geom
> > from mn_smokers ms
> > inner join minnesota_counties mc on ST_Intersects(ms.geom, mc.geom)
> > )
> > , point_raster_agg as
> > (
> > select geom, count(point_id) as pixel_point_value
> > from points
> > group by geom
> > )
> > , output_rast as
> > (
> > select r.rid, pixel_point_value, ST_SetValue(rast, ST_Transform(p.geom,
> 26915), pixel_point_value ) as setrast
> > ,rast, geom
> > from point_raster_agg p
> > inner join mn_dasymetric r on ST_intersects(r.rast, ST_Transform(p.geom,
> 26915))
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users@lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
<div dir="ltr">Ok thanks<div><br></div><div>I aligned my query to the functional \
indices and it is much faster.</div><div><br></div><div>inner join mn_dasymetric r on \
ST_intersects(st_envelope(r.rast), ST_Transform(p.geom, \
26915))<br></div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Sun, Apr 18, 2021 at 1:21 PM Paul Ramsey <<a \
href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> \
wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">The indexing of \
rasters is (and remains a pain in the butt) (maybe something I should look at \
shortly) because the index is bound to geometry and almost always declraed \
functionally. You've built functional indexes, <br> <br>
GIST (ST_Transform(ST_Envelope(rast),26915))<br>
<br>
But you aren't calling them with queries that use the SAME functional signature. \
The rule of functional indexes:<br> <br>
* An index like this: CREATE INDEX foo_x ON (function(bar))<br>
* must be called like this: SELECT * FROM foo WHERE function(bar) = 'baz'<br>
<br>
Note that function(bar) appears in the CREATE and in the SELECT.<br>
<br>
P<br>
<br>
> On Apr 18, 2021, at 10:53 AM, David Haynes <<a \
href="mailto:haynesd2@gmail.com" target="_blank">haynesd2@gmail.com</a>> \
wrote:<br> > <br>
> Hello,<br>
> <br>
> I'm following up on this blog post to see if there is a fast way to join \
points to rasters.<br> > <a \
href="http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html" \
rel="noreferrer" target="_blank">http://blog.cleverelephant.ca/2018/09/postgis-external-storage.html</a><br>
> <br>
> I'm attempting to do a dasymetric mapping process, which is assigning areal \
unit values to raster pixels. For this example, I have created two tables. Table 1, \
mn_smokers has are geom (x,y) coordinates and spatial indices on 4326 and 26915. The \
resulting raster will be stored in mn_dasymetric and it also has indices at 4326 and \
26915. When running the explain query it seems that index isn't really being \
used. <br> > <br>
> <br>
> CREATE INDEX mn_smokers_gist_4326 ON mn_smokers USING GIST (geom);<br>
> CREATE INDEX mn_smokers_gist_26915 ON mn_smokers USING GIST \
(ST_Transform(geom,26915));<br> > CREATE INDEX mn_dasymetric_gist_4326 ON \
mn_dasymetric USING GIST (ST_Transform(ST_Envelope(rast),4326));<br> > CREATE \
INDEX mn_dasymetric_gist_26915 ON mn_dasymetric USING GIST \
(ST_Transform(ST_Envelope(rast),26915));<br> > <br>
> Type: Nested Loop (Inner); ; Cost: 6.22 - 1594516.11<br>
> Type: Limit; ; Cost: 5.11 - 6.22<br>
> Type: Nested Loop (Inner); ; Cost: 5.11 - 471.95<br>
> Type: Seq Scan; Rel: minnesota_counties ; Cost: 0.00 - 34.09<br>
> Type: Bitmap Heap Scan; Rel: mn_smokers ; Cost: 5.11 - 437.50<br>
> Type: Bitmap Index Scan; Rel: mn_smokers_gist_4326 ; Cost: 0.00 - 5.10<br>
> Type: CTE Scan; Rel: points ; Cost: 0.00 - 4.00<br>
> Type: Materialize; ; Cost: 0.00 - 59.57<br>
> Type: Seq Scan; Rel: mn_dasymetric ; Cost: 0.00 - 47.38<br>
> <br>
> <br>
> with points as<br>
> (<br>
> select sp_id as point_id, smoker as point_value, ms.geom<br>
> from mn_smokers ms <br>
> inner join minnesota_counties mc on ST_Intersects(ms.geom, mc.geom)<br>
> )<br>
> , point_raster_agg as<br>
> (<br>
> select geom, count(point_id) as pixel_point_value<br>
> from points <br>
> group by geom<br>
> )<br>
> , output_rast as<br>
> (<br>
> select r.rid, pixel_point_value, ST_SetValue(rast, ST_Transform(p.geom, \
26915), pixel_point_value ) as setrast<br> > ,rast, geom<br>
> from point_raster_agg p<br>
> inner join mn_dasymetric r on ST_intersects(r.rast, ST_Transform(p.geom, \
26915))<br> > _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> > <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> <br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" \
target="_blank">postgis-users@lists.osgeo.org</a><br> <a \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" \
target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br> \
</blockquote></div>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic