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
0 votes
0 answers
55 views

Calculate latitude and longitude based on distance (miles)

I have been trying to get location details based on miles with the below query, SELECT * FROM (SELECT id,loc1, zipcode, latitude,longitude, (3959 * acos(cos(radians(36.0411)) * cos(radians(latitude)) *...
Loganathan Natarajan's user avatar
0 votes
0 answers
59 views

MySQL retrieving GEOMETRY Points (indexed) from a table which are within a GEOJSON polygon

I am trying to retrieve all of the points from a table which are within a GEOJSON polygon. I am running everything below through a Python function. So, I created a table from a CSV file of open data ...
David Graham's user avatar
-3 votes
1 answer
63 views

Looking for SQL tutorials for beginner [closed]

Where can I get quality tutorials on learning SQL for GIS?
Justine Kimanthi's user avatar
0 votes
0 answers
125 views

Spatial index creation: MySQL vs Spatialite

I'm just starting to learn MySQL and I have a script that loads a large CSV file (4 million rows) into a table and then adds a spatial index to the GeometryLatLon column. The spatial index creation ...
Rojj's user avatar
  • 175
0 votes
1 answer
149 views

Missing address in OSM file

I downloaded a part of germany .osm file from geofabrik and extracted all ways and nodes whose tag contains addr-key using osmfilter: osmfilter.exe hessen-latest.osm --keep=addr:*=* --drop-relations --...
Erkan's user avatar
  • 1
0 votes
0 answers
80 views

Inconsistent results from spatial databases

I'm trying to write something agnostic to whichever database vendor is chosen between MySQL, SQL Server, Oracle, and PostgreSQL. However, I see inconsistent results in functions that return measures. ...
mxcolin's user avatar
  • 61
1 vote
0 answers
248 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'...
niko peikrishvili's user avatar
0 votes
0 answers
159 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,...
neubert's user avatar
  • 360
1 vote
0 answers
190 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 ...
evorg's user avatar
  • 11
2 votes
0 answers
244 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 ...
Pavel Pereverzev's user avatar
0 votes
1 answer
349 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 ...
Kannan Goundan's user avatar
1 vote
0 answers
521 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 ...
MapsEveryWhere's user avatar
5 votes
2 answers
2k 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 ...
Matt_Geo's user avatar
  • 1,050
0 votes
0 answers
101 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: '...
iKey's user avatar
  • 101
1 vote
2 answers
3k 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 ...
Kawtar's user avatar
  • 29
1 vote
0 answers
62 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 ...
neubert's user avatar
  • 360
0 votes
2 answers
2k 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 ...
Ankit's user avatar
  • 1
1 vote
0 answers
554 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 ...
John C's user avatar
  • 159
2 votes
1 answer
154 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 ...
azis511's user avatar
  • 91
1 vote
0 answers
140 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 :...
Manoj's user avatar
  • 11
1 vote
1 answer
2k 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 ...
sts's user avatar
  • 21
1 vote
1 answer
422 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(...
f.khantsis's 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, ...
Tylerian's user avatar
3 votes
0 answers
3k 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 ...
Evan Carroll's user avatar
  • 7,041
1 vote
1 answer
687 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, ...
Georg's user avatar
  • 1,001
2 votes
0 answers
1k 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-...
Rohith Mohan's user avatar
1 vote
0 answers
269 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 ...
vaishnavi mukundhan's user avatar
1 vote
0 answers
968 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 ...
Timo Jokinen's user avatar
1 vote
0 answers
215 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 ...
pbs's user avatar
  • 11
0 votes
1 answer
1k 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 ...
frizzer's user avatar
17 votes
1 answer
4k 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 ...
Evan Carroll's user avatar
  • 7,041
1 vote
1 answer
983 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 ...
Shade's 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 ...
dominic's user avatar
  • 121
2 votes
1 answer
8k 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 ...
fedecupe's user avatar
  • 141
3 votes
0 answers
104 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 ...
luminious's user avatar
1 vote
0 answers
607 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 ...
Miguel Ángel García's user avatar
2 votes
1 answer
3k 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. ...
moosewiththumbs's user avatar
4 votes
3 answers
8k 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 -...
AMahajan's user avatar
  • 188
1 vote
1 answer
91 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 ...
AndreaNobili's user avatar
4 votes
2 answers
3k 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 ...
Govind Samrow's user avatar
3 votes
0 answers
287 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,...
R.N.'s user avatar
  • 31
1 vote
1 answer
7k 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(...
Mike Morris - MBXSW's user avatar
2 votes
0 answers
550 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....
pierot's user avatar
  • 121
2 votes
1 answer
301 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....
zod's user avatar
  • 555
1 vote
0 answers
83 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 ...
iblanco's user avatar
  • 19
5 votes
1 answer
810 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 ...
Hasan Mustafa's user avatar
1 vote
1 answer
872 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 ...
kiks73's user avatar
  • 486
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 ...
jmcbroom's 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 ...
user1794648's 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','$...
poshan's user avatar
  • 761