Hi, I am using postgis-2.0SVN (revision 7065, I think - I downloaded it on Monday).
I am doing an analysis in which I need to test 50,000 points in a vector table against a binary raster (with 0 set as NoData and 1 being the area of interest). From what I can find online (Amazon has not sent my copy of Postgis in Action yet), the following is the canonical way of doing this: select V.id, ST_Intersection(R.rast, V.the_geom) as pts from raster R, vector V where ST_Intersects(R.rast, V.the_geom) All I really need is: select V.id from raster R, vector V where ST_Intersects(R.rast, V.the_geom) and I was hoping that it would be a faster way to do this. However, when I run a test on my data, however, with "LIMIT 10", the equivalent code takes 24,926 ms. Doing something more complicated, like this (because ST_Intersection seems to return empty geometries for the NoData areas): select V.id, ST_IsEmpty((ST_Intersection(R.rast, V.the_geom)).geom) as ptest from raster R, vector V LIMIT 10; -- then I filter these results for ptest = FALSE takes 589 ms. I tried scaling this up to LIMIT 100 (359,304 and 6,444 ms, respectively) and 1000 (1,980,397 and 61,628 ms). This is a huge difference. Is this the best way to use ST_Intersection/ST_Intersects? Am I doing something wrong here? Would I be better off converting this to a vector? Any tips on optimizing this query would be appreciated (I have to run the same query against several different rasters, plus I want to understand how to do this right). JP
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
