Questions tagged [mysql-spatial]
MySQL spatial refers to the Extensions for Spatial Data, that enable the generation, storage, and analysis of geographic features.
61
questions
0
votes
0
answers
21
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)) *...
0
votes
0
answers
30
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 ...
- 43
-3
votes
1
answer
60
views
Looking for SQL tutorials for beginner [closed]
Where can I get quality tutorials on learning SQL for GIS?
0
votes
0
answers
75
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 ...
- 155
0
votes
1
answer
102
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 --...
- 1
0
votes
0
answers
72
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. ...
- 61
1
vote
0
answers
213
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'...
0
votes
0
answers
138
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,...
- 338
1
vote
0
answers
166
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 ...
- 11
2
votes
0
answers
221
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 ...
- 1,724
0
votes
1
answer
312
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 ...
- 163
1
vote
0
answers
458
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 ...
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 ...
- 1,050
0
votes
0
answers
97
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:
'...
- 101
1
vote
2
answers
2k
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 ...
- 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 ...
- 338
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 ...
- 1
1
vote
0
answers
495
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 ...
- 159
2
votes
1
answer
142
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 ...
- 91
1
vote
0
answers
139
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 :...
- 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 ...
- 21
0
votes
1
answer
336
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(...
- 111
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,
...
- 51
3
votes
0
answers
2k
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 ...
- 6,971
1
vote
1
answer
622
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, ...
- 991
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-...
- 21
1
vote
0
answers
252
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
0
answers
951
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 ...
- 143
1
vote
0
answers
207
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 ...
- 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 ...
- 1
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 ...
- 6,971
1
vote
1
answer
935
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 ...
- 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 ...
- 121
2
votes
1
answer
7k
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 ...
- 141
3
votes
0
answers
103
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 ...
- 31
1
vote
0
answers
586
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
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. ...
4
votes
3
answers
7k
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 -...
- 188
1
vote
1
answer
87
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 ...
- 111
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 ...
- 143
3
votes
0
answers
271
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,...
- 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(...
2
votes
0
answers
546
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....
- 121
2
votes
1
answer
293
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....
- 545
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 ...
- 19
5
votes
1
answer
783
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 ...
- 3,316
1
vote
1
answer
848
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 ...
- 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 ...
- 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 ...
- 33
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','$...
- 761