December 24th, 2014 by depesz | Tags: , , , , , , | 3 comments »
Did it help? If yes - maybe you can help me?

On 15th of December, Heikki Linnakangas committed patch:

Add point <-> polygon distance operator.
Alexander Korotkov, reviewed by Emre Hasegeli.

I'm not actually using geometric types at all. Or geography. Of course I am aware of PostGIS, and what's built into PostgreSQL itself, but it just so happened, that, so far, I had only one time usecase for them.

But in any way – there are people that use it, and for them it will be much bigger news than it is for me.

We got distance operator for point and polygon.

Let's start with some sanity check. Let's use a triangle, that will have all it's points far from the point that we'll be measuring distance to.


$ SELECT '(1.3,0)'::point <-> '((-100,-50),(0,-50),(0,50))'::polygon;
(1 ROW)

Works. In my short test (creating 100000 random polygons in a table, and doing:

EXPLAIN analyze SELECT * FROM test ORDER BY '(0,0)'::point <-> poly LIMIT 2;

, with gist index on poly, it doesn't look like we can use index. Most likely – as of now, and it will probably be added in future.

In any case – finding distance works now and is definitely usefull. Thanks.

  1. 3 comments

  2. # Alexander Korotkov
    Dec 24, 2014

    I’ve to clarify the things a bit. Someone can find this operator useful itself. But actually it’s a side effect. 🙂
    I have also “KNN-GiST with recheck” patch which allows to do exact KNN with complex geometrical objects. Main use case is PostGIS, but we need to have some example in core for testing. point poly operator is supposed to be such example. For while, only it is committed.

  3. Dec 24, 2014

    So, if I understand right – once the rest will get committed, this operator should use index? Nice.

  4. # Alexander Korotkov
    Dec 24, 2014

    Yes, you understand right. Also, once KNN-GiST with recheck will be committed we will be looking forward for PostGIS to use it.

Leave a comment