All geek questions in one place

Find if POINT is inside POLYGON in MySQL 5.7

I need to find all the properties that are contained in a user-found place; the location may be a city, county, etc. Each property has lat and long, which can be used to create a POINT in MySQL. Places are located like GEOMETRY, most of which are POLYGON and MULTIPOLYGONS. After a week of troubleshooting, I can't get DB to give me a match. This is a script.

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 a FAST for Charlotte.

@property gets a POINT for a property that I'm 100% sure is inside Charlotte or inside POLYGON. I even tested it with external tools to test the health.

every time it ST_CONTAINSreturns 0! Whatever I do is always 0.
I tried to use ST_WITHIN. Then I made sure that the SRID is the same. At first, both were set to 4328 nothing else, then I set them to 0, again nothing. I added a spatial index for both the polygon and the point, nothing else.

This is the DOT that I map to POINT (35.086449 -80.741455).

To test the health, I ran this request:

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

The result is 1, which corresponds to!

What the hell am I missing? Thank.

+4
source share
1 answer

try it

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

Worked for me

0
source

Source: https://habr.com/ru/post/1683070/

More articles:


All Articles