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 | |
  • 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

Your Answer

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

Browse other questions tagged or ask your own question.