[postgis-devel] Why doesn't knn work on table1.geom <#>table2.geom

Paul Ramsey pramsey at opengeo.org
Mon Oct 17 07:46:12 PDT 2011

No, no ticket please. I don't feel like there's anything unexpected
here. I imagine the performance difference with PgSQL is, as you
noted, because 'geometry' is a varlena while 'point' is a fixed size

On Mon, Oct 17, 2011 at 1:08 AM, Nicklas Avén
<nicklas.aven at jordogskog.no> wrote:
> Hallo Regina
>> Nicklas -- have you tried your tests on the <#>.  That one doesn't do any
>> centroid calculations
>> so should give the same answer for point and point but different for others.
>> I'm not sure if we are always doing a centroid calc even for points,
>> but if we are then we might see a difference in performance speed between
> Trying:
> SELECT a.gid, b.gid, ABS((a.p1<#>b.p1)-(a.p2<->b.p2)) FROM points a,
> points b WHERE b.gid =1 ORDER BY ABS((a.p1<#>b.p1)-(a.p2<->b.p2)) DESC
> limit 1;
> The <#> operator also gives other answers than ST_Distance and
> PostgreSQL <->, but different from PostGIS <-> operator. The difference
> is about 0.008 units. The timing with <#> operator is slightly worse
> than <-> operator (no indexes involved).
> I don't think anymore this has anything to to with centroid calc since
> that in the bbox case is just dividing the bbox sides by 2.
> The difference comes from that the distance is from bboxes instead of
> the real geometries. Don't we use double precision bboxes now?
> If we don't, that is why. If we do use double precision bboxes it might
> be some issue there.
> Paul, do you want a ticket or is it expected?
> /Nicklas
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel

More information about the postgis-devel mailing list