Questions tagged [mysql-spatial]
MySQL spatial refers to the Extensions for Spatial Data, that enable the generation, storage, and analysis of geographic features.
46
questions
1
vote
1answer
117 views
MySQL 5.7 and how to store WGS 84 Coordinates
I want to store WGS84 coordinates in a MySQL 5.7 database. After much reading, I think I should use the POINT datatype with a SPATIAL INDEX. But I am very confused about SRSs, SRIDs, lat/long vs long/...
0
votes
0answers
15 views
Selecting routes in MySQL table that intersect with a given bounding box?
I have a MySQL table, that stores routes. The table stores the NorthEast and SouthWest coordinates of the route bounding box as POINT type. Also it stores 10 key coordinates along the route as a ...
0
votes
0answers
23 views
Initialize spatial_ref_sys table in MySQL 5.7
Using MySQL 5.7 for a spatial application and kind of baffled as to why there doesn't seem to be any nice way to prepopulate spatial_ref_sys. Isn't there any premade SQL script out there to fill the ...
1
vote
0answers
59 views
Show MySQL vector data on QGIS canvas
I am a novice to Python and QGIS coding. I am facing an issue while showing the MySQL spatial data on QGIS canvas. Database connection details are below:
database name:- mysql
host :- localhost
port :...
1
vote
1answer
300 views
MySQL 8.0 Error Code: 3617. Latitude is out of range in function st_geomfromtext
First I have a problem creating spatial index in MySQL 8.0. Here the description:
I have a collection of several thousand species range maps (polygon & multi-polygon) which is provided by IUCN as ...
0
votes
0answers
35 views
How can I confirm the maximum size of a polygon for acurate use in a geometry column?
Suppose I have a table with a geometry column containing polygons created from points of lat and long values (SRID: 4326). Given an arbitrary lat and long location (again SRID: 4326) I want to query ...
0
votes
1answer
109 views
MySQL spatial slowed after upgrade to 8
After I updated MySQL from 5.7 to 8, spatial functions have become very slow. Specifically, this query:
SELECT points3_.track_id AS track_id
FROM waypoint points3_
WHERE ST_CONTAINS(ST_POLYFROMTEXT(...
2
votes
0answers
676 views
Find all points near a point within a radius MySql 8.0 (SRID 4326)
This question is targeted to MySQL 8.0 with its new spatial features.
Given the following table:
CREATE TABLE `places` (
`id` INT(11) UNSIGNED NOT NULL,
`location` POINT NOT NULL SRID 4326,
...
1
vote
0answers
196 views
What is geometry(4326), and can you do Cartesian calculations with unprojected ellipsoidal coordinates?
I know Geography(4326) is an unbounded, and unprojected, and covers the entire world with ellipsoidal coordinates. I get that the math on this system is done on a spheroid. I also get that with a ...
1
vote
0answers
143 views
Result of ST_Contains gives false positive
I am trying to figure out the state that Berlin is in. So now my query SELECT * FROM states WHERE ST_Contains(Geo, Point(13.383068, 52.530644)); return two results. One for Berlin which is correct, ...
2
votes
0answers
313 views
Optimizing MySQL query to select all points with in a large polygon using spatial indexes
Re-posting it from stackoverflow as per their suggestion. Link to the thread in stackoverflow https://stackoverflow.com/questions/53182385/optimizing-mysql-query-to-select-all-points-with-in-polygon-...
1
vote
0answers
111 views
Build a tile server using MySQL as backend
I need to set up a tile server using MySQL but cannot find any documentation for it.
I have done it with Postgres by following this link and it works fine.
I understand that after downloading the ...
1
vote
0answers
399 views
Find single nearest point of point in mysql
I have an app that formerly used PostgreSQL with PostGIS but now the environment changes and I have to use MySql.
In my app i had a database query that searches the nearest point to a point. Note ...
1
vote
0answers
97 views
MySQL ST_Contains returning empty recordset?
Using MySQL 5.6, I have converted lat and lng fields into Points - using AsText, I can see this is OK.
I have another table to hold the polygon data, and again using AsText, I can see the correct ...
0
votes
1answer
448 views
mySQL Spatial ST_contains does not include all Results [closed]
I when I run the first command, mySQL 5.7 only shows a sub-collection of all results. Only when I run the 2nd command I get the correct answer.
The problem with the 2nd command: I cannot use the ...
15
votes
1answer
2k views
Where does the default Earth radius in ST_Distance_Sphere come from?
MySQL says in the docs for ST_Distance_Sphere
Calculations use a spherical earth and a configurable radius. The optional radius argument should be given in meters. If omitted, the default radius is ...
1
vote
1answer
313 views
Grouping database results based on intersection using MySQL GIS?
I am using MySQL GIS (5.7.18).
I have a number of locations in the database. I also have a number of other polygons (not in the database).
I would like to perform a single query, intersecting all ...
0
votes
1answer
2k views
Hibernate and Mysql gives “Geometry byte string must be little endian.” error
I'm using mysql and wildly.
I want to look for points in a given polygon. My method looks like:
public Response getGeoJsonByBox(
@QueryParam("topLeftX") double ...
2
votes
1answer
4k views
Convert lat/lon mysql columns to geometry, check if point is in polygon, return polygon id
I have a mysql database table with many ids, each id has a "start lat", "start lon" "end lat" and "end lon" column.
I want to check in wich polygon the start point belongs (and write this in a "start ...
3
votes
0answers
80 views
In MySQL are there unidirectional and directional graphs?
I'm new to MySQL and studying about geospatial data. I have read the documentation of MySQL and learned about the ST_Geometry type, its subtypes and its functions; but I didn't find anything about ...
1
vote
0answers
386 views
Using Google Maps, layers of polygons and calculate its intersections [closed]
I am developing a web application that uses Google Maps and it has polygons (circles and irregular polygons) drawed inside this map.
This polygons are stored into a DataBase (MySQL 5.7) as Polygons ...
2
votes
1answer
1k views
Searching for a point within a multipolygon
I'm performing a mySQL query to check if a point is within a multipolygon field. I've got a database field storing a multipolygon and I want to check if a point is within any of those polygons. ...
4
votes
3answers
3k views
Unable to open datasource 'MySQL' using ogr2ogr
I am trying to dump shapefile data into MySQL using GDAL ogr2ogr command
ogr2ogr –f MySQL MySQL:SHP_DB,host=localhost,user=root,password=root feature_object.shp -nln phil_custom -update -overwrite -...
1
vote
1answer
72 views
Why I obtain different values calculating distance between two point in the space using this “Great-circle distance” formula and Google Earth tool?
I am not so into database and GIS and I have the following doubt about this function used on a MySql database to calculate the distance between 2 points.
I am started from this tutorial where there ...
4
votes
2answers
2k views
MySQL normalizing points with latitude > 90
Facing very strange problem, I've points table for storing location of users in location(POINT) column. Problem with st_distance_sphere function where I've stored some specific location.
update ...
2
votes
0answers
170 views
Query WKT from BLOB in mysql
I am trying to query a table in MySQL which contains spatial data. Out of all the columns, I have one column called "pt". Each entry in this field is specified as 'BLOB'. In my MySQL workbench 6.3,...
0
votes
1answer
4k views
MySql select points contained in polygon
I am extremely new to mysql, but I cannot find the error in this query
SELECT latitude, longitude from mytable where ST_CONTAINS(ST_GEOMFROMTEXT('POLYGON((47 -67, 47 -123, 21 -78, 47 -67)'), point(...
2
votes
0answers
432 views
Find closest points between two tables in MySQL with Spatial extensions
I have a two tables: one with trackings points and one with shops.
I want the trackings points that have 1 or more shops within a certain distance from that point.
What I have now:
SELECT
a.*,
b....
1
vote
1answer
177 views
Is there a way of doing ST_SnapToGrid in MySql?
Given that MySQL 5.7 has spatial data types
Is there an equivalent to PostGIS's ST_SnapToGrid in MySQL?
Looking at the reference:
http://dev.mysql.com/doc/refman/5.7/en/spatial-function-reference....
1
vote
0answers
70 views
Are GIS tools suitable for physical capacity and placement management?
I'm a complete newbie to GIS systems, I've been watching some introductory videos about GIS but I'm not yet sure if GIS tools are the right tool for solving my problem.
I have a database with ...
5
votes
1answer
568 views
Creating line-strings from points in MySQL
I am extracting the lat/long information from Geo-tagged images and storing them in MySQL. I want to get a line-string from the points, I looked into the GROUP_CONCAT() function but I am not able to ...
1
vote
1answer
670 views
Mysql 5.5 spatial to Google Maps API
I've got a mysql table with points and polygons spatial geometries.
I can see that MySQL 5.5 doesn't have the ST_AsGeoJSON() function to extract to GeoJSON.
I'm looking for the simplest way to ...
2
votes
2answers
3k views
Can't add POINT NOT NULL column to MySQL table
I recently upgraded to MySQL 5.7.9 & am using MyISAM for my table engine.
Adding a new POINT column like so:
ALTER TABLE <table> ADD centroid POINT NOT NULL;
gives this error:
ERROR ...
2
votes
1answer
946 views
Store .shp to MySQL database with QGIS
I'm using QGIS 2.10 (Pisa) and I can´t store spatial information on my MySQL database (.shp or geojson). In previous versions that was allowed.
It's not supported anymore or there are any plugins or ...
3
votes
2answers
2k views
Column 'SHAPE' cannot be null in mysql database even if the query string is correct
I am trying to write an POLYGON into an column named SHAPE which is a geometry type. And created query which looks
$sql = "INSERT INTO rokka(zone,district,location,area,SHAPE)
VALUES ('$zone','$...
4
votes
1answer
1k views
MySQL select linestrings into a multilinestring
I have a mysql column of linestring type with 2000 linestrings (high water mark values to be more specific).
I want to merge them into one multilinestring so i can see what the shape of these values ...
1
vote
1answer
571 views
Error #1064 from MySQL searching database for results near lat/long
I'm trying to run a SQL query to search for a term and a users location. I need help to search my database for locations near a user with the description of 'X'. The users location (lat, long) is ...
3
votes
2answers
3k views
MySQL Polygon (Spatial) Insert Statement works sporadically
I know that MySQL doesn't have great spatial support at the moment, but is it that buggy that my insert statements would work sporadically? In general, longer coordinates seem to be failing on insert (...
1
vote
1answer
464 views
Get all points with in some range of a point (or range of point) in MySQL
I have got a list of points and a fixed range (10m). So what I have to do is for each point I have to find all the points present in a table with the 10m range of the given point.
For example:
Let ...
4
votes
1answer
6k views
is MySQL spatial ready for production?
currently I'm using PostgreSQL/PostGIS for a small spatial project. Last week the project manager asked me if we could migrate to MySQL with Spatial Extension.
I know MySQL but have no experience ...
6
votes
2answers
4k views
Which spatial DBMS should I select?
I found there are a number of RDMS have the spatial capability e.g. mysql, postgreSQL
I considering which I should go forward!
I would like to store the lat lon coord from the tracker to the db ...
9
votes
4answers
9k views
how to find 20 closest points efficiently [closed]
Say I want to find 20 closest business near me.
My table structure is like this:
BusinessID varchar(250) utf8_unicode_ci No None Browse distinct values Change Drop ...
1
vote
1answer
1k views
MySQL: Query the envelope (mbr, extents) of a spatial table?
this question provides a solution to get the evelope (aka: mbr, aka: extents) of a spatial table in PostGIS, but I'm having trouble doing the same in MySQL.
The ogr MySQL driver does this, and a ...
14
votes
2answers
36k views
Data types for storing lng/lat in MySQL
I'm storing a large list of lnt/lat points in a MySQL database. At the moment these are projected points for the UK in M, but in the long term I would like to make sure that I can store the ...
13
votes
3answers
7k views
Poor Performance when using Spatial Indexes in MySQL
Re-post of a question asked on Stack Overflow when it was suggested this would be a better forum.
I'm trying a little experiment at pushing a data set which is not geo-spatial but fits it quite well ...
23
votes
4answers
3k views
Would PostGIS offer an advantage over MySQL for a produce farm application?
I have a web app that stores the locations of farms in West Michigan. You can search for a product (e.g. "broccoli") and it will show you all the farms that grow that product.
Right now I'm using ...
