CODEWITHSUNDEEP
mysqlspatial-query
Gorjan Mishevski
Gorjan Mishevski

Reputation: 184

Find if a POINT is inside a POLYGON in MySQL 5.7

I need to find all properties that are contained in a user searched location; a location can be a city, county etc. Every property has a lat and long which can be used to create a POINT in MySQL. The locations are of type GEOMETRY, most of them are POLYGONs and MULTIPOLYGONS. After a week of searching and troubleshooting I can't seem to make the DB give me a match. This is the scenario.

SET @area = (SELECT area.polygon from area where area.city = 'Charlotte' and area.type =  'city');
SET @property = (SELECT property.point from property where id = 397315);
SELECT st_contains(@area, @property);

@area gets the POLYGON for Charlotte.

@property gets the POINT for a property that I am 100% sure is inside Charlotte or inside the POLYGON. I even tested it with external tools for a sanity check.

every time ST_CONTAINS returns 0! Whatever I do it is always a 0.
I tried using ST_WITHIN failed. Then I made sure the SRID is the same. First both were set on 4328 still nothing, then I set them on 0, again nothing. I added a spatial index for both the polygon and point still nothing.

This is the POINT that I am matching against POINT (35.086449 -80.741455).

For sanity check I ran this query: 

select st_contains(
ST_GeomFromText('POLYGON (( 322 322, 322 513, 528 513, 528 322, 322 322 ))'),
ST_GeomFromText('POINT (418 411)'));

Which resulted in 1 which is a match!

What the hell I am missing? Thank you.

Upvotes: 12

Views: 4669

Answers (1)

Kodr.F
Kodr.F

Reputation: 14390

Try this

    SELECT MBRContains(POLYGON (( 322 322, 322 513, 528 513, 528 322, 322 322 )),   
 GeomFromText('Point(418 411)')) AS whatEver

Worked for me

Upvotes: 4

Related Questions