2

I'm trying to work with some data from Google Maps and have a few locations along with their bounding boxs (northeast, southwest) data stored.

Using that data, I have the following query...

SELECT
    * 
FROM
    `address_components` 
WHERE
    MBRContains (
        ST_MakeEnvelope (
            ST_GeomFromText ( 'POINT(39.643950680292 -79.981536819708)', 4326 ),
            ST_GeomFromText ( 'POINT(39.641252719708 -79.984234780292)', 4326 )
        ),
        `location`
    );

The problem I'm running into with this though is that it's saying:

3618 - st_makeenvelope(POINT, POINT) has not been implemented for geographic spatial reference systems.

How can I work around this issue?

Additionally, how would I change this so that instead of hard-coding my bounding box, I can select a row based on a place_id and then find all records that its location falls within the envelope of?

Improve this question

1 Answer 1

Reset to default
1

There is clearly no work around for this issue.

  • MySQL can't do this operation (and many others) on a geographic srs.
  • You can't reproject in MySQL.

Oracle MySQL doesn't want to compete with Oracle Spatial. So you're SOL. MariaDB is currently behind MySQL in GIS too and it's a low-priority item for them. Either reproject to something Web Mercator and do Cartesian bounding box stuff, or PostgreSQL and PostGIS, which can do geographic bounding boxes, and on an index at that. And, also ST_Transform from one SRS to another.

PostGIS is the goto solution for GIS.

Improve this answer
4
  • Why do you say there is "clearly no work around"? From my understanding, ST_MakeEnvelope() just takes two points and creates a polygon. I'm not very good at math, but I'm pretty sure this is a simple mathematical equation that could be placed in a stored function until MySQL gets around to implementing it. MySQL has spatial indexes and they're working on adding support for geographic SRIDs. "As of 8.0.11 there are a few spatial functions that don’t yet support geography"
    – user1960364
    Commented Jan 18, 2019 at 20:01
  • Because that would create a polygon suitable for Cartesian math. You connect two points on a sphere with an arc, and a bounding box on a sphere is different from a bounding box on a 2d-projection of a sphere. You should look at a geography envelope from PostGIS stackoverflow.com/questions/26743578/… Ideally you don't have to worry about this, it would just work. But it's MySQL.
    – Evan Carroll
    Commented Jan 18, 2019 at 20:45
  • Sorry, perhaps polygon wasn't the right word. I know it creates a curve since it's forming around a sphere. My point was that it's still based on mathematics so there should be a way to create a stored procedure that does the same, right? Granted, it probably wouldn't be as performant, but I do believe they intent to add this functionality into the core from what I've seen in MySQL 8 thus-far.
    – user1960364
    Commented Jan 18, 2019 at 20:56
  • You can do anything with stored procedures because they're turing complete. But this would be no fun. I doubt MySQL will ever get them. Boost doesn't yet them, and MySQL is using Boost. And, even if Boost does get them, it's not likely to be high on Oracle's priority list. A few people are working on Boost, github.com/boostorg/geometry
    – Evan Carroll
    Commented Jan 18, 2019 at 21:07

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.