Professional Web Applications Themes

Point in Polygon - MySQL

I was trying out the OpenGIS spatial extensions in mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline 5.1 and noticed that there might be a bug (or feature) with Points and Polygons, specifically I'm interested in finding out wether a point is within a polygon or not. So here's the little test I ran: create table map_test ( name CHAR(80) not null, loc Point not null, spatial key(loc), primary key(name) ); insert into map_test values ( 'one two', GeomFromText('POINT(1 2)') ); insert into map_test values ( 'two two', GeomFromText('POINT(2 2)') ); insert into map_test values ( 'two one', ...

  1. #1

    Default Point in Polygon

    I was trying out the OpenGIS spatial extensions in
    mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline
    5.1

    and noticed that there might be a bug (or feature) with Points and
    Polygons, specifically I'm interested in finding out wether a point is
    within a polygon or not. So here's the little test I ran:

    create table map_test ( name CHAR(80) not null, loc Point not null,
    spatial key(loc), primary key(name) );

    insert into map_test values ( 'one two', GeomFromText('POINT(1 2)') );
    insert into map_test values ( 'two two', GeomFromText('POINT(2 2)') );
    insert into map_test values ( 'two one', GeomFromText('POINT(2 1)') );

    select name, AsText(loc) from map_test where Within( loc,
    GeomFromText('POLYGON((0.8 1.2, 2.2 2.6, 2.6 2.2, 1.2 0.8, 0.8 1.2))')
    );
    +---------+-------------+
    | name | AsText(loc) |
    +---------+-------------+
    | one two | POINT(1 2) |
    | two two | POINT(2 2) |
    | two one | POINT(2 1) |
    +---------+-------------+
    3 rows in set (0.00 sec)

    mysql> select name, AsText(loc) from map_test where Contains(
    GeomFromText('POLYGON((0.8 1.2, 2.2 2.6, 2.6 2.2, 1.2 0.8, 0.8 1.2))'),
    loc );
    +---------+-------------+
    | name | AsText(loc) |
    +---------+-------------+
    | one two | POINT(1 2) |
    | two two | POINT(2 2) |
    | two one | POINT(2 1) |
    +---------+-------------+
    3 rows in set (0.00 sec)

    The way the points and that polygon are arranged, I expected to get
    only POINT(2 2) returned.

    As you can see, i tried both Within and Contains mentioned at
    [url]http://dev.mysql.com/doc/refman/5.0/en/functions-that-test-spatial-relationships-between-geometries.html[/url]
    (perhaps someone can explain the subtle difference ?) But neither of
    these worked.

    So what's the deal here? I suspect it's got something to do with the
    algorithm used to compute Within and Contains, but I'm not really sure.

    the.theorist@gmail.com Guest

  2. #2

    Default Re: Point in Polygon

    [email]the.theorist[/email] wrote:
    > I was trying out the OpenGIS spatial extensions in
    > mysql Ver 14.12 Distrib 5.0.18, for pc-linux-gnu (i686) using readline
    > 5.1
    >
    > and noticed that there might be a bug (or feature) with Points and
    > Polygons, specifically I'm interested in finding out wether a point is
    > within a polygon or not. So here's the little test I ran:
    >
    > create table map_test ( name CHAR(80) not null, loc Point not null,
    > spatial key(loc), primary key(name) );
    >
    > insert into map_test values ( 'one two', GeomFromText('POINT(1 2)') );
    > insert into map_test values ( 'two two', GeomFromText('POINT(2 2)') );
    > insert into map_test values ( 'two one', GeomFromText('POINT(2 1)') );
    >
    > select name, AsText(loc) from map_test where Within( loc,
    > GeomFromText('POLYGON((0.8 1.2, 2.2 2.6, 2.6 2.2, 1.2 0.8, 0.8 1.2))')
    > );
    > +---------+-------------+
    > | name | AsText(loc) |
    > +---------+-------------+
    > | one two | POINT(1 2) |
    > | two two | POINT(2 2) |
    > | two one | POINT(2 1) |
    > +---------+-------------+
    > 3 rows in set (0.00 sec)
    >
    > mysql> select name, AsText(loc) from map_test where Contains(
    > GeomFromText('POLYGON((0.8 1.2, 2.2 2.6, 2.6 2.2, 1.2 0.8, 0.8 1.2))'),
    > loc );
    > +---------+-------------+
    > | name | AsText(loc) |
    > +---------+-------------+
    > | one two | POINT(1 2) |
    > | two two | POINT(2 2) |
    > | two one | POINT(2 1) |
    > +---------+-------------+
    > 3 rows in set (0.00 sec)
    >
    > The way the points and that polygon are arranged, I expected to get
    > only POINT(2 2) returned.
    >
    > As you can see, i tried both Within and Contains mentioned at
    > [url]http://dev.mysql.com/doc/refman/5.0/en/functions-that-test-spatial-relationships-between-geometries.html[/url]
    > (perhaps someone can explain the subtle difference ?) But neither of
    > these worked.
    >
    > So what's the deal here? I suspect it's got something to do with the
    > algorithm used to compute Within and Contains, but I'm not really sure.
    Looks like you missed the little disclaimer at the top of that page:

    "Currently, MySQL does not implement these functions according to the
    specification. Those that are implemented return the same result as the
    corresponding MBR [minimal bounding rectangle]-based functions. This
    includes functions in the following list other than Distance() and
    Related()."

    The minimal bounding rectangle of your polygon is POLYGON((0.8 0.8, 0.8
    2.6, 2.6 2.6, 2.6 0.8, 0.8 0.8)). This rectangle includes all three
    points. Apparently MySQL will implement proper geometric comparisons
    "when they get around to it"; don't ask me why, since the basic
    point-in-polygon test only takes around 10 lines of code.

    -- David

    David Wahler Guest

  3. #3

    Default Re: Point in Polygon

    Oh dear me, I should have paid more attention.

    In that case. Is there any way to extend MySQL in this one area? (at
    least until some future version fixes the lapse)

    the.theorist@gmail.com Guest

Similar Threads

  1. the angle from point A to point B (where the mouse is)
    By Jon Moyles in forum Macromedia Director 3D
    Replies: 3
    Last Post: February 24th, 04:03 AM
  2. polygon count delema
    By Roofy in forum Macromedia Director 3D
    Replies: 5
    Last Post: September 18th, 08:40 PM
  3. create polygon on the fly
    By Hilmar Hermens in forum Macromedia Director 3D
    Replies: 0
    Last Post: August 26th, 03:23 PM
  4. SVG outputting polygon instead of path
    By Brian_Huonker@adobeforums.com in forum Adobe Illustrator Macintosh
    Replies: 3
    Last Post: July 23rd, 08:02 AM
  5. Polygon Tool
    By Julian Barnett in forum Macromedia Fireworks
    Replies: 1
    Last Post: October 1st, 02:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139