Re: Looking for help with spatial query SQL

[Dustin Burke]

Hi Jennifer,
I can't help much with your spatial query but have some ideas for how to attack the problem. First off, how big is the dataset you're dealing with? I imagine the set is very static (how often does a trail or campground move?) How are you representing the trails? Are you approximating the trails using a discrete set of points? For the campgrounds, how are these indexed? As points or as regions? If they're points then whatever query you perform will only return the campgrounds whose main office falls within the "three mile buffer" (assuming the main office is the point that gets indexed).

The naive approach would be to compute the distance of EACH campground to the trail system. You would have a very accurate answer and would incur only a one-time computational expense assuming you store the results of this brute-force query. You could even devise a system that whenever new campgrounds are added (or existing ones removed) you update the stored result to keep the result consistent.

I like your idea of creating the "three mile buffer". Essentially that would be your range query and it would be used to find the MBRs that overlap with this "buffer". Finding these MBRs is only the first step (filter step - reducing the number of MBRs you need to pursue further). The next step is refinement where you extract all the objects (campgrounds) within these MBRs and compute the distance function on them. This is an improvement over the naive approach since you're limiting the number of objects to perform the distance function on. However, is it worth the cost of the additional overhead needed to spatially index these points if you potentially only need to perform this calculation once?

Best,
Dustin