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);
113408
  • 135
  • 6

2 Answers2

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.

Evan Carroll
  • 65,432
  • 50
  • 254
  • 507
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

TurboGraphxBeige
  • 166
  • 6