Border-Line Cases: MySQL Point-in_Polygon Function Results in Duplicates When the Point is on the Border

Question

Hi,

I'm using MySQL to pull lat longs from a database and check whether or not they are in a particular neighborhood.

Everything works great, except if the point is on the border between two neighborhoods. Then, the point is included in both neighborhoods. It gets duplicated. What's the best way to handle points that are on borders?

Each point should be counted in only one neighborhood-- not both. Also, I don't want, for example, neighborhood A to get all of the border cases, but the bordering neighborhood, neighborhood B, to get zero cases.

Imagine that the point 30.3030, -70.7070 lies on the border between Newport and Oldport. Unfortunately, the point gets counted twice. It gets recorded as being in both Oldport and Newport.

type | latitude | longitude | neighborhood

small |  30.3030  | -70.7070  | Newport
small |  30.3030  | -70.7070  | Oldport
small |  30.3344  | -70.7274  | Anotherport

I use the select statement below:

SELECT t.type, t.latitude, t.longitude, s.neighborhoods 
  FROM my_type_table t, neighborhood_shapes s
WHERE myWithin(POINTFROMTEXT( CONCAT( 'POINT(', t.latitude, ' ', t.longitude, ')' ) ) , s.neighborhood_polygons )) = 1

my_type_table has columns:

• type (VARCHAR)
• latitude (decimal)
• longitude (decimal)

...and neighborhood_shapes has columns:

• neighborhoods (VARCHAR)
• neighborhood_polygons (geometry)

I use the myWithin fucntion to test whether the point is in one neighorhood or another. Here's a link to it: myWithin function at mySQL forum . The function returns 1 when the point is in the polygon and 0 when it isn't in the polygon.

How would you solve this problem? Any advice?

Thank you!

-Laxmidi

Answer 1

Hi,

Okay, I figured it out. There was a very small overlap in some of the polygons. So, when the function ran it put the point in both neighborhoods.

Thank you.

-Laxmidi