[postgis-users] Bounding Box to Speed up Queries on Index

[Brent Wood]

--- adam at spatialsystems.org wrote:


---------------------------------
I have 1.5 million data points and I need to find all the data points within
300 meters of a specific location.  I have a GiST index set on my "location"
(geom) column and it is Vacummed and Analyzed.
 
I'm trying to make my query but I'm not sure how to set the bounding box.  I'd
like the box to be no closer than 300 meters to my query point.  How do I set
the BOX3D inputs?  I don't understand the "Units".
 

The map units are degrees for SRID 4326. You are using a box (I assume) to
speed things up, & can then apply a filter such distance to refine the result
set. You could transform your data to a meters based projection, to your map
units are meters, but this seems unnecessary overhead, given you don't need
that level of precision in your box query.

So to get an approx measure of 300m in degrees, given around 110,000m per
degree, your box will be the point location +- about 0.0027 (round up to, say, 
0.003 to ensure the box is big enough to pick up all the points you want in the
result set), so your BOX3D clause would be along the lines of:

where location
   && 'BOX3D((-87.983439-0.003) (42.042663-0.003),
             (-87.983439+0.003) (42.042663+0.003))'::box3d 

(given you are passing a string, you'd need to calculate the XY box coords
beforehand, unless you use concat '||' to build up the string inline, so the
above example shows the process, not the code)


HTH,

  Brent Wood



SELECT 
    ec,
    long,
    lat,
    distance_spheroid ( 
        location, 
        SetSRID(MakePoint(-87.983439, 42.042663),4326), 
        'SPHEROID["WGS_1984",6378137,298.257223563]'
    ) as distance
FROM 
    best_ec
WHERE
    location
    && 'BOX3D(90900 190900, 100100 200100)'::box3d AND
    distance_spheroid(
        location, 
        SetSRID( MakePoint(-87.983439, 42.042663), 4326 ), 
        'SPHEROID["WGS_1984",6378137,298.257223563]'
    ) < 300
ORDER BY 
    distance_spheroid(
      &
 nbsp; location, 
        SetSRID( MakePoint(-87.983439, 42.042663), 4326 ), 
        'SPHEROID["WGS_1984",6378137,298.257223563]'
    )
 
 
Thanks,
Adam
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>