Questions tagged [mysql]
MySQL is an open-source, relational database management system. Place you question on MySQL Spatial Extensions here.
275
questions
1
vote
1
answer
37
views
OpenStreetMap data OSMOSIS and MySQL - make faster?
How can I make dumping .osm data into a MySQL database faster using Osmosis? Currently 3 hours so far for the USA file but MySQL is only using 10-15 percent CPU, 10 percent RAM and HD is negligible (...
- 11
0
votes
1
answer
28
views
Seeking MySQL alternative to PostgreSQL ST_SimplifyPolygonHull
I need to optimize the timezones GeoJSON from https://github.com/evansiroky/timezone-boundary-builder/releases/tag/2022f (timezones-with-oceans.geojson.zip) to be stored in database.
There are some ...
- 101
0
votes
0
answers
43
views
Store layer database credentials for QGIS Server
I'm using QGIS Server to provide WMS access to a handful of layers. QGIS Server is running on a headless Ubuntu machine and seems to be working fine for layers that are themselves stored in files (...
- 1
1
vote
0
answers
41
views
MapServer WFS Filter incorrectly maps PropertyName as Literal instead of MySQL Field
I've never used MapServer before, but have been tasked with migrating a legacy website which uses it to an updated server, including an upgrade to MapServer 7.6.2. Most stuff just works, but I'm ...
- 141
0
votes
0
answers
163
views
MySQL : Error Code: 3548. There's no spatial reference system with SRID 1498173264
I am trying to run a MySQL query below:
select *
from geo
where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609
and get an error: Error Code: 3548. There's no spatial ...
- 433
0
votes
0
answers
80
views
QGIS: Points (vector layer) imported from MySQL table are available but wouldn't show
I have a MySQL table with points coordinates that I want to process in QGIS.
After some trial-and-error process I've found I should define the imported field a "Point" data-type (and not the ...
- 31
0
votes
0
answers
50
views
Determining mile marker locations on railroad route LineStrings
Page 7 of the Texas SP Austin and Northwestern Railroad Historic District--Fairland to Llano NRHP application says the following:
Contributing and Noncontributing properties are noted by two sets of ...
- 314
0
votes
0
answers
281
views
MySQL - Filter a table based based on location / distance - ST_Distance
I have table with Polygon geometries. I'd like to filter the table to include rows that are at a certain distance from a Point coordinate.
Here's a fiddle link for table schema, data etc.
https://...
- 433
0
votes
1
answer
1k
views
Invalid GIS data provided to function st_polygonfromtext in MySQL
I have a table in MySQL with geometry data in one of the columns. The datatype is text and I need to save it as Polygon.
I have tried a few solutions, but keep running into the error
Invalid GIS data ...
- 433
1
vote
0
answers
152
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
107
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,...
- 314
0
votes
0
answers
186
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
45
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
439
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
110
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
573
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 ...
- 451
2
votes
1
answer
442
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 ...
- 335
2
votes
0
answers
166
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,676
1
vote
0
answers
380
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
69
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
81
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
440
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
2k
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
136
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
368
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
435
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
0
votes
1
answer
390
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
vote
1
answer
127
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
129
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
777
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
238
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 ...
- 4,000
2
votes
2
answers
787
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
53
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
347
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
95
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
33
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=...
- 451
1
vote
1
answer
832
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
4k
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 ...
- 73
0
votes
1
answer
1k
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
2
answers
2k
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
840
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
73
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
45
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
1k
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
453
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
435
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
1
answer
301
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
0
votes
2
answers
700
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
2k
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
452
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