[postgis-users] spheroid bounding box calculation

[Stephen Woodbridge]

Whooops, you are right. Change that query to be:

select * from poi_table
  where
    st_within(the_geom, st_expand(setsrid(makepoint(x,y),4326), radius))
  order by st_distance(setsrid( makepoint(x,y),4326), the_geom) asc
  limit n;

or to explicitly specify the index usage

select * from poi_table
  where
    st_expand(setsrid(makepoint(x,y),4326), radius) && the_geom
  order by st_distance(setsrid( makepoint(x,y),4326), the_geom) asc
  limit n;

I'm more familiar with the later example, but st_within() is faster in 
some cases so you might want to compare performance of both of these.

-Steve

steve enzer wrote:
> Stephen,
> 
> It seems that st_within takes only two arguments, both geometries, not
> 3 as in your example -- did you mean another function? THANKS
> 
> http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.spatial.doc/rsbp4158.html
> 
> On Thu, Jul 23, 2009 at 1:17 PM, Stephen
> Woodbridge<woodbri at swoodbridge.com> wrote:
>> Oh, and the basic sql query would be:
>>
>> select * from poi_table where st_within(setsrid(makepoint(x,y),4326),
>> the_geom, radius) order by st_distance(setsrid( makepoint(x,y),4326),
>> the_geom) asc limit n;
>>
>> (x,y) == (lon, lat)
>> radius == is your initial search radius
>> n == is your limit on results
>>
>> -Steve
>>
>> Stephen Woodbridge wrote:
>>> steve enzer wrote:
>>>> I'm facing a similar issue; a huge postgis database with a point
>>>> column, trying to find a clean query to bring back the nearest n
>>>> results to a point.  Why isn't that easy? Or is it?
>>> The problem is that the nearest n points is not well defined in spatial
>>> terms. For example think about the difference between rural Wyoming and
>>> downtown Manhattan!
>>>
>>> Typically to make a query fast you need to use and index and that means
>>> you need a bounding box that specifies your search distance. But the bbox
>>> that is good for Manhattan may be too small for Wyoming.
>>>
>>> One strategy is to search at a given small radius and see if you have
>>> enough results, if not double the search radius and redo the query. While
>>> this may seem costly, it is in fact very fast because postgresql is very
>>> good at caching results and pages in memory.
>>>
>>> Hope this helps,
>>>  -Steve
>>>
>>>> I'm using georuby and spatial adapter.
>>>>
>>>> Steve
>>>>
>>>> On Thu, Jul 23, 2009 at 10:01 AM, Jeff Davis<pgsql at j-davis.com> wrote:
>>>>> On Thu, 2009-07-23 at 08:58 -0700, Paul Ramsey wrote:
>>>>>> Hopefully GEOGRAPHY will retire all these requirements once and for
>>>>>> all :)
>>>>> Is that something under development? I'm new to these lists, so I
>>>>> haven't been following.
>>>>>
>>>>> Regards,
>>>>>       Jeff Davis
>>>>>
>>>>> _______________________________________________
>>>>> postgis-users mailing list
>>>>> postgis-users at postgis.refractions.net
>>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>>>>
>>>> _______________________________________________
>>>> postgis-users mailing list
>>>> postgis-users at postgis.refractions.net
>>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at postgis.refractions.net
>>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>