[postgis-users] sql/function which returns points within certain distance?

[Paul Ramsey]

select * from pttable where the_geom && 
GeometryFromText('BOX3D($pt_x - $radius  $pt_y - $radius,$pt_x + $radius
$pt_y + $radius)'::box3d,-1) 
and distance(the_geom, GeometryFromText('POINT($pt_x $pt_y)',-1)) <
$radius;

What is happening here? We have an index-based 'overlap query' going on
first, using the && operator, followed by a slower distance() test. We
count on the optimizer to figure out to do the && test first and then
the distance() test. If we did not trust the optimizer we could simple
next the && query inside the distance() query with a sub-select.

Twan Kogels wrote:
> 
> Hello,
> 
> I have put a point layer (.shp) with many points in a postgresql database,
> i've done this with the shp2pgsql tool supplied with the postgis distribution.
> 
> When i let Mapserver load the layer with a postgis connection, all points
> appear correct.
> 
> I'm now reading the postgis/sql functions of postgis and i'm wondering if
> it is possible to create a query which returns all points within a distance
> of 10 km. from a center point.
> 
> To illustrate my question i have made a picture:
> http://www.gdeesha.com/bin/center.gif
> 
> As you can see the picture contains several black points, the blue point is
> the center point. The red circle is the 10 km circle. Is it possible that a
> sql query will return only the black point in the red circle?
> 
> (The circle may also be replaced by a rectangle)
> 
> Best regards,
> Twan Kogels
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users

-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey at refractions.net
     | Phone: (250) 885-0632
     \_