[postgis-users] Efficiency of <->, # and &&/st_distance

[Nicklas Avén]

Hallo Jan

On Mon, 2012-02-06 at 15:25 +0100, Jan Hartmann wrote:
> Hi,
> 
> I am working on an interactive application to add and remove points
> from a map. When removing, the user clicks on the map and should get
> back the nearest point in the database. Normally, I would do this with
> something like: "select ... where <point> && the_geom and
> st_distance(<point>,the_geom) < 10)". 

Here you should use ST_Dwithin instead. The above will never get to the
distance test if the point is outside the bbox of the_geom. 

The points outside the bbox can be closer than the points inside the
bbox. But if, as I understad it the_geom is also a point, then the bbox
is (almost) the same as the point.


> I see there are two new operators now: # and <->. Is there any
> difference in efficiency in this case?

Yes, this is the new operators for KNN-distance.
To use it you will need PostgreSQL 9.1 and the not yet released PostGIS
2.0

The KNN distance will use the gist-index to order the points by their
distance which will make it very efficiency. 

The difference between <#> and <-> is that since the index only is aware
of the bbox it cannot handle the "real" geometry. Then there is two
options:
<#> uses the smalest distance to the whole bbox 
<-> uses the distance to the center of the bbox

the syntax for KNN is in the example of the operator:
http://postgis.org/documentation/manual-svn/geometry_distance_centroid.html


HTH

Nicklas

> 
> Also: all three methods require a GIST index. However, this is a very
> interactive application that continuously changes the number of
> points. The number of points can potentially grow quite large
> (hundreds of thousands) Are there any rules to organise the database
> so as to optimize GIST access and update (e.g. maximum number of
> records, partitioning into multiple tables)?
> 
> Jan
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users