MySQL Spatial Queries – Search Rows Within a Polyline

MySQLspatial

I'm stuck with the logic of a MySQL query where I need to find if a row exists where:

  1. The column longitude and latitude are within a Polyline
  2. The column hit is duplicated within the last month

I've been able to achieve the second point using :

select   latitude,
         longitude,
         count(*)
from     hits
WHERE datetime BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE()
group by latitude,
         longitude
having   count(*) > 1

but I can't figure out a way to only search for rows where the latitude and longitude are within a Polyline. Performance is a main factor here as the table hits can have millions of records.(can also do with a version 1 not so scalable/performant)

Any help would be much appreciated!

EDIT: I'm using MySQL 5.7 and I also have a column coordinates of type point (spatial type) also Polyline means :

A polyline is a list of points, where line segments are drawn between consecutive points

EDIT 2: Here is the query for people using php/symfony

$query = $this->em->createQuery('
            SELECT   count(i) as counter
            FROM     HitsBundle:Hit i
            WHERE i.datetime BETWEEN :lastMonth AND :now 
                  AND 
                  MBRCovers(
                    ST_Buffer( 
                        ST_GeomFromText(\'LineString('.$linestring.')\') ,
                        0.00001
                    ),
                    i.coordinates
                  ) = 1
            GROUP BY i.coordinates
            HAVING  counter > 1
        ')
            ->setParameter('lastMonth', $lastMonth)
            ->setParameter('now', new \DateTime())
            ->setMaxResults(1);

Best Answer

When Google says Polyline they're referring specifically to the Encoded Polyline Algorithm Format. That's a rendering format though. Essentially, you're rendering a point (with longitude, and latitude) as an ASCII string for easy API-transfer. That's not useful to you.

In GIS terms, what you need to do is take the Polyline from Google and construct a GIS LINESTRING from it. A GIS LINESTRING is a series of sequential points. For completeness in parlance, a line segment is a LINESTRING between just two points. A LINESTRING includes one or more line segements.

How do you get the Polyline to LINESTRING? There are many ways. For PostgreSQL with PostGIS that's easy because you have ST_LineFromEncodedPolyline which will do it. In MySQL you'll either have to do this in the app, in your case PHP, or write a very complex function (like ST_LineFromEncodedPolyline in PostGIS) to do it.

Personalized advice: in your case I would convert from PolyLine to WKB in PHP and then bring it into the database with ST_GeomFromWKB

Either way, after you have the LINESTRING you'll have to

Then in the end, it'll look something like this,

SELECT MBRCoveredBy(
  ST_Buffer( ST_GeomFromWKB( ? ) , DISTANCE_IN_METERS ),
  t.pt
)
FROM TABLE as t;

Where ? is the WKB output from PHP.

Related Question