MySQL Forums - GIS

ST_Contains with multi polygon (no replies)

tabs den — Sun, 11 Dec 2022 09:42:34 +0000

Hello

I Have 2 type of data : point(car) & Polygons(Boundary of town)
I want to know if my car is in town.

My fonction work well but not when I have MultiPolygons for a Town
When a town has Two or more boundaries my fonction return false even the car is in one Boundary

SELECT name FROM town WHERE ST_Contains(town.geom,GeomFromText( 'Point( $x $y)' ))

Thanks

Mysql 5.7.27 ST_INTERSECTS Problem (1 reply)

Fantasy Black — Fri, 04 Nov 2022 16:33:24 +0000

There are 2 same geometry(Polygon) data in one table. I want to use 'ST_INTERSECTS' function with a bigger polygon to find proper data, but it dosen't work with error msg "Invalid GIS data provided to function st_intersects." and error code 3037.

ST_Transform (no replies)

Jasnoor Singh — Wed, 22 Dec 2021 10:36:14 +0000

When i am trying to transform from SRID 4326 to SRID 32643 it displays an error that transform to SRID 32643 is not supported.
Please help, I urgently need it.

Efficient Distance Querying in MySQL (no replies)

Edwin Desouza — Wed, 10 Nov 2021 16:58:59 +0000

https://aaronfrancis.com/2021/efficient-distance-querying-in-my-sql

https://tighten.co/blog/a-mysql-distance-function-you-should-know-about/

ST_SRID() flips coordinates for SRID 4326; ST_GeomFromText() doesn't (no replies)

Peter Brodersen — Thu, 18 Feb 2021 14:50:30 +0000

Hi,

When I use ST_SRID() with SRID 4326 (WGS84) the X and Y coordinates are flipped. I was wondering whether this is a bug or as intended.

In this example the X coordinate returned by ST_ASTEXT() (as well as with ST_X()) is 55 in all examples except for the last one.

SELECT
ST_ASTEXT(POINT(55,12)),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)')),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 25832)),
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 4326)),
ST_ASTEXT(ST_SRID(POINT(55,12),25832)),
ST_ASTEXT(ST_SRID(POINT(55,12),4326)),
ST_X(POINT(55,12)),
ST_X(ST_GeomFromText('POINT(55 12)')),
ST_X(ST_GeomFromText('POINT(55 12)', 25832)),
ST_X(ST_GeomFromText('POINT(55 12)', 4326)),
ST_X(ST_SRID(POINT(55,12),25832)),
ST_X(ST_SRID(POINT(55,12),4326))
\G

*************************** 1. row ***************************
ST_ASTEXT(POINT(55,12)): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)')): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 25832)): POINT(55 12)
ST_ASTEXT(ST_GeomFromText('POINT(55 12)', 4326)): POINT(55 12)
ST_ASTEXT(ST_SRID(POINT(55,12),25832)): POINT(55 12)
ST_ASTEXT(ST_SRID(POINT(55,12),4326)): POINT(12 55)
ST_X(POINT(55,12)): 55
ST_X(ST_GeomFromText('POINT(55 12)')): 55
ST_X(ST_GeomFromText('POINT(55 12)', 25832)): 55
ST_X(ST_GeomFromText('POINT(55 12)', 4326)): 55
ST_X(ST_SRID(POINT(55,12),25832)): 55
ST_X(ST_SRID(POINT(55,12),4326)): 12
1 row in set (0.00 sec)

This might be based on an idea that WGS84 is usually written as Latitude,Longitude, roughly corresponding to Y,X instead of X,Y as mentioned here:
"Coordinate system: Ellipsoidal 2D CS. Axes: latitude, longitude. Orientations: north, east. UoM: degree"
https://epsg.io/4326

However the manual specifically states:
"ST_SRID() changes the geometry SRID value without transforming its coordinates."
https://dev.mysql.com/doc/refman/8.0/en/gis-general-property-functions.html#function_st-srid

I get that "transformed" might only refer to transformation due to different spatial reference systems, however flipping the X and Y coordinate also seems like a transformation.

