12

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.

Gorjan Mishevski
  • 182
  • 3
  • 10

1 Answers1

4

Try this

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

Worked for me

Kodr.F
  • 13,932
  • 13
  • 46
  • 91
  • By replacing "st_contains" by "MBRContains" you are changing the shape of the polygon. And thus, this request will return some points that should not have been included. I suggest you read about "Minimum Bounding Rectangles" to understand it – SKMTH Sep 24 '21 at 09:08