Unable to run within when different SRID generated

[rawaludin]

I've table like this:

CREATE TABLE `jovan_test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `properties__name` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.name'))) VIRTUAL,
  `properties__age` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.age'))) VIRTUAL,
  `properties__height` int(11) GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.height'))) VIRTUAL,
  `properties__address__city` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.city'))) VIRTUAL,
  `properties__address__zip_code` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.zip_code'))) VIRTUAL,
  `properties__address__state` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.state'))) VIRTUAL,
  `properties__address__country` varchar(255) COLLATE utf8_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`__docs`,'$.properties.address.country'))) VIRTUAL,
  `properties__address__home_coordinate` point GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.home_coordinate')))) VIRTUAL,
  `properties__address__routes` geometry GENERATED ALWAYS AS (st_geomfromgeojson(json_unquote(json_extract(`__docs`,'$.properties.address.routes')))) VIRTUAL,
  `__docs` json NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `jovan_test_properties__name_index` (`properties__name`),
  KEY `jovan_test_properties__age_index` (`properties__age`),
  KEY `jovan_test_properties__height_index` (`properties__height`),
  KEY `jovan_test_properties__address__city_index` (`properties__address__city`),
  KEY `jovan_test_properties__address__zip_code_index` (`properties__address__zip_code`),
  KEY `jovan_test_properties__address__state_index` (`properties__address__state`),
  KEY `jovan_test_properties__address__country_index` (`properties__address__country`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Insert new value like this:

insert into jovan_test set __docs = '{"properties": {"age": 29, "name": "Rahmat Awaludin", "height": 160, "address": {"city": "Bandung", "state": "Jawa Barat", "routes": {"type": "LineString", "coordinates": [[102, 0], [103, 1], [104, 0], [105, 1]]}, "country": "Indonesia", "zip_code": "43193", "home_coordinate": {"type": "Point", "coordinates": [30, 10]}}}}'

With resulting like this:

mysql root@localhost:intelligence> select id, st_astext(properties__address__home_coordinate) from jovan_test;
+----+-------------------------------------------------+
| id | st_astext(properties__address__home_coordinate) |
+----+-------------------------------------------------+
| 2  | POINT(30 10)                                    |
+----+-------------------------------------------------+

The within query generated query like this:

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))"))

It silently error on mysql (I don't get error message in laravel), the message like this:

Binary geometry function st_within given two geometries of different srids: 4326 and 0, which should have been identical.

This because ST_GeomFromGeoJson generate SRID 4326 by default see here if none provided.

The srid argument, if given, must be a 32-bit unsigned integer. If not given, the geometry return value has an SRID of 4326.

While ST_GeomFromText generate SRID 0 by default if not provided see here.

This can be solved by using SRID to 0 on first ST_GeomFromGeoJson call. That is possible with this library.

But, I believe allowing set SRID on scopeWithin will work best. So the resulting query would be

select * from `jovan_test` where st_within(`properties__address__home_coordinate`, ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))",4326))

This sinergy with allowing SRID on creating column as you alredy done.

What do you think? I can prepare a PR if you want.

[grimzy]

Thank you for reporting this.

I agree, we have to handle SRIDs when fetching and comparing spatial data.
As you mentioned, to fix this issue we should update how all fields types are represented in queries, for example:

ST_GeomFromText("POINT(0 -10)")
ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))")

become:

ST_GeomFromText("POINT(0 -10)", 4326)
ST_GeomFromText("POLYGON((40 -10,-10 -10,-10 40,40 40,40 -10))", 4326)

(or an SRID different than 4326, depending on what is stored)

I think this issue is also related to #47 (how the package fetches the binary data from the database).

And yes, PRs are greatly appreciated! And unit tests to fasten a release event more! 👍

[grimzy]

I'm working on a PR for this task srid.

[kotasjan]

So if I comprehended it well, there is currently no option how to save POINT object with SRID different than 0. Am I right? I was trying to find a way for a week without success. When I tried to use SRID = 0 all the spatial functions returned me really weird data so I assume it is caused by this. :(

[sikhlana]

@grimzy is there any update on adding support for SRID?

[grimzy]

@sikhlana To be honest, no.

Currently we do this: https://github.com/grimzy/laravel-mysql-spatial/blob/master/src/Types/Geometry.php#L55:

public static function fromWKB($wkb)
    {
        // mysql adds 4 NUL bytes at the start of the binary
        $wkb = substr($wkb, 4);
        $parser = new Parser(new Factory());
        return $parser->parse($wkb);
    }

Literally: we remove the srid info from the WKB, then parse it. This code is to make the parser "compatible" with MySQL's WKB (meh... it just uses the default: 4326).

In any case, I've tried other parsers, tried to decode myself; but no dice.

Any help is greatly appreciated!!! Last time I pushed something for this was in the srid branch. I think the rest of the commits in that branch have every other change needed.

[grimzy]

@kotasjn sorry :(

[sikhlana]

@grimzy I'm checking if integrating with https://github.com/libgeos/php-geos does any trick. Will let you know.