pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ivan Panchenko <i.panchenko@postgrespro.ru>
To: pgsql-general@lists.postgresql.org
Subject: Re: postgresql 13.1: precision of spatial operations
Date: Wed, 30 Nov 2022 00:22:12 +0300
Message-ID: <01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru> (raw)
In-Reply-To: <CAGVmuwqbGT6cGtSNKJT8fD=v8uNBm7n7hQFcAzNRsfEYecYY9w@mail.gmail.com>
References: <CAGVmuwqbGT6cGtSNKJT8fD=v8uNBm7n7hQFcAzNRsfEYecYY9w@mail.gmail.com>

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....
>
> 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

Attachments:

image.png (image/png, 315.9K) download view image
image.png (image/png, 80.8K) download view image

view thread (5+ messages)  latest in thread

Message-ID: <01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru>
Permalink:  ../01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru/
Also on:    postgresql.org/message-id/01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: i.panchenko@postgrespro.ru
  Subject: Re: postgresql 13.1: precision of spatial operations
  In-Reply-To: <01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru>
  References: <CAGVmuwqbGT6cGtSNKJT8fD=v8uNBm7n7hQFcAzNRsfEYecYY9w@mail.gmail.com> <01265526-0922-bb4a-21a5-4b9973adeb2b@postgrespro.ru>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox