MySQL within geometry query

Question

SELECT `NAME` FROM world_boundaries 
WHERE Within(GeomFromText('POINT(8.5929098 50.0286713)'), ogc_geom);

Why does this query return 3 countries when it should return one. It returns: 1. France 2. Germany (correct one) 3. America

Is my country boundaries data flawed or is there some trick with these geometry functions?

I've added the approach I used to solve a similar problem. Hope this helps. — culebrón
– culebrón, Commented Dec 14, 2009 at 19:54

Community · Accepted Answer · 2020-06-20 09:12:55Z

4

MySQL can't check a point in poly yet. It will check if your point is within the ([minX, maxX], [minY, maxY]). Also, I don't see within in MySQL documentation.

Note

Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions. This includes functions in the following list other than Distance() and Related().

These functions may be implemented in future releases with full support for spatial analysis, not just MBR-based support.

From here

I used a point-in-poly ray-tracing algorithm, which can be quite accurate outside of polar regions if the lines aren't long. Otherwise you'll need to solve spherical triangles.

The simple implementation is like this:

create table point (x int, y int...) /* int values (int(lat * 10^6)) for better performance */
create table poly (x1 int, y1 int, x2 int, y2 int...) /* temporary table for 1 poly only */

query that selects points inside the poly:

select point_id from point, poly
where ((y<y2 and y>=y1) or (y<y1 and y>=y2)) and ((x<x2 and x>=x1) or (x<x1 and x>=x2))
group by point_id
having sum(x1*(y2-y1)*sign(y2-y1) < x*(y2-y1)*sign(y2-y1) - (y-y1)*(x2-x1)) % 2

Counts 20,000 points vs a poly of 40 lines in 0.06 seconds on Pentium with 2.5Ghz. :)

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 14, 2009 at 19:41

culebrón

36.9k21 gold badges78 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dfilkovi Over a year ago

Is there any php algorithm that can use geometric mysql data and check if a point is within given polyline?

culebrón Over a year ago

You can extend the algorithm I've given for multiple polys. It's better to hand this job over to MySQL than to calculate it in PHP, but anyway you can see the article I've linked and re-write that piece of code in PHP.

Collectives™ on Stack Overflow

MySQL within geometry query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related