[postgis-users] creating a spatial index for finding objects withing a certaindistance in miles based on lat/lon

[Daniel Ceregatti]

Hugh W. O'Brien wrote:

> Since I only had to support North America, I picked 3 postal codes in
> Houston that formed the opposite and adjacent sides of a right
> triangle.  Then, using the geocoded postal code data my company
> purchased, I identified the 3 coordinates that stood for my postal
> codes.  Then using the distance_spheriod(...)  ( I think ) function of
> postgis, I determined a degrees/mile conversion for latitude and
> longitude.  Note, at the time I did this the distance_spheroid(...)
> function of postgis had a bug ( it couldn't handle either the verticle
> or horizontal calc because of a bug ).  So I tweak the geocoded postal
> code value  ( decreased the last decimal point value of one of the
> points by 1 to get around the bug ). 

Unfortunately I can't narrow my search area down to such a small area.
Ultimately I want to have the entire world be searchable via this facility.

It seems that I simply need to incorporate the "degrees per distance
unit" in the query that creates the box3d dynamically. Perhaps all
that's needed now is to work out the math and test it.

Oh, and figure out how to get around boxes that traverse 90 degree
latitudes and  0/180 degree longitudes.

>
>
> I wish I could give you more help like sample code and my actual
> conversions and sample query but I no longer work for the company I
> did this for ( been unemployed for 3+ month atm ). 

Sorry to hear that. :(

>
>
> I hope this helps. 

I believe this will help me understand this whole thing better. I'm just
surprised that this is so difficult to implement in postgres, seeing 
how easy it was to implement in oracle. You'd think that this type of
application would be standard fare in such a package.

Daniel Ceregatti

>
>
> -- Hugh
>
> Daniel Ceregatti wrote:
>
>> Sigh...I'm still having some trouble wrapping my mind around all this. I
>> followed the thread referenced in this post:
>>
>> http://postgis.refractions.net/pipermail/postgis-users/2002-December/001905.html
>>
>>
>> This post shows a "bastardized" query for doing what I need. Thing is,
>> it seems that there are some assumptions made. For example, the "Miles
>> per lat, Miles per lon" deal. Obviously this will vary over great
>> distances. As I want to be able to query distances around the entire
>> world, and across 90 lon/180 lat, I don't see how this will do what I
>> need.
>>
>> Hugh, perhaps you can tell me how you ultimately did this? My thread
>> starts here:
>>
>> http://postgis.refractions.net/pipermail/postgis-users/2003-September/003087.html
>>
>>
>> Thanks,
>>
>> Daniel Ceregatti
>>
>> Paul Ramsey wrote:
>>
>>  
>>
>>> Daniel Ceregatti wrote:
>>>
>>>   
>>>
>>>>> Daniel, lat/lon *are* degrees. Degrees are the units of a lat/lon
>>>>> data
>>>>> set. You can pretend that lat/lon coordinates are planar for the
>>>>>       
>>>>
>>>> I'm not too "geometrically inclined", hence my confusion. So what
>>>> you're
>>>> saying is that my existing index is already appropriate for the query?
>>>>     
>>>
>>> Yes, that is what I am saying. You just have to question your index in
>>> the right way. Instead of asking "what are all the points within 3
>>> miles of point X?" you have to ask "what are all the points within a
>>> bounding box of (lon1 lat1,lon2 lat2) and also within 3 miles of point
>>> X?" So the only "tricky" bit is constructing the bounding box so that
>>> it is about 6 miles on a side but expressed in degrees of latitude and
>>> longitude.
>>>
>>> P
>>>
>>>   
>>
>
>