In any case it seems weird that the flip (if it is an intended feature) only happens when using ST_SRID() and not when specifying the same specific SRID as an argument for ST_GeomFromText()

It might be related to this bug report:
https://bugs.mysql.com/bug.php?id=94382

- Peter Brodersen

`ST_DISTANCE_SPHERE` different output in 5.7 and 8.0 (no replies)

Matan Yadaev — Sun, 07 Feb 2021 19:22:03 +0000

I write an open-source library that exposes an API to MySQL GIS functions from Laravel applications.
I run the library's tests on both MySQL 5.7 and 8.0, everything works fine except `ST_DISTANCE_SPHERE` - this function outputs different values for each MySQL version.

```sql
SELECT ST_DISTANCE_SPHERE(POINT(55.5, 23.1), POINT(55.51, 23.1));
# MySQL 5.7: 1022.792591459987
# MySQL 8.0: 1022.7925914593363
```

The difference begins in the tenth digit after the zero.

Any idea why it happens and how can I fix it?

Distance between POINT and LINESTRING (5 replies)

Omar Garro — Sat, 05 Dec 2020 16:17:08 +0000

Hi,
how can I run a query to calculate the minimum distance between a POINT(lat lng) and a LINESTRING and the filter results where that distance is less than xxx meters ?

I am trying with:

SELECT ST_Distance(
ST_GeomFromText( 'POINT(-34.647958 -58.388129)', 3857),
ST_GeomFromText( geometry_fld, 3857)
) as Distance2
FROM `sec_cab_fo`
HAVING Distance2 < 200

But it seems to be returning bad inaccurate results

gemotry_fld is a Linestring field. It contains something like this:

LINESTRING (
-34.5976965433552 -58.483349327765,
-34.5976879214475 -58.4832948928198,
-34.5978127817356 -58.4828331022848,
-34.5975919709953 -58.4829059127938,
-34.5974830787817 -58.4832033974371,
-34.5966875141324 -58.4839039527997,
-34.5966531026929 -58.4838060055857,
-34.5966888038735 -58.4836719190874,
-34.5966522003641 -58.4836282139431,
-34.5968243496615 -58.4833544201464,
-34.5966164990581 -58.4833768839409
)

My version of mysql is 8.0.21

MySQL 8: ST_Interects works very strange (1 reply)

Борис Мартиросов — Mon, 31 Aug 2020 22:17:14 +0000

Hi!
Since upgrading from Mysql 5.7 to 8.0.
I'm totally confused on how spatial functions work!
Look:
SELECT
st_intersects(
ST_GeomFromText(
'POLYGON((-90 -179, -90 180, 90 180, 90 -179, -90 -179))', 4326),
st_geomfromtext('POINT(44.041870130401 42.869292509262)', 4326)
) as result

returns result = 0! Which is wrong IMO.

but SELECT
st_intersects(
ST_GeomFromText('POLYGON((-90 -179, -90 180, 90 180, 90 -179, -90 -179))', 0),
st_geomfromtext('POINT(44.041870130401 42.869292509262)', 0)
) as result
gives result = 1.

How come? And How sholud I fix my query for it to work correctly with 4326 SRID?

MySQL 8.0: GeoSpatial data handling made easy (no replies)

Edwin Desouza — Sun, 03 May 2020 21:38:15 +0000

https://mydbops.wordpress.com/2020/05/03/geospatial-data-handling-made-easy-with-mysql-8-0/

Three Dimensional Points in WKB (no replies)

Ephraim Khantsis — Sat, 25 Apr 2020 20:47:35 +0000

It seems that MySQL does not yet support Z axis in WKB. Is there any way I can track when this will arrive?

Shape File import in mysql Database (2 replies)

Shreyas Kumar — Mon, 13 Apr 2020 16:52:05 +0000

HI, i want to load shapefile in mysql db. Is there any inbuilt tools or utility that can import shapefile data in table.? don't wnat to use any third part tools.

MySQL 8.0 GIS Units of Measure - Meter, foot, Clarke's yard, or Indian Foot (no replies)

Edwin Desouza — Mon, 29 Apr 2019 16:42:03 +0000

