1

I am trying to figure out the state that Berlin is in. So now my query SELECT * FROM states WHERE ST_Contains(Geo, Point(13.383068, 52.530644)); return two results. One for Berlin which is correct, but also Brandenburg which is surrounding Berlin. See the attached graphics. So it seems that ST_Contains does not account for the hole inside.

Currently the geospatial column is of datatype MulitPolygon.

Is there any other my-sql function that I can use to only find the correct state?

enter image description here

Improve this question
6
  • does your hole have the right orientation? – Ian Turton♦ Jan 10 '19 at 13:49
  • I got if from here: polygons.openstreetmap.fr/get_wkt.py?id=62504&params=0. Not sure what you mean by right orientation? – Georg Jan 10 '19 at 13:55
  • I forget for mysql outers are clockwise and holes are anti-clockwise or the other way round. check the docs – Ian Turton♦ Jan 10 '19 at 14:06
  • Do you know if there's a way to reverse the result I get from openstreetmap? – Georg Jan 10 '19 at 14:07
  • 1
    You should test your geometry with ST_IsValid() If the internal ring doesn't have clockwise rotation (opposite of the external counter-clockwise rotation), then it's an invalid overlapping exterior ring, which would generate strange topology errors. – Vince Jan 10 '19 at 14:13
0

MySQL contains is not standard compliant. It does not use a precise object shape, just a boundary box.

So it "ignore" holes too.

Take a look at this thread for a MySQL or PostGIS solution: https://stackoverflow.com/questions/15394645/mysql-spatial-contains-shows-wrong-result

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.