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
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'...
user avatar
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,...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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: '...
user avatar
  • 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 ...
user avatar
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&...
user avatar
  • 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 ...
user avatar
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,...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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?
user avatar
  • 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. ...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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....
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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=...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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?
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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(...
user avatar
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 ...
user avatar
  • 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("...
user avatar
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 ...
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, ...
user avatar
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 ...
user avatar
  • 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, ...
user avatar
  • 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. ...
user avatar
  • 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( `...
user avatar
  • 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 ...
user avatar
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,...
user avatar

1
2 3 4 5 6