Questions tagged [mysql-spatial]

MySQL spatial refers to the Extensions for Spatial Data, that enable the generation, storage, and analysis of geographic features.

Filter by
Sorted by
Tagged with
1 vote
0 answers
25 views

Using MySQL WKB

I am using MySQL version 8.0.29 and I have an issue with WKB string, I will simplify query here SELECT ST_GeomFromWKB(X'...
user avatar
0 votes
0 answers
22 views

OSGeo4W / ogr2ogr gives "Unable to create or change a table without a primary key" error with MySQL 8.0

I'm trying to use OSGeo4W Shell to populate my MySQL DB with data from a shapefile and am having some difficulty. Here's the command I'm using: ogr2ogr -f MySQL MySQL:maps,host=localhost,user=root,...
user avatar
  • 205
0 votes
0 answers
46 views

Shapefile > MySQL > Shapefile/GeoJSON

I have created a React application to display a Leaflet map, with a series of polygons over the top. The data source for the polygons is a GeoJSON file. As part of the app, I am giving the user the ...
user avatar
  • 1
0 votes
0 answers
85 views

Converting coordinate systems in MySQL

I have one table with shapes using ESPG 4326 as the coordinate system and another using ESPG 3857. I'd like to be able to convert the ESPG 4326 table to ESPG 3857 with an SQL query but I'm unsure of ...
user avatar
  • 205
2 votes
0 answers
75 views

Clustering spatial data in database

I have a simple website that is hosted on 000webhost. This service provides storing data in MySql database for a free plan. I wanted to extend some functionality for my site by clustering loads of ...
user avatar
0 votes
1 answer
143 views

lat/long lookup in GeoJSON regions provided by US state governments

I have polygon data (usually GeoJSON format) for various city and county boundaries from several US state governments. Example: New Mexico tax districts. Given a lat/long coordinate, I need to figure ...
user avatar
1 vote
0 answers
172 views

QGIS is not displaying geometry features for MySQL table

I'm currently working in a MySQL database version 5.7. I've a polygon table that was created using the following way: CREATE TABLE coords( ref_geo CHAR(80) NOT NULL, geom GEOMETRY NOT NULL ); I ...
user avatar
2 votes
2 answers
502 views

How to input geospatial data into MS SQL Server via Python and SQLAlchemy

I'm trying to add a GeoJSON file into the SQL Server database. I have this following script that works fine for inputting the geospatial data into MYSQL Database, thus creating a GEOMETRY type column ...
user avatar
  • 916
0 votes
0 answers
30 views

How to determine if a person is on the line or distance from it

There is a base table with the following structure: id - AUTO_INCREMENT name_hotel - varchar coordinates - multilinestring The coordinates form a line In the database, it is written like this: '...
user avatar
  • 101
1 vote
1 answer
560 views

How to output GeoJSON file from MySQL database shapefile?

I have a MySQL spatial database ('mydb') of one table ('tst'), I mean by spatial. I need to output the GeoJSON file from this spatial MySQL table. I downloaded geoPHP library and also MySQL to GeoJSON ...
user avatar
  • 29
1 vote
0 answers
41 views

HIFLD Railroads DB contains coordinates that don't make sense

I downloaded the HIFLD Railroads DB and imported it into MySQL with OSGeo4W. I then do this query and the coordinates I get just don't make sense: SELECT ST_AsGeoJson(SHAPE) FROM railroads WHERE ...
user avatar
  • 205
0 votes
2 answers
752 views

Getting area (in square meter or km) of polygon in MySQL 5.6

I have a table in MySQL that has polygon defined. Read MySQL 5.6 geospatial index documentation and came across ST_Area function. Tried to dig into the units which are computed as per the spatial ...
user avatar
  • 1
1 vote
0 answers
313 views

MySQL performance issue with ST_Contains not using spatial index

We are having what seems to be a fairly large mysql performance issue on trying to run a fairly simple update statement. We have a table(1.8mil) with houses that contains a Lat+Long geometry point ...
user avatar
  • 161
2 votes
1 answer
102 views

Flight paths over the date line

I have a table with lat/long values in MySQL. The sequence column gives me the order in which the path must be traced. . I need to create a line from these values and display the flight path via a ...
user avatar
  • 81
1 vote
0 answers
109 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 :...
user avatar
  • 11
1 vote
1 answer
1k 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 ...
user avatar
  • 21
0 votes
1 answer
248 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(...
user avatar
5 votes
0 answers
3k 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, ...
user avatar
1 vote
0 answers
1k 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 ...
user avatar
  • 6,641
1 vote
1 answer
362 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, ...
user avatar
  • 911
2 votes
0 answers
806 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-...
user avatar
1 vote
0 answers
176 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 ...
user avatar
1 vote
0 answers
800 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 ...
user avatar
1 vote
0 answers
138 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 ...
user avatar
  • 11
0 votes
1 answer
861 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 ...
user avatar
16 votes
1 answer
3k 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 ...
user avatar
  • 6,641
1 vote
1 answer
742 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 ...
user avatar
  • 111
0 votes
1 answer
3k 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 ...
user avatar
  • 121
2 votes
1 answer
6k 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 ...
user avatar
  • 141
3 votes
0 answers
93 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 ...
user avatar
1 vote
0 answers
502 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 ...
user avatar
2 votes
1 answer
2k 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. ...
user avatar
4 votes
3 answers
6k 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 -...
user avatar
  • 178
1 vote
1 answer
79 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 ...
user avatar
4 votes
2 answers
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 ...
user avatar
3 votes
0 answers
230 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,...
user avatar
  • 31
1 vote
1 answer
6k 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(...
user avatar
2 votes
0 answers
516 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....
user avatar
  • 21
2 votes
1 answer
259 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....
user avatar
  • 545
1 vote
0 answers
81 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 ...
user avatar
  • 19
5 votes
1 answer
713 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 ...
user avatar
1 vote
1 answer
763 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 ...
user avatar
  • 464
2 votes
2 answers
5k 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 ...
user avatar
  • 772
2 votes
1 answer
1k 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 ...
user avatar
3 votes
2 answers
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','$...
user avatar
  • 741
4 votes
1 answer
2k 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 ...
user avatar
  • 141
1 vote
1 answer
595 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 ...
user avatar
3 votes
2 answers
4k 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 (...
user avatar
1 vote
1 answer
595 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 ...
user avatar
4 votes
1 answer
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 ...
user avatar
  • 727