postgresql 13.1: precision of spatial operations

From: Вадим Самохин <samokhinvadim(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: postgresql 13.1: precision of spatial operations
Date: 2022-11-29 16:39:42
Message-ID: CAGVmuwqbGT6cGtSNKJT8fD=v8uNBm7n7hQFcAzNRsfEYecYY9w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi there,
I have polygons in a table and I'm fetching those that contain a specific
point. The problem is that when I'm checking against a point which is
really close (~5 meters) but is not contained within a polygon, it gets
fetched nevertheless.

Steps to reproduce:
1.
create table zones (
zone_id int,
zone_polygon polygon,
description text
);
create index zones__zone_polygon on zones using gist(zone_polygon poly_ops);

2. insert into zones (zone_polygon) values
('(37.6040241,55.7609641),(37.6240129,55.7519367),(37.6215344,55.7536616),(37.6172064,55.7559509),(37.6126178,55.7584013),(37.6088694,55.7622611),(37.60747,55.7633072),(37.6040241,55.7609641)');

3. Here is this polygon on a map:
https://www.keene.edu/campus/maps/tool/?coordinates=37.6040241%2C%2055.7609641%0A37.6240129%2C%2055.7519367%0A37.6215344%2C%2055.7536616%0A37.6172064%2C%2055.7559509%0A37.6126178%2C%2055.7584013%0A37.6088694%2C%2055.7622611%0A37.6074700%2C%2055.7633072%0A37.6040241%2C%2055.7609641

4. Check whether a point is contained within a polygon: select count(1)
from zones where zone_polygon @> '(37.617635,55.755814)'::polygon;
count
-------
1
(1 row)

5. But actually it's not (sorry, couldn't find a way to represent this
point on the same map. Use street view, it's more convenient to see that):
https://www.keene.edu/campus/maps/tool/?coordinates=37.617635%2C%2055.755814

6. Just in case, here are the images.
The polygon:
[image: image.png]
And the point:
[image: image.png]

Am I doing anything wrong? Any idea how to fix that?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2022-11-29 16:44:47 Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
Previous Message Bruce Momjian 2022-11-29 16:31:19 Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all