Questions tagged [mysql]
MySQL is an open-source, relational database management system. Place you question on MySQL Spatial Extensions here.
269
questions
1
vote
0
answers
28
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
23
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,...
- 205
0
votes
0
answers
32
views
How to import shapefile into MySQL with Python
Let's see if you could help me with this:
I try to upload a gpkg (geo-referenced) file (table) to a MariadB server (MySQL). Not in PostgreSQL, Mariadb-(MySQL)
If I import from an OSGeo4W in the ...
- 131
0
votes
0
answers
20
views
Connect ArcGIS map to existing MySQL databse?
I have an existing dataset in a MySQL database that refers to specific country codes in Rwanda. I was curious whether or not it's possible to link an ArcGIS map of Rwanda to that dataset and display ...
- 1
2
votes
1
answer
89
views
QGIS show WKT from attribute table
I have a dataset (from mysql) which I can load into QGIS. One of the columns in the table (and thus in the attribute table) is a WKT column. I am not able to select this column for the geometry.
The ...
- 131
1
vote
0
answers
59
views
Optimized query to find nearby locations
I am working with the GeoNames database which has around 10 million rows of locations including longitude and latitude.
I have tested around 6 various nearby location queries including spatial indexes....
- 119
1
vote
0
answers
78
views
Can MySQL be used instead of PostGIS database in QGIS?
I'm having trouble connecting to a MySQL DB in QGIS and am wondering if maybe I'm expecting MySQL to be a replacement for PostGIS when it isn't.
Can I use MySQL to store large vector datasets or must ...
- 421
2
votes
1
answer
201
views
Writing a vector with geopandas to MySQL is written as TEXT not as POLYGON
I am currently trying to write some vector files into a MySQL database. It works great for most vector files, but for some I got the eror:
(pymysql.err.DataError) (1406, "Data too long for column ...
- 143
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 ...
- 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 ...
- 1,572
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 ...
0
votes
0
answers
60
views
Why are points from a MySQL WMS layer, displayed on a OpenLayers 2 map, reading out a lat/long location that is off by 500 meters,
I have a MySQL table containing MySQL point geometries (MySQL 5.7). I have configured a GeoServer WMS layer using this data. I cannot set the native srs (not enabled in config dialog), declared srs is ...
- 1
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:
'...
- 101
0
votes
0
answers
175
views
Saving polygon coordinates into MySQL
I am currently building a housing complex mapping in my regency with PHP, Leaflet.draw and MySQL to store the data.
My problem is, how to save the polygon coordinates into MySQL?
I am extremely new to ...
0
votes
1
answer
866
views
Saving data of Leaflet Draw using PHP into MySQL
How do I insert Leaflet Draw coordinates in the MySQL database with id longitude-latitude columns?
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8&...
- 1
0
votes
1
answer
42
views
Accessing x and y coordinates of a POLYGON in MySQL
In my database. I have a spatial column called coordinates which is of datatype POLYGON. so for example. The lunch room has coordinates POLYGON(0 2, 1 2, 3 2, 5 2, 0 2). That's just a hypothetical ...
1
vote
1
answer
177
views
Extract top, bottom, left, and right (as numeric values) from a polygon in MariaDB?
Is there a way to extract the minimum and maximum x and y values separately from a polygon value in MariaDB (I'm using 10.5.8 with no extensions)?
Eg:
set @poly = ST_GeomFromText('POLYGON((1.2 1.2,2 1,...
- 111
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 ...
- 161
0
votes
1
answer
213
views
QGIS MySQL points import
I have a column in my MySQL database table that contains location information (GeoJSON).
I want to import this data and see the points on my QGIS project map but it doesn't seem to work.
What I was ...
- 1
1
vote
1
answer
78
views
QGIS -> MySQL Updates
I added a MySQL layer containing GeoData to my QGIS 3 project.
I want this layer updated every time the database updates.
Is there a way to do that?
- 11
1
vote
1
answer
104
views
Convert points to line segment
In MySQL, I have two tables to store GPS trackpoint and second table to store chainage values. Lines can have more than one condition.
I wish to generate line segments by using both tables above.
...
- 13
0
votes
1
answer
280
views
Sorting by distance in MySQL
I found a sample of code
SELECT *
FROM locator
WHERE SQRT(POW(X(center) - 49.843317 , 2) + POW(Y(center) - 24.026642, 2)) * 100 < radius
in this question Find points within a distance using MySQL....
- 1
3
votes
0
answers
178
views
Import a layer in MySQL using PyQGIS
I managed to import a layer from a MySQL database in QGIS with PyQGIS.
Now I'm looking to import a layer from QGIS into MySQL.
I explored several ways :
Using ogr2ogr on the command line, the ...
- 3,845
2
votes
2
answers
555
views
Loading points from an array given by PHP query from MySQL db to OL
New to OpenLayers and JS in general. I have some coords stored as lat long, then PHP script which load these points from MySQL db and simply print it as follows:
var points = [[49.66356869941062, 16....
- 21
1
vote
0
answers
48
views
CARTO update existing table from external MySQL database
I would like my Carto data to be updated, using external data (MySQL database, I run and manage locally using PHP scripts), so that the Carto table gets updated with the most recent data, whenever the ...
- 11
1
vote
0
answers
205
views
Calculating miles between two points using Vincenty formula
I have the following MySQL function:
DELIMITER $$
DROP FUNCTION IF EXISTS `fn_vincenty_distance`$$
CREATE FUNCTION `fn_vincenty_distance` (
lat1 FLOAT, lon1 FLOAT,
lat2 FLOAT, lon2 ...
- 111
0
votes
0
answers
73
views
Failure to display points from a MySQL table in QGIS
I connected a MySQL table containing points with QGIS.
Adding new records from QGIS works well.
The problem is this: QGIS does not display the points on the map. However QGIS manages to interpret the ...
0
votes
0
answers
23
views
No coordinates from GDAL index in MySQL
I have run the gdaltindex command with output to a MySQL table, however only a list of file name are
gdaltindex -lyr_name Source_raster_idx -f MYSQL MySQL:mapDB,host=localhost,user=admin,password=...
- 421
1
vote
1
answer
524
views
Openlayers: Using PHP and npm
I've got a rather large MySQL database containing polygons (coordinates) and their additional properties (e. g. text labels in different languages). I would like to use PHP to connect to my database ...
- 303
3
votes
0
answers
3k
views
Large GeoJSON file in Leaflet performance issue
I have a large GeoJSON file (now it is about 22MB with 4K polygons but it is going to contains about 10k polygons)
I load the file in Leaflet using L.GeoJSON.AJAX, on each feature I attach two ...
- 63
0
votes
1
answer
728
views
QGIS is not showing/rendering points that are present in MySQL server
I have a MySQL database in which there are auto generated points almost 1000 created via Laravel (PHP framework) it has a very simple schema as shown below. I just want to visualise them on an ...
- 101
1
vote
1
answer
1k
views
Need to connect to MySQL or MariaDB databases in QGIS 3.4+
I need to connect to MySQL/MariaDB database tables where my geographic data is stored. I used to be able to do this in earlier versions of QGIS, but that option disappeared ~ QGIS 2.14. Are there any ...
- 86
1
vote
0
answers
619
views
ST_Latitude and ST_Longitude functions do not return exact values in MySQL [closed]
I tried to collaborate on this question from the Stack Overflow site.
The ST_Latitude and ST_Longitude functions (equal ST_X and ST_Y) of MySQL (version 8.0.17) returns a non-exact value for certain ...
- 111
1
vote
0
answers
61
views
Display all trajectories location along with dummy location
I'm trying to display the all trajectory location on Google map along with dummy location. I have tried to display dummy locations with sample location and it works fine, but when I try to connect to ...
- 11
1
vote
0
answers
40
views
Multiple layer data in one delimited text layer
I am a GIS beginner.
I'm exporting several point coords from a Mysql database, and importing into QGIS using delimited text layer.
I need to plot all the points and set the symbology to distinguish ...
- 11
1
vote
1
answer
811
views
How to make query of MySQL connection in QGIS?
i have added a connection with a MySQL database in QGIS
(Add vector layer - Database - MySQL).
Is there an option to make a query over the database in QGIS?
0
votes
1
answer
411
views
ArcCatalog connecting to MySQL DB [closed]
Looking to connect to MySQL DB from ArcCatalog.
As of now I have ArcCatalog 10.5 running on machine 1.
I have a MySQL 8 instance running on machine 2 (server).
I am able to connect to machine 2 from ...
- 69
1
vote
2
answers
312
views
MySQL, geoPHP, select sites near the current site
I'm working with archaeological sites, stored as polygons in a MariaDB 10.3 table, and I have PHP 7.2 with GEOS and geoPHP available. I'm creating a page that displays all the related information for ...
- 21
0
votes
0
answers
457
views
Query and display MySQL DB data from HTML
I want to link MySQL DB to the HTML and get the data according to the query below.
SELECT * FROM feature1 WHERE place = 'feature2'
php seems one of the way to do this, since I am one day old with ...
user139697
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(...
- 101
0
votes
2
answers
562
views
Unable to save point features to QGIS on MySQL database
I created a table with point geometry in MySQL. Subsequently added a vector layer from MySQL table. The table could be accessed from QGIS. However on pressing the add button the system threw warning ...
- 1
1
vote
0
answers
1k
views
How to insert a Polygon into MySQL database Table?
I have shapefiles which I converted to .csv format. I want to insert those into a MySQL Database table. I used ST_PolyFromText according to the MySQL reference manual.
## create table
cur.execute("...
- 109
1
vote
1
answer
299
views
Can't get the correct geometry values after MySQL querying
I have large points data and also a small bounding boxes. I want all the points present within this bounding boxes.
With the extent of the bbox , I want to get all the points within this bbox.
With ...
- 109
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
1
vote
1
answer
2k
views
Check whether a point is inside a polygon or not in MySQL
I am trying to get the points those are within the shapefile. I have tried the Spatial functions and it's showing an error:
3033 - Binary geometry function st_contains given two geometries of ...
- 111
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, ...
- 911
0
votes
1
answer
2k
views
Automatically refresh Leaflet Markers from MySQL
I have a leaflet map that loads marker details from a MySQL database. I have a timer that clears and reloads the layers (and therefore markers); however; it doesn't seem to be refreshing the data. ...
- 1
3
votes
2
answers
2k
views
Server side clustering with geohash
I need help with clustering algorithm. I have a mysql database with lat, lng, geohash(12 chars).
I am usign this query to select all records for zoom level 2 (2 chars)
SELECT COUNT( id ) , LEFT( `...
- 233
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 ...
- 143
0
votes
1
answer
1k
views
Why does mysql ST_WITHIN work for some MultiPolygons but doesn't for others?
The mysql queries are to return all POI's within a certain country.
This works (for Angola):
SELECT
poi.poi_id,
poi.poi_address_full
FROM
poi
WHERE
ST_WITHIN(
poi.poi_point,...
