23
votes
Accepted
Where does the default Earth radius in ST_Distance_Sphere come from?
Ok, this is hilarriuusss. I tracked this down. In an old copy of lwgeom/lwgeom_spheroid.c in PostGIS 1.0.0rc4 you can see this,
/*
* This algorithm was taken from the geo_distance function of the
*...
- 7,119
8
votes
Accepted
Why particular polygon submitted by user is invalid?
PostGIS 3.1.1, in PostgreSQL 13 (what I had handy), is able to report the issue --
WITH bad_data AS (
SELECT ST_GeomFromText('POLYGON((-122.148755985 37.42723718100007,-122.148462038 37.42697222300006,...
- 20.3k
6
votes
Why particular polygon submitted by user is invalid?
If you would prefer an all Java solution then you should look at the JTS IsValidOp
So a program like:
String polyStr = "POLYGON((-122.148755985 37.42723718100007,-122.148462038 37.42697222300006,-...
- 83.2k
5
votes
View database connections in QGIS with Python
You can do it in this way:
from PyQt4.QtCore import QSettings
self.settings = QSettings()
self.settings.beginGroup( '/MySQL/connections/' )
connections = self.settings.childGroups() # [u'adsb', u'...
- 37k
5
votes
Accepted
QGIS show WKT from attribute table
If it's just for visualization you could use the geometry generator with the same expression:
geom_from_wkt("WKT")
To set it up you may duplicate your layer (so you will get one layer with ...
- 17.4k
4
votes
Find features within given coordinates and distance using MySQL
It's 2020, you should be using the built in spatial function of the RDBS. In this case you are after the ST_Distance function.
-- geo column in users table is of Geometry data type, with a spatial ...
- 159
4
votes
Accepted
Parsing WKB with python geos: ParseException: Unknown WKB type 16
So, in a nutshell: this is actually a MySQL binary blob of wkt. With AsText(column) I was able to get a normal wkt format, and then parse it with shapely/geopy:
d = pd.read_sql_query('''SELECT AsText(...
- 2,004
4
votes
Accepted
Mapserver hide OGR exception
This is fixed in Mapserver-7.0.3 and backported to other versions (6.0.5, 6.2.3, 6.4.4). Looks like Kain's question was taken as a bug report which was subsequently fixed. See OGR error messages too ...
- 353
4
votes
Passing data from mysql/PHP into Leaflet
OK, so the main struggle (and obvious error) is the newbie mistake of not controlling which code runs in PHP in the server, which code runs in JS in the browser, and not having control over which ...
- 10.3k
4
votes
Accepted
Server side clustering with geohash
While the geohash representation is convenient, it is not designed with spatial queries in mind. From my understanding of the Geohash algorithm you can't just select all the points inside a given ...
- 1,144
3
votes
Does MySQL only support bounding boxes calculations instead of the real polygon geometry?
The accepted response is fairly outdated for the current state of the art.
MySQL 5.6 has decent support for the OGC data types on MyISAM tables. You probably don't want to use MyISAM tables though.
...
- 131
3
votes
ArcMap to MySQL using Modelbuilder
You can connect to MySQL using OLE DB connection and then use the connection in your model. If you can, it might be easier to write a tiny Python script that you could invoke as part of the model. In ...
- 30k
3
votes
Accepted
MySQL Using ST_CONTAINS to obtain points within a bounding box
For anyone having the same problem - the answer is that an incorrect syntax was being using for the POLYGON declaration. The POLYGON should have been declared using the following method:
SET @g1 = ...
- 171
2
votes
Different results given by PostGIS and MySQL?
This question was asked ½ a decade ago, but things have changed since then. As stated in @Mapperz’s comment, 5.6 is the first version of MySQL with true full-shape spatial geometry operators and ...
- 121
2
votes
Converting Shapefile to MySQL?
The best available answer is to use ogr2ogr to import shape files. FWtools is a great tool to import shape files into mysql here is an example
ogr2ogr -f MySQL MySQL:database_name,host=localhost,user=...
- 71
2
votes
Find features within given coordinates and distance using MySQL
It might be base to create a function .. so you can reuse it other other areas. Also would make your query a bit cleaner... At least that is my 2 cents.
DELIMITER $$
CREATE FUNCTION calcDistance(...
- 121
2
votes
How to filter OSM data for addr keys with Osmosis
Osmosis has option for tag filter. here is the documentation. https://wiki.openstreetmap.org/wiki/Osmosis/Detailed_Usage_0.46#--tag-filter_.28--tf.29
Example usage:
osmosis \
--read-xml input.osm \
...
- 8,390
2
votes
Can joined tables be served by QGIS Server WFS?
I can confirm that joined tables are properly served by QGIS server as WFS
my setup:
postgres geometry_table
postgres attribute_table
The tables are joined by QGIS desktop (add geometry table as a ...
- 1,411
2
votes
Accepted
Vincenty's formulae to calculate in MySQL and PHP
With points as close as these we can ignore what type of spheroid the earth is and use the eqirectangular projection Pythagoras formula is all that is required.
The following two functions are in PHP....
- 156
2
votes
Load coordinates from database to Google Maps
You could use software like MapServer or GeoServer to read your database and provide the data as a layer of layers as part of a WMS and/or WFS.
So you use Java to populate the database, and a WMS/...
- 13.6k
2
votes
Why I obtain different values calculating distance between two point in the space using this "Great-circle distance" formula and Google Earth tool?
Google Earth is probably calculating distance over the earth as a spheroid, taking account for the not-quite-spherical nature of the earth. Also, your earth radius is probably a smidge big, try 6371 ...
- 20k
2
votes
Accepted
Looking for geojson data host?
If the data is not sensitive or private in nature and does not violate any organizational data policies it is possible to host GeoJSON on GitHub. See documentation. Simply commit the file as you would ...
- 621
2
votes
Accepted
Can't get the correct geometry values after MySQL querying
It is a matter of representing the geometry.
You can use ST_AsText() to get a textual representation of the the geometry
The query would therefore be similar to
SELECT latitude,longitude, ...
- 43.2k
2
votes
Need to connect to MySQL or MariaDB databases in QGIS 3.4+
I still see it as an option in QGIS 3.8 through the add vector layer tool:
- 16.9k
2
votes
Sorting by distance in MySQL
Please change your query as following
SELECT *
FROM locator
WHERE SQRT(POW(X(center) - 49.843317, 2) + POW(Y(center) - 24.026642, 2)) * 100 < radius
ORDER BY SQRT(POW(X(center) - 49.843317, 2) + ...
- 34k
2
votes
Determining minx/miny/maxx/maxy for shape from polygon in GeoJSON file
I used shape.bounds to access the tuple representing the min/max x/y, very easy:
minmax=(shape.bounds[0],shape.bounds[1],shape.bounds[2],shape.bounds[3])
2
votes
Why particular polygon submitted by user is invalid?
Another option using Python:
from shapely.wkt import loads
from shapely.validation import explain_validity
from shapely.geometry import Point
wkt = r"POLYGON((-122.148755985 37.42723718100007,-...
- 77.8k
1
vote
Lost geometry from QGIS mysql vector layer
Not a fix for the underlying problem, but try to add the layer back as a new vector layer then:
Layer|Copy style, then
Layer|Paste style
That should make the newly-added layer match the old style; ...
- 14.9k
1
vote
Correct method to save and restore leaftlet drawing into mysql database
I would recommend looking into this approach:
L.GeoJson layer and pointing Leaflet.draw at it.
You can iterate over drawn objects with drawnItems.eachLayer() method and add any objects to the layer.
- 12.1k
1
vote
How to convert polygon shapefile to CSV?
In QGIS, you can use the MMQGIS plugin function "Export Geometry to CSV". This tool would generate WKT for each polygon in the shape file as well as any associated attributes. The process is simple ...
- 2,324
Only top scored, non community-wiki answers of a minimum length are eligible