CODEWITHSUNDEEP
mysqlsqlpolygonpoint
niche
niche

Reputation: 53

check for point in a polygon

This code selects all the rows in the table when the point is hard coded with no errors or warnings:

SELECT *
    FROM lat_lng 
    WHERE Contains(
            GeomFromText('POLYGON((0 0,0 100,100 100,100 0,0 0))'),
            GeomFromText('Point(50 50)') )

OR defined by variable $var = "50 50" (with no errors or warnings)

SELECT *
    FROM lat_lng 
    WHERE Contains(
            GeomFromText('POLYGON((0 0,0 100,100 100,100 0,0 0))'),
            GeomFromText('Point($var)') )

However, when I use a column called "location" to define the point, zero rows are selected (with no errors or warnings):

SELECT *
    FROM lat_lng 
    WHERE Contains(
            GeomFromText('POLYGON((0 0,0 100,100 100,100 0,0 0))'),
            GeomFromText('Point(location)') )

based on this two row sample table:

id | location
1  |  50 50
2  |  500 500

why?

Upvotes: 5

Views: 7632

Answers (1)

jcaron
jcaron

Reputation: 17710

You are providing a string which is Point(location) (verbatim). What you want is reference the location column, so you'll need to build the string:

CONCAT('Point(',location,')')

Also you probably want to store the Point directly in that other table (rather than text), and then reference it without the GeomFromText

Update

Storing Points directly in you database:

Schema:

CREATE TABLE locations (id integer primary key auto_increment,location point);
INSERT INTO locations (location) VALUES (Point(50,50));
INSERT INTO locations (location) VALUES (Point(500,500));

Request:

SELECT id
    FROM locations
    WHERE Contains(
        GeomFromText('POLYGON((0 0,0 100,100 100,100 0,0 0))'),
        location);

Update 2

SQL fiddle: http://sqlfiddle.com/#!2/b5d1f/9

Upvotes: 5

Related Questions