2

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);
  • 1
    Why aren't you using GIS features? You should be storing points not latitude and longitude. – Evan Carroll Aug 1 '18 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" – Evan Carroll Aug 1 '18 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 – Evan Carroll Aug 1 '18 at 18:17
  • Also what version of MySQL. – Evan Carroll Aug 1 '18 at 18:40
  • @EvanCarroll Thanks for your swift comment, please check my edit! – 113408 Aug 1 '18 at 19:10
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,

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

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 Aug 2 '18 at 15:54
  • 1
    @113408 updated, MySQL has the function named MBRCoveredBy (because they're stupid) – Evan Carroll Aug 2 '18 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 Aug 3 '18 at 9:24
1

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,
         longitude,
         count(*)
from     hits
WHERE datetime BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE()
AND MBROverlaps(ST_PointFromText('POINT(longitude latitude)', 4326), polyline_geom)
group by latitude,
         longitude
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. – Evan Carroll Aug 1 '18 at 18:21
  • Indeed, we'll see how it goes! – Gob Tron Aug 1 '18 at 18:26
  • MySQL 8.0+ supports ST_SRID(ST_Point(x,y),srid) dba.stackexchange.com/q/213813/2639 – Evan Carroll Aug 1 '18 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 Aug 1 '18 at 19:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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