1

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

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
M2sh
  • 741
  • 1
  • 11
  • 23

2 Answers2

0

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

Michael - sqlbot
  • 169,571
  • 25
  • 353
  • 427
  • thanks, but i have multiple points & wants to find all in my table like this query `SELECT id, asText(latlng) FROM points WHERE asText(latlng) in ('POINT(35.80684 51.427820000000004)','POINT(35.72343 51.303200000000004)')` can you suggest me a query for multiple points with MBRWithin ? – M2sh Oct 23 '13 at 18:15
-1

Try this:-

SELECT id, asText(latlng) FROM `points` WHERE asText(latlng) in ('POINT(35.80684 51.427820000000004)')
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • thanks, but does not return any records, i have changed the where clause to this `WHERE asText(latlng) in ('POINT(35.80684 51.427820000000004)')` and it works but is this query use spatial indexes for finding records? – M2sh Oct 20 '13 at 10:15
  • No, that query does not use spatial indexes. That query requires a full table scan. – Michael - sqlbot Oct 21 '13 at 11:59
  • @Michael-sqlbot i leave a comment to your answer plz see that, thanks – M2sh Oct 25 '13 at 10:13