使用列名作为mysql中POINT几何的参数

Question

I am working on a query which fetches all the rows in which lat and longs are inside given polygon. I am using geometry functions to do so. I am able to use contain method with polygon and point, However In my case I am using polygon coordinates explicitly in query and want to use "latitude" and "longitude" column name as parameters of POINT. I tried to use concat method but I am getting a "false" return from contain method.

query:

  SELECT 
    * 
  from table  
  WHERE 
    contains(
       GeomFromText(
         'POLYGON(
            (32.717399 -117.144126,32.714655  -117.136487,32.710827 -117.145843,32.714005 -117.146444, 32.717399 -117.144126))
         '),
       GeomFromText(
         'POINT(
            concate(latitude," ",longitude))
         '))

My table structure is:

property_id                 int(11)
latitude                    decimal(10,6)    
longitude                   decimal(10,6)
Address_f1365               char(100)
AddressOnTheInternet_f27    char(255)

Answer 1

Try something like this, before using in actual query:

SELECT asWKT(GeomFromText(CONCAT('POINT(', 15, ' ', 26, ')')));

Shows:

'POINT(15 26)'

I suggest you to test anything, that would be part of spatial data query with function asWKT() / asText(). Check the manual for supported formats and conversion functions.

You have wrongly used CONCAT(). Try:

SELECT 
    * 
FROM
    `table`
WHERE 
    CONTAINS(
       GeomFromText('POLYGON((32.717399 -117.144126,32.714655  -117.136487,32.710827 -117.145843,32.714005 -117.146444, 32.717399 -117.144126))')
    ,
       GeomFromText(CONCAT('POINT(', latitude, ' ', longitude, ')'))
    )