Questions tagged [mysql]

MySQL is an open-source, relational database management system. Place you question on MySQL Spatial Extensions here.

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

Calculate distance (km) between point within multipolygon and nearest edge

I have MariaDB with geographic points and multi-polygons representing country borders. I need to calculate the nearest distance between one point within the country and its closest border. My example ...
Kiew's user avatar
  • 73
0 votes
0 answers
28 views

Vector layer points are not being imported/shown from MySQL datasource

Issue QGis 3.24.12 does not visualise/import points from a vector layer with a MySQL datasource anymore. In QGis 3.24.12, I import a table from a MySQL datasource as a vector layer. However, the ...
Isaac Sterckx's user avatar
0 votes
1 answer
63 views

Migration of running MySQL 5.7 geo query to MySQL 8

We are testing a migration from MySQL 5.7 to MySQL 8 and have identified some queries that no longer work and was hoping to get insight into the most appropriate changes. I am not a geo expert and am ...
timpone's user avatar
  • 103
0 votes
2 answers
117 views

Determining minx/miny/maxx/maxy for shape from polygon in GeoJSON file

I have implemented the following code to search a GeoJSON file for a point (not asking for a code review or critique): #!/usr/local/bin/python3.11 import os import sys import fiona import shapely def ...
Joel Breazeale's user avatar
0 votes
0 answers
84 views

Datetime conversion exception with GeoServer and MySQL plugin 2.23.2

After recently upgrading my database server from MySQL 5.7 to 8.0.31, I have updated the GeoServer in my project with the latest version 2.23.2 and the corresponding MySQL connector. After this I got ...
Peter Koltai'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
0 votes
0 answers
76 views

MySQL is missing in QGIS 3.30 for MacOS

MySQL is missing in Add Vector layer database option. Instead, MySQL option is visible with eVis plugin. On my Mac (intel) MySQL woks fine with other clients outside of QGIS (eg workbench).
gabriele'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
0 answers
282 views

Transfer GeoJSON data to a MySQL database

I want to transfer my GeoJSON data to a MySQL database. I know that MySQL somehow supports GeoJSON but I do not really understand how that works. That was my try: SET @point_o_from_geoJSON := ...
Melina M's user avatar
1 vote
1 answer
176 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 (...
Jason G's user avatar
  • 11
0 votes
1 answer
37 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 ...
Lito's user avatar
  • 101
1 vote
0 answers
63 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 ...
odysseus's user avatar
  • 141
0 votes
0 answers
604 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://...
kms's user avatar
  • 473
0 votes
1 answer
2k 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 ...
kms's user avatar
  • 473
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
0 votes
0 answers
69 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 ...
sverdon's user avatar
2 votes
1 answer
808 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 ...
3dSpatialUser's user avatar
1 vote
0 answers
161 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....
Ryan NZ's user avatar
  • 119
1 vote
0 answers
1k 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 ...
Steven Lutz's user avatar
2 votes
1 answer
686 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 ...
Sebastian H's user avatar
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
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
0 votes
0 answers
74 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 ...
winston's user avatar
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
0 votes
0 answers
607 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 ...
rizkydhani's user avatar
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&...
martin's user avatar
  • 1
0 votes
1 answer
267 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 ...
StillaBeginner's user avatar
1 vote
1 answer
564 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,...
rosscova's user avatar
  • 111
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
0 votes
1 answer
472 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 ...
GreekMustard's user avatar
1 vote
1 answer
160 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?
mQsR's user avatar
  • 11
1 vote
1 answer
147 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. ...
loy's user avatar
  • 13
0 votes
1 answer
1k 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....
runback's user avatar
3 votes
0 answers
321 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 ...
Vincent Bré's user avatar
  • 4,090
2 votes
2 answers
957 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....
pomdoe's user avatar
  • 21
1 vote
0 answers
55 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 ...
Clara's user avatar
  • 11
1 vote
0 answers
419 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 ...
Kermit's user avatar
  • 111
0 votes
0 answers
115 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 ...
GUILLAUME's user avatar
0 votes
0 answers
37 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=...
Steven Lutz's user avatar
1 vote
1 answer
1k 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 ...
Moehrengulasch's user avatar
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 ...
Cenere Blu's user avatar
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 ...
Ahmed's user avatar
  • 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 ...
gNat's user avatar
  • 86
1 vote
0 answers
1k 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 ...
wchiquito's user avatar
  • 111
1 vote
0 answers
81 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 ...
John David's user avatar
1 vote
0 answers
52 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 ...
nicomonjelat's user avatar
1 vote
1 answer
2k 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?
Tim van Heteren's user avatar
0 votes
1 answer
465 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 ...
Kyle's user avatar
  • 69
1 vote
2 answers
532 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 ...
vrteach's user avatar
  • 21

1
2 3 4 5 6