[postgis-users] sql/function which returns points within certain distance?
Paul Ramsey
pramsey at refractions.net
Wed May 22 09:51:34 PDT 2002
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
\_
More information about the postgis-users
mailing list