sjaakp / yii2-spatial Goto Github PK

Hi,
as you can read at https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-0.html#mysqld-8-0-0-spatial-support the mysql functions X(), Y() are GeomFromText() are removed. The new functions are called ST_X(), ST_Y() and ST_GeomFromText().

Is it possible to add support for mysql 8?

Greetings Chris

Firstly thanks great library.

When i run a ->nearest() on my new spatialmodel, I get the following SQL created, but it causes an error in MySQL 5.7.25:

SELECT *, 111.045*DEGREES(ACOS(COS(RADIANS('-34.06176630'))*COS(RADIANS(_lat))*COS(RADIANS('18.82098640')-RADIANS(_lng))+SIN(RADIANS('-34.06176630'))*SIN(RADIANS(_lat)))) AS _d, AsText(geo_point) AS geo_point FROM (SELECT *, X(geo_point) AS _lng, Y(geo_point) AS _latFROMlocation HAVING (_lng BETWEEN 17.733952878026 AND 19.908019921974) AND (_latBETWEEN -34.962302118812 AND -33.161230481188))0HAVING_d< 100 ORDER BY_d``

Hi, Thanks for such a useful extension!!

I am using this exact query:

$crowds = Crowd::find()
            ->joinWith('reports')
            ->select(['crowd.*', 'report.created_at'])
            ->andWhere(['>=', 'reports_count', Crowd::$REPORTS_TRUST_NUMBER])
            ->andWhere(['>=', 'report.created_at', new Expression('DATE_SUB(NOW(), INTERVAL 12 HOUR)')])->all();

and I am getting an "Spatial attribute not converted" error, Crowd is an instance of sjaakp\spatial\ActiveRecord and my find() function is returning an instance of sjaakp\spatial\ActiveQuery

For making it simpler, we can reduce the problematic query to:

$crowds = Crowd::find()
            ->joinWith('reports')
            ->select(['crowd.*'])->all();

I need to use select() because both crowd and report tables have a location field and if select() is not used I get a "Integrity constraint violation" because location field is ambiguous.

Good work ! Any chance we get similar extension to be used with a PostgreSQL/Postgis database ?

There is a problemin function nearest (file ActiveQuery.php): in Haversine formula when the lat/lng specified in the query matches EXACTLY the lat/lng of a record in the database, some times returns NULL and not 0 for distance _d
The problem is that acos is invoked with a value which is not in the range of -1 and 1, so it returns null.
This is the solution:
replace
'_d' => "{$lenPerDegree}*DEGREES(ACOS(COS(RADIANS(:lt))*COS(RADIANS(_lat))*COS(RADIANS(:lg)-RADIANS(_lng))+SIN(RADIANS(:lt))*SIN(RADIANS(_lat))))" // Haversine

with
'_d' => "{$lenPerDegree}*DEGREES( IFNULL(ACOS(COS(RADIANS(:lt))*COS(RADIANS(_lat))*COS(RADIANS(:lg)-RADIANS(_lng))+SIN(RADIANS(:lt))*SIN(RADIANS(_lat))),0))" // Haversine

I have found the solution here:
https://stackoverflow.com/questions/28254863/mysql-geospacial-search-using-haversine-formula-returns-null-on-same-point

The Haversine formula is nice and fast, but it has a ‘fatal’ flaw of returning NULL sometimes when measuring the distance between a point and itself. About 3% of latitudes have this problem; example: 60.5544. Any longitude works, because it cancels out. About 6% compute that the distance from a point to itself is a few meters.

The Vincenty formula is a more numerically stable version of the Haversine formula for computing great circle distances. The question of numerical stability comes up specifically when the distances between points are small. In those cases the cosine is very close to 1, so the inverse cosine function is not as precise as the inverse tangent function used in this formula.
For Vincenty formula see: http://www.plumislandmedia.net/mysql/vicenty-great-circle-distance-formula/

So you can implement the Vincenty formula.

Thank you for this helpful extension!
Just a proposal: Why not put all the code from sjaakp\spatial\ActiveRecord into a class sjaakp\spatial\SpatialTrait (similar to how it's done here: https://demos.krajee.com/tree-manager - see Step 2: Setup Model) and then use this trait in a spatial model?
The reason: In my project most of the models extend from a custom class BaseActiveRecord but not all of them should be spatial. So in order to use sjaakp\spatial\ActiveRecord only in the spatial models I would have to implement an additional class SpatialBaseActiveRecord that extends from sjaakp\spatial\ActiveRecord and provides all the functionality of my genuine BaseActiveRecord. That could be done much easier with a trait. Or am I missing something?
What do you think?

By default Yii2 appends the table name (alias) to the query selection when using a join - “table name. * ".
Please add table name (alias) to support joins

Hi,

Some short example would be very helpful!

Thanks a lot!
Toby