pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
postgresql 13.1: precision of spatial operations
5+ messages / 3 participants
[nested] [flat]

* postgresql 13.1: precision of spatial operations
@ 2022-11-29 16:39  Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Вадим Самохин @ 2022-11-29 16:39 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

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?

Attachments:

image.png (image/png, 315.9K) download view image
image.png (image/png, 80.8K) download view image
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: postgresql 13.1: precision of spatial operations
@ 2022-11-29 21:22  Ivan Panchenko <i.panchenko@postgrespro.ru>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Ivan Panchenko @ 2022-11-29 21:22 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: postgresql 13.1: precision of spatial operations
@ 2022-11-30 15:48  Вадим Самохин <samokhinvadim@gmail.com>
  parent: Ivan Panchenko <i.panchenko@postgrespro.ru>
  0 siblings, 1 reply; 5+ messages in thread

From: Вадим Самохин @ 2022-11-30 15:48 UTC (permalink / raw)
  To: Ivan Panchenko <i.panchenko@postgrespro.ru>; +Cc: pgsql-general@lists.postgresql.org

Thank you so much Ivan, it worked!

ср, 30 нояб. 2022 г. в 00:22, Ivan Panchenko <i.panchenko@postgrespro.ru>:

> 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: image.png]
> And the point:
> [image: 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
^ permalink raw reply [nested|flat] 5+ messages in thread

* Re: postgresql 13.1: precision of spatial operations
@ 2022-11-30 17:51  Brad White <b55white@gmail.com>
  parent: Вадим Самохин <samokhinvadim@gmail.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Brad White @ 2022-11-30 17:51 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

On 11/30/2022 9:48 AM, Вадим Самохин wrote:

> Thank you so much Ivan, it worked!
Can you give any more detail on which approach you took, for the sake of 
future followers?





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: postgresql 13.1: precision of spatial operations
@ 2022-12-01 08:40  Вадим Самохин <samokhinvadim@gmail.com>
  parent: Brad White <b55white@gmail.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Вадим Самохин @ 2022-12-01 08:40 UTC (permalink / raw)
  To: Brad White <b55white@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

ср, 30 нояб. 2022 г., 20:51 Brad White <b55white@gmail.com>:

> On 11/30/2022 9:48 AM, Вадим Самохин wrote:
>
> > Thank you so much Ivan, it worked!
> Can you give any more detail on which approach you took, for the sake of
> future followers?
>

Sure, I multiplied all points' coordinates by a factor of 10^6.

Here are the steps to demonstrate that the solution Ivan gave worked:

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
('(37622012.9,55751936.7),(37621534.4,55753661.6),(37617206.4,55755950.9),(37622012.9,55751936.7)');

3. select count(1) from zones where zone_polygon @>
'(37617635,55755814)'::polygon;
 count
-------
     0
(1 row)

>

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2022-12-01 08:40 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-29 16:39 postgresql 13.1: precision of spatial operations Вадим Самохин <samokhinvadim@gmail.com>
2022-11-29 21:22 ` Ivan Panchenko <i.panchenko@postgrespro.ru>
2022-11-30 15:48   ` Вадим Самохин <samokhinvadim@gmail.com>
2022-11-30 17:51     ` Brad White <b55white@gmail.com>
2022-12-01 08:40       ` Вадим Самохин <samokhinvadim@gmail.com>

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