{"id":102,"date":"2012-03-31T12:41:05","date_gmt":"2012-03-31T19:41:05","guid":{"rendered":"http:\/\/blog.light42.com\/wordpress\/?p=102"},"modified":"2012-11-10T20:01:41","modified_gmt":"2012-11-11T04:01:41","slug":"knn-gist-index-in-postgresql","status":"publish","type":"post","link":"http:\/\/blog.light42.com\/wordpress\/?p=102","title":{"rendered":"KNN GIST Index in Postgresql"},"content":{"rendered":"

With version 9.1 of PostgreSQL and above, the KNN GIST Index<\/a> is available.<\/p>\n

There are two new operators defined:\u00a0 <-> and <#>\u00a0
\n<\/code><\/p>\n

With version 2.0 of PostGIS<\/a> and above, the KNN GIST Index functionality is exposed for PostGIS geometry types.<\/p>\n

<-><\/strong> - Returns the distance between two points. For point \/ point checks it uses floating point accuracy (as opposed to the double precision accuracy of the underlying point geometry). For other geometry types the distance between the floating point bounding box centroids is returned. Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality.<\/code><\/p>\n

<#><\/strong> - Returns the distance between bounding box of 2 geometries. For point \/ point checks it's almost the same as distance (though may be different since the bounding box is at floating point accuracy and geometries are double precision). Useful for doing distance ordering and nearest neighbor limits using KNN gist functionality.<\/code><\/p>\n

Let’s look at how they operators affect search results in a real-world example \u2013 proximity of parcels from a point on the street. Note that one of the geometries in this example is a very thin polygon just inside of a larger one against the street.
\nThis first example shows bounding box\u00a0ordered distance <#><\/strong>
\n