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,
from     hits
group by latitude,
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 
                        ST_GeomFromText(\'LineString('.$linestring.')\') ,
                  ) = 1
            GROUP BY i.coordinates
            HAVING  counter > 1
            ->setParameter('lastMonth', $lastMonth)
            ->setParameter('now', new \DateTime())
  • 1
    Why aren't you using GIS features? You should be storing points not latitude and longitude. Commented Aug 1, 2018 at 18:15
  • When you say within a polyline, are you meaning polygon? Or you mean on a MULTILINE? How are you storing whatever it is you're calling a "polyline" Commented Aug 1, 2018 at 18:16
  • 1
    As a side note, you should really never use MySQL anyway, but if you need GIS you should really really never use MySQL. Instead consider PostGIS/PostgreSQL Commented Aug 1, 2018 at 18:17
  • Also what version of MySQL. Commented Aug 1, 2018 at 18:40
  • @EvanCarroll Thanks for your swift comment, please check my edit!
    – 113408
    Commented Aug 1, 2018 at 19:10

2 Answers 2


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,

  ST_Buffer( ST_GeomFromWKB( ? ) , DISTANCE_IN_METERS ),

Where ? is the WKB output from PHP.

  • I've been able to get the Linestring on the php side however I don't see how I will use ST_CoveredBy to find the rows where I have points inside the converted Polygon. Can you please post an example?
    – 113408
    Commented Aug 2, 2018 at 15:54
  • 1
    @113408 updated, MySQL has the function named MBRCoveredBy (because they're stupid) Commented Aug 2, 2018 at 18:54
  • It took me some testing and time but I was able to do it at the end thanks to your help ! I edited my question with the solution as it's a bit different from the code you posted
    – 113408
    Commented Aug 3, 2018 at 9:24

I don't know much about MySQL spatial capabilities as I use PostgreSQL/PostGIS.

I think what you want is the MBROverlaps() and ST_PointFromText() functions.

So the idea is to create a point (spatial data type) with the latitude and longitude coordinates and place it on earth with the correct SRID (the coordinate system and projection). Then you check if there is an overlap between a point and a Polyline.

The SQL code would look like this:

select   latitude,
from     hits
AND MBROverlaps(ST_PointFromText('POINT(longitude latitude)', 4326), polyline_geom)
group by latitude,
having   count(*) > 1 

Take a look at the doc: https://dev.mysql.com/doc/refman/5.7/en/spatial-analysis-functions.html

  • It's a good attempt at an answer but I find it a little hard to believe that he's storing polygons in the database if he doesn't understand gis/points. Commented Aug 1, 2018 at 18:21
  • Indeed, we'll see how it goes! Commented Aug 1, 2018 at 18:26
  • MySQL 8.0+ supports ST_SRID(ST_Point(x,y),srid) dba.stackexchange.com/q/213813/2639 Commented Aug 1, 2018 at 18:53
  • @GobTron I'm confused on how to supply the polyline_geom parameter to the query. I get it from an API as an array of coordinates. Can you provide an example of what polyline_geom should look like ?
    – 113408
    Commented Aug 1, 2018 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.