Questions tagged [mysql]
MySQL is an open-source, relational database management system. Place you question on MySQL Spatial Extensions here.
260
questions
0
votes
0answers
13 views
Checking location in which polygon
I have KML file for country polygon.
I can read the KML file in PHP and see all boundaries.
What I want now is when I put a marker on point showing me the title of the polygon.
Does anyone have any ...
1
vote
0answers
34 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 ...
0
votes
1answer
33 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
1answer
24 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?
0
votes
0answers
57 views
Using MySQL connection in ArcGIS Online Service
Apologies if this is a dumb question.
I have some programs that utilize MySQL to store data that I would like to use in some ArcGIS Online services. I have an ArcGIS Server set up on our network ...
1
vote
1answer
73 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.
...
0
votes
0answers
26 views
Looping jsongeo array
I have the following code:
$i=0;
$sql=mysql_query("SELECT * from table");
$tot = mysql_num_rows($sql);
$feature = array(
'type' => 'FeatureCollection',
'features' =...
0
votes
0answers
8 views
Calculating where map coordinates land within a collection of district boundaries? [duplicate]
The following coordinates make up the district boundaries of Westminster in the UK (point-to-point polygon):
51.4859, -0.1295
51.4846, -0.1498
51.4978, -0.1561
51.4978, -0.1796
51.5259, -0.2062
51....
0
votes
1answer
52 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....
3
votes
0answers
64 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 ...
1
vote
2answers
114 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....
1
vote
0answers
40 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 ...
0
votes
0answers
21 views
Open MySQL server spatial table in PyQGIS standalone application
I am using below code to connect the MySQL spatial table installed on server on a QGIS based standalone application. I am using python 2.7, QGIS 2.18 and MySQL 8.0 version.
uri="MySQL:mysql,host=177....
1
vote
0answers
49 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 ...
0
votes
0answers
23 views
Showing geometry from MySQL table on standalone QGIS application
I am using below code to connect the MySQL table on a QGIS based standalone application. I am using QGIS 2.7 and MySQL 5.7 version.
uri="MySQL:mysql,host=localhost,port=3306,user=root,password=...
0
votes
0answers
36 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
0answers
19 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=...
1
vote
1answer
201 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 ...
0
votes
0answers
850 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 ...
1
vote
1answer
320 views
MySQL 5.7 and how to store WGS 84 Coordinates
I want to store WGS84 coordinates in a MySQL 5.7 database. After much reading, I think I should use the POINT datatype with a SPATIAL INDEX. But I am very confused about SRSs, SRIDs, lat/long vs long/...
0
votes
1answer
196 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 ...
1
vote
1answer
372 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 ...
1
vote
0answers
259 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 ...
1
vote
0answers
51 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 ...
1
vote
0answers
31 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 ...
1
vote
1answer
309 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
1answer
224 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 ...
1
vote
2answers
181 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 ...
0
votes
0answers
366 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 ...
0
votes
1answer
159 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(...
0
votes
1answer
313 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
vote
0answers
708 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("...
1
vote
1answer
131 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 ...
4
votes
0answers
1k 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,
...
0
votes
1answer
652 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 ...
1
vote
0answers
181 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, ...
0
votes
1answer
1k 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. ...
3
votes
2answers
1k 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( `...
1
vote
0answers
567 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 ...
0
votes
1answer
864 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,...
0
votes
1answer
292 views
Spatial functions with MySQL, XAMPP, and Wordpress
I am trying to figure out how to using the ST_AsGeoJSON function in MySQL, but I keep getting an error that says that this function does not exist. I have found in the MySQL documentation that this ...
1
vote
2answers
461 views
MySQL 8 MBRIntersects 10 times slower than PostGIS equivalent
I'm trying to migrate a PostGIS database to MySQL 8 and am having a big performance hit when it comes to MBRIntersects.
Using Mapbox to render vector tiles, I need to extract geometry intersecting ...
1
vote
0answers
153 views
Which approach is faster for getting all POIs from MySQL/MariaDB with PHP/Laravel [closed]
Correct me if I'm wrong.
There are three approaches to get the nearest homes, users have created in my website:
To create a table with two columns(latitude, longitude) that both of them are float ...
1
vote
0answers
104 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 ...
2
votes
0answers
384 views
Storing Shapely polygons in MySQL
I have to store some polygons in a MySQL database. The polygons are in Plate Carrée (ESPG 32662) projection, but I also tried to first converting them to the usual latitude-longitude projection (ESPG ...
0
votes
1answer
343 views
TurfJS + PHP + MySQL
I have a PHP endpoint/service that receives Lat/Long data from a 3rd party. The data is then processed in PHP code and saved into a MySQL database. This process works fine without any issues.
I need ...
1
vote
1answer
369 views
Importing shapefile to SQL database, attributes are all NULL
I used ogr2ogr to import a shapefile into a MySQL database using the following:
ogr2ogr MYSQL:"db,host=localhost,user=root" -nln "map" -a_srs "EPSG:4683" map.shp -overwrite -addfields -...
1
vote
0answers
1k views
Parsing GeoJSON in PHP?
i am trying to parse GeoJson from database with php but somehow i got an error and i cannot found any error in my script. here my script
$id = 1;
$db = new mysqli("localhost","root","","skriboo");
$...
0
votes
1answer
952 views
MySQL GPS distance calculation is failing
MySQL 5.6 here. I'm storing GPS coordinates as MySQL Point types in my DB and am trying to build a query that takes a single GPS coordinate (consisting of latitude and longitude components) and ...
2
votes
1answer
234 views
Lost geometry from QGIS mysql vector layer
I have a vector layer displaying geometry from a mysql view.
This works fine for several days/weeks until suddenly it fails to refresh and my layer reverts to a non-geometry table, losing all symbols,...