https://elephantdolphin.blogspot.com/2019/04/mysql-80-gis-units-of-measure-meter.html

MySQL 8.0 GIS -- Inserting Data & Fun Functions (no replies)

Edwin Desouza — Wed, 03 Apr 2019 03:18:41 +0000

MySQL 8.0 GIS -- Inserting Data & Fun Functions
https://elephantdolphin.blogspot.com/2019/04/mysql-80-gis-inserting-data-fun.html

MySQL 8.0 Geographic Information Systems (GIS) - How to get the distance between two cities (no replies)

Edwin Desouza — Wed, 27 Mar 2019 16:56:02 +0000

MySQL 8.0 Geographic Information Systems (GIS) - How to get the distance between two cities

https://elephantdolphin.blogspot.com/2019/03/mysql-80-geographic-information-systems.html

geometry number length limited to 6,how to change? (no replies)

ysu whl — Sun, 20 Jan 2019 03:46:46 +0000

when I insert a geometry ，the number in geometry be limited to 6 length。
I input：select POINT(123456.78,1234.5678)
I got:POINT(123457 1234.57)
My spatial reference is customed，and I need to store longer numbers in the geometry，how can i change the limited of the number length？
thank you very much!

The best way to locate, in MySQL 8 GIS (no replies)

Edwin Desouza — Thu, 08 Nov 2018 00:13:16 +0000

https://medium.com/maatwebsite/the-best-way-to-locate-in-mysql-8-e47a59892443

Upgrading to MySQL 8.0 with Spatial Data (no replies)

Edwin Desouza — Mon, 02 Jul 2018 15:22:05 +0000

https://mysqlserverteam.com/upgrading-to-mysql-8-0-with-spatial-data/

Detecting Incompatible Use of Spatial Functions before Upgrading to MySQL 8.0 (no replies)

Edwin Desouza — Thu, 28 Jun 2018 19:29:38 +0000

https://mysqlserverteam.com/detecting-incompatible-use-of-spatial-functions-before-upgrading-to-mysql-8-0/

Geo Coordinates calculating (1 reply)

Rainer Parzer — Tue, 26 Jun 2018 13:41:45 +0000

Hi

First of all ... I'm new to mysql :)

And here is my "Problem".

Lets say i have 5 tables (in reality a lot more).
Each table contains an ID , Latitude and Longitude (in decimal format).
The Tables gets updated every 30 seconds .... so the actual position is always in the last line of the table.
And there is also a timestamp in each line.

Table1:
'123456','10.34411','18.12345','2018-06-15 16:19:16'
'123456','10.34411','18.12345','2018-06-15 16:19:30'
'123456','10.34411','18.12345','2018-06-15 16:29:48'
'123456','10.34411','18.12345','2018-06-15 16:34:45'
'123456','10.34411','18.12345','2018-06-15 16:35:22'

Table2:
'654321','12.34411','15.12345','2018-06-15 16:19:16'
'654321','12.34411','15.12345','2018-06-15 16:19:30'
'654321','12.34411','15.12345','2018-06-15 16:29:48'
'654321','12.34411','15.12345','2018-06-15 16:34:45'
'654321','12.34411','15.12345','2018-06-15 16:35:22'

Table3:
'567891','17.34411','28.12345','2018-06-15 16:19:16'
'567891','17.34411','28.12345','2018-06-15 16:19:30'
'567891','17.34411','28.12345','2018-06-15 16:29:48'
'567891','17.34411','28.12345','2018-06-15 16:34:45'
'567891','17.34411','28.12345','2018-06-15 16:35:22'

what i would like to is:
calculate the distance from the most recent entry from 1 to 2 to 3 and from 2 to 1 to 3 and from 3 to 1 to 2 and so on

Thanks a lot for helping
rainer

POINT index is used in v5.7 but not in v8.0 (1 reply)

J C — Tue, 12 Jun 2018 07:18:13 +0000

Dear MySQL Forum users,

I am encountering a problem with using spatial indices. I have a table with 2 rows: id and location. Location is a POINT type, NOT NULL.

