Thread: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Colin Dunklau
Date:
Hello! I believe I've found a bug in the type conversion process from polygon to point. In the documentation found here http://www.postgresql.org/docs/9.2/interactive/functions-geometry.html, Table 9-32 claims that running the point() function on a polygon returns the "center of polygon". This is not the case for some polygons, as shown below. I'm running postgres 9.2.2, on Mac 10.8. This is a fresh install of Postgres.app from http://postgresapp.com/ : frink:~ cdunklau$ psql psql (9.2.2) Type "help" for help. For the below two queries, I expect to get a result of (0.5, 0.5). cdunklau=# select point( polygon '((0,0),(0,1),(1,1),(0,1))'); point ------------- (0.25,0.75) (1 row) cdunklau=# select point( polygon '((0,0),(1,0),(1,1),(1,0))'); point ------------- (0.75,0.25) (1 row) The conversion does indeed work as expected for other polygons: cdunklau=# select point( polygon '((-1,-1),(-1,1),(1,1),(1,-1))'); point ------- (0,0) (1 row) Please let me know if you have problems reproducing this error, or if I can assist in any way to isolate the problem. I had hoped to use the type conversion to replace center-finding application logic, but it appears I'll need to keep the application logic at least until this bug is fixed. Best regards, Colin Dunklau
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Tom Lane
Date:
Colin Dunklau <colin.dunklau@gmail.com> writes: > Hello! I believe I've found a bug in the type conversion process from > polygon to point. > In the documentation found here > http://www.postgresql.org/docs/9.2/interactive/functions-geometry.html, > Table 9-32 claims that running the point() function on a polygon > returns the "center of polygon". This is not the case for some > polygons, as shown below. It looks like what the code is actually computing is the average X position and average Y position of the points listed in the polygon. I'm not sure how defensible that algorithm is (the comment attached to it mumbles something about how it should be the weighted means of the line segments instead). But for non-convex polygons like these I'm not sure there's any intuitively correct answer. If you want us to change the code to produce the answers you exhibit, you'd better explain (1) how you got them and (2) why that's the right answer and the current way is wrong. regards, tom lane
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Dean Rasheed
Date:
On 1 February 2013 22:16, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Colin Dunklau <colin.dunklau@gmail.com> writes: >> Hello! I believe I've found a bug in the type conversion process from >> polygon to point. > >> In the documentation found here >> http://www.postgresql.org/docs/9.2/interactive/functions-geometry.html, > >> Table 9-32 claims that running the point() function on a polygon >> returns the "center of polygon". This is not the case for some >> polygons, as shown below. > > It looks like what the code is actually computing is the average X > position and average Y position of the points listed in the polygon. That's correct. This is the most common definition used for the centre of a polygon --- formally referred to as the centroid. See for example http://en.wikipedia.org/wiki/Centroid cdunklau=# select point( polygon '((0,0),(1,0),(1,1),(1,0))'); point ------------- (0.75,0.25) (1 row) That is the correct answer. One way to think about the centroid is as the centre of gravity of the polygon. In this case the polygon is an "L" shape, so imagine balancing an "L" shaped object on knife edge --- it would balance if the edge was aligned at 45 degrees to the sides of the L, cutting each edge in half. So the centroid is halfway along the line that connects the midpoints of the L shape. It's also easy to see that the centroid is definitely not at (0.5, 0.5) because the entire polygon lies to the northwest / southeast of that point. Regards, Dean
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Dean Rasheed
Date:
On 3 February 2013 09:16, Dean Rasheed <dean.a.rasheed@gmail.com> wrote: >> It looks like what the code is actually computing is the average X >> position and average Y position of the points listed in the polygon. Although, if that's really how it's being calculated, then that's not really the centroid. Consider for example adding an extra redundant point to one side of a square: polygon '((-1,-1),(-1,0),(-1,1),(1,1),(1,-1))') that will shift the average X position over to the left, but the centroid is still at (0,0). Regards, Dean
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Simon Riggs
Date:
On 1 February 2013 17:54, Colin Dunklau <colin.dunklau@gmail.com> wrote: > For the below two queries, I expect to get a result of (0.5, 0.5). > > cdunklau=# select point( polygon '((0,0),(0,1),(1,1),(0,1))'); > point > ------------- > (0.25,0.75) > (1 row) > I think you just simply mistyped the coordinates... sriggs=# select point( polygon '((0,0),(0,1),(1,1),(1,0))'); point ----------- (0.5,0.5) (1 row) Your last point is a duplicate of the 2nd point, so you have a 4-pointed triangle and hence a strange centre. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Tom Lane
Date:
Dean Rasheed <dean.a.rasheed@gmail.com> writes:
>>> It looks like what the code is actually computing is the average X
>>> position and average Y position of the points listed in the polygon.
> Although, if that's really how it's being calculated, then that's not
> really the centroid.
Yeah --- according to the wikipedia entry, it's the correct answer if
we consider the polygon vertexes as a disconnected collection of points
... but that's probably not what people really expect. I think a
"correct" answer in full generality for polygons that are non-convex,
contain duplicate points, etc might be pretty hard.
regards, tom lane
Re: postgres 9.2.2 point conversion from polygon doesn't always give accurate center
From
Colin Dunklau
Date:
You nailed it, stupid typos. Sorry for the inconvenience, and thank you all for your assistance. On Sun, Feb 3, 2013 at 3:33 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On 1 February 2013 17:54, Colin Dunklau <colin.dunklau@gmail.com> wrote: > >> For the below two queries, I expect to get a result of (0.5, 0.5). >> >> cdunklau=# select point( polygon '((0,0),(0,1),(1,1),(0,1))'); >> point >> ------------- >> (0.25,0.75) >> (1 row) >> > > I think you just simply mistyped the coordinates... > > sriggs=# select point( polygon '((0,0),(0,1),(1,1),(1,0))'); > point > ----------- > (0.5,0.5) > (1 row) > > Your last point is a duplicate of the 2nd point, so you have a > 4-pointed triangle and hence a strange centre. > > -- > Simon Riggs http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services