From: | Ivan Panchenko <i(dot)panchenko(at)postgrespro(dot)ru> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: postgresql 13.1: precision of spatial operations |
Date: | 2022-11-29 21:22:12 |
Message-ID: | 01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Vadim,
On 29.11.2022 19:39, Вадим Самохин wrote:
> 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
>
First of all, you could reduce your large example to a smaller one:
select
'(37.6220129,55.7519367),(37.6215344,55.7536616),(37.6172064,55.7559509),(37.6220129,55.7519367)'::polygon
@> '(37.617900,55.755814)'::point;
> 6. Just in case, here are the images.
> The polygon:
> image.png
> And the point:
> image.png
>
gnuplot could be used for easier visualization.
> Am I doing anything wrong? Any idea how to fix that?
The problem is that the geometric comparison operations are "fuzzy", see
here
https://github.com/postgres/postgres/blob/master/src/include/utils/geo_decls.h
I can recommend you either multiply your coordinates by 10000 or rebuild
Postgres with EPSILON = 0.
Regards,
Ivan
From | Date | Subject | |
---|---|---|---|
Next Message | Kirk Wolak | 2022-11-29 22:16:23 | Re: delete statement returning too many results |
Previous Message | Young Seung Andrew Ko | 2022-11-29 20:46:37 | PostgreSQL extension for storing Graph data (Apache AGE) |