The CREATE code is as follows:
******************************
CREATE TABLE `test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`location` POINT NOT NULL,
`location2` GEOMETRY NOT NULL,
INDEX `id` (`id`),
SPATIAL INDEX `location` (`location`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=16384
;
******************************

Then, running the following query yields different results in MySQL v5.7 and v8.0:

******************************
EXPLAIN SELECT * FROM test WHERE MBRContains(ST_GeomFromText('LINESTRING(4.848402825923472 51.985867267867974,5.562753957891971 52.205296088730535)', 4326), location) LIMIT 1;
******************************

In v5.7 it works fine and the output is
"1" "SIMPLE" "test" \N "range" "location" "location" "34" \N "1" "100,00" "Using where".

However, in v8.0, the entry for possible_keys is (null). Why is this the case? I would like to use an index to speed up my query.

Thanks in advance!

(Sorry if I posted this in the wrong part of the forum)

MySQL 8.0: GIS Creating Your Own Spatial Reference Systems (no replies)

Edwin Desouza — Fri, 01 Jun 2018 15:04:06 +0000

MySQL 8.0: GIS Creating Your Own Spatial Reference Systems
https://mysqlserverteam.com/creating-your-own-spatial-reference-systems-in-mysql-8-0/

MySQL 8.0: GIS Upgrading Spatial Indexes (no replies)

Edwin Desouza — Wed, 30 May 2018 19:30:55 +0000

https://mysqlserverteam.com/upgrading-spatial-indexes-to-mysql-8-0/

MySQL 8.0: GIS Projected Spatial Reference Systems (no replies)

Edwin Desouza — Mon, 28 May 2018 21:04:40 +0000

MySQL 8.0: GIS Projected Spatial Reference Systems
https://mysqlserverteam.com/projected-spatial-reference-systems-in-mysql-8-0/

MySQL 8.0: GIS Geographic Spatial Reference Systems (no replies)

Edwin Desouza — Fri, 25 May 2018 16:20:32 +0000

MySQL 8.0: GIS Geographic Spatial Reference Systems
https://mysqlserverteam.com/geographic-spatial-reference-systems-in-mysql-8-0/

MySQL 8.0: GIS Geographic Indexes in InnoDB (no replies)

Edwin Desouza — Fri, 04 May 2018 16:44:25 +0000

MySQL 8.0: GIS Geographic Indexes in InnoDB
https://mysqlserverteam.com/geographic-indexes-in-innodb/

MySQL 8.0: GIS Axis Order in Spatial Reference Systems (no replies)

Edwin Desouza — Thu, 03 May 2018 16:50:32 +0000

https://mysqlserverteam.com/axis-order-in-spatial-reference-systems/

MySQL 8.0: GIS Geography (no replies)

Edwin Desouza — Wed, 02 May 2018 15:20:05 +0000

https://mysqlserverteam.com/geography-in-mysql-8-0/

MySQL 8.0: GIS Spatial Reference Systems (no replies)

Edwin Desouza — Mon, 30 Apr 2018 14:48:41 +0000

MySQL 8.0: GIS Spatial Reference Systems
https://mysqlserverteam.com/spatial-reference-systems-in-mysql-8-0/

How do you handle unknown POINT values in MySQL? (no replies)

Peter B — Thu, 29 Mar 2018 22:26:23 +0000

I posted this in the InnoDB forum, but I think this is probably a better place for my question.

MySQL 5.7 introduced Spatial Indexes on POINT columns. Apparently, you cannot have NULL values in a spatial index. At the same time, you cannot use a DEFAULT for POINT columns.

So how do you handle the case where the value for POINT is unknown?

I geocode every address in my database, but if the end user doesn't supply an address, what should I put there?

In another question, someone suggested placing them at the North Pole POINT(0.0000,90.0000) but this takes 25 bytes for each unknown address.

Is there a way to resolve this?

FYI: I am not a DBA.

MySQL 8.0 GIS Overview (no replies)

Edwin Desouza — Sat, 10 Mar 2018 05:43:01 +0000

https://www.slideshare.net/NorvaldRyeng/mysql-80-gis-overview