where col in() clause for geometry column in mysql

问题:

i have a table with geometry(point) column data type, i stored data as POINT Object in mysql like this :

id      asText(latlng)

1   POINT(35.80684 51.427820000000004)

2   POINT(35.726940000000006 51.30407)

3   POINT(35.726940000000006 51.30407)

4   POINT(35.726940000000006 51.30407)

5   POINT(35.72343 51.303200000000004)

6   POINT(35.72675 51.303760000000004)

Now, i want to select and find some records by giving a list of POINTs and wants to use WHERE in() clause in sql syntax.

But this query is not working :

SELECT id, asText(latlng) FROM `points` WHERE latlng in (POINT(35.80684 51.427820000000004))

anybody can help me to resolve my problem?

thanks

回答1:

Try this:-

SELECT id, asText(latlng) FROM `points` WHERE asText(latlng) in ('POINT(35.80684 51.427820000000004)')

回答2:

The correct form of the query uses the MBRWithin() or MBRContains() geospatial functions in MySQL to actually compare the points and use a spatial index if available.

These functions can also compare dissimilar geospatial objects, to determine, for example, whether a point is within a linestring or polygon, using minimum bounding rectangles and R-trees.

SELECT id, asText(latlng)
  FROM points
 WHERE MBRWithin(latlng,POINT(35.80684, 51.427820000000004));

Live demo here: http://sqlfiddle.com/#!2/394d0a/1