19
votes
Accepted
Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]
That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability.
When you use Geometry on MySQL (...
- 64.7k
17
votes
Accepted
Does MySQL store point datatypes as LAT LNG or LNG LAT?
Constructor Syntax
Looking up the spatial function reference, you'll see
Point(x, y)
Constructs a Point using its coordinates
This isn't entirely correct. All GIS implementations must do (x,y) for ...
- 64.7k
13
votes
Accepted
Alternative to MakeValid() for spatial data in SQL Server 2016
Let me caveat that I am playing with spatial data in SQL server for the first time (so you probably already know this first part), but it took me a while to figure out that SQL Server isn't treating (...
- 10k
13
votes
Accepted
How do I create a point with an SRID in MySQL?
MySQL 8.0+
MySQL supports Point(x,y) which is a GIS function that constructs a point. With MySQL 8.0 and newer, you can further assign an SRID to that point with ST_SRID(srid)
SELECT ST_SRID( Point(...
- 64.7k
11
votes
Accepted
How can I speed-up my query on geo-location processes
You may consider using a GIST index based on using the function ll_to_earth. This index will allow for fast "nearby" searches.
CREATE INDEX
ON locs USING gist (ll_to_earth(lat, lng));
Once you ...
- 13.6k
11
votes
Accepted
Can PostGIS use spatial indexes on foreign tables?
Assuming at least Postgres 9.6 for local and remote server, and the same version of PostGIS installed, running this on the local server should do the trick:
ALTER SERVER your_foreign_server_name ...
- 182k
11
votes
Accepted
&& vs ST_Intersects performance
Background, functionality and performance
&& operator
&& is bounding-box overlaps. All operators call functions in PostgreSQL: you can see this \doS+ && in this case && ...
- 64.7k
9
votes
Accepted
MSSQL Geography STIntersects returns 1 when 0 is expected
The reason you are having a problem is that the polygon is wound the wrong way which results in a polygon that covers the entire world except for your area of interest.
Try
DECLARE @t_point ...
- 1,111
8
votes
PostgreSQL vs. MySQL: spatial feature comparison
Speaking of some very major things. Here is a list of things PostGIS supports that are totally absent in MySQL and MariaDB.
SRID in calculations, give your points a different SRID and you'll get ...
- 64.7k
8
votes
Accepted
SQL Server Line Constructor or version of ST_MakeLine(pt1,pt2)?
Try ShortestLineTo():
SELECT
pt1.ToString(),
pt2.ToString(),
pt1.ShortestLineTo(pt2).ToString() AS line -- what goes here
FROM #tmp;
Returns
POINT (1 1) POINT (2 2) LINESTRING (1 1, 2 2)
...
- 10k
8
votes
Accepted
What's stored in spatial_ref_sys?
No, it comes with PostGIS as a standardized catalog of the spatial reference systems your database can work with. It will be recreated when you CREATE EXTENSION postgis;.
In fact, copying the table ...
- 64.7k
7
votes
Accepted
Cross join on a numbers table to get line vertices, is there a better way?
I know a bit about Oracle performance and pretty much nothing about custom data types, but I'll try to give you a plan to improve performance.
1) Verify that you cannot get an explain plan.
It's ...
- 32.6k
7
votes
Accepted
How to optimize STDistance execution?
Regardless of any other improvements you make, be sure to test the impact on spatial execution times of enabling trace flags 6532, 6533, and 6534 (start-up only). These turn on native code spatial ...
- 90.3k
7
votes
What's the difference between POINT(X,Y) and GeomFromText("POINT(X Y)")?
MySQL 8+
For posterity the only thing that matters is that
Point(X,Y) is a constructor for numbers with precision and does not require converting first to text making it faster. It's also guaranteed ...
- 64.7k
7
votes
PostGIS vs. SQL Server for GIS data
Having worked with the GIS capabilities of both systems, I'd suggest the following:
If you are primarily looking to store and query geometries, their capabilities are pretty comparable.
If you want ...
- 207
7
votes
Accepted
Problem getting zips in radius via MySQL
From Indexing and Querying Spatial Data in Oracle in the Oracle® Spatial Developer's Guide 11g Release 2 (11.2):
Querying Spatial Data
Spatial uses a two-tier query model with primary and ...
- 908
6
votes
Accepted
What are object-relational databases, and why is this model necessary in spatial databases?
The relational model and the object-oriented paradigm
The state-of-the-art relational model, originated in 1970 by Dr. E. F. Codd, is applied science concerning the field of database administration. ...
- 8,540
6
votes
Accepted
Cannot get geometry object from data you send to the GEOMETRY field
Wrong syntax... Instead of
INSERT INTO track VALUES (
position=ST_PointFromText(...)
);
use
INSERT INTO track SET
position=ST_PointFromText(...);
Reference: https://dev.mysql.com/doc/...
- 79.4k
6
votes
Accepted
Spatial Index Compression
Your syntax for ALTER INDEX...REBUILD WITH (DATA_COMPRESSION...) is correct. The problem is actually revealed in the error message:
Invalid usage of the option data_compression in the ALTER INDEX
...
- 16.3k
6
votes
Accepted
SQL Server Point Constructor or version of ST_Point(x,y)?
SQL Server 2012+
Yes, it's not listed there though. You'll find it documented on the docs of geography::Point(x,y,srsid)
The following example uses Point() to create a geography instance.
geography::...
- 64.7k
6
votes
Accepted
ERROR: invalid GeoJson representation
From the docs on ST_GeomFromGeoJSON
ST_GeomFromGeoJSON works only for JSON Geometry fragments. It throws an error if you try to use it on a whole JSON document.
This means you'll want to cut out ...
- 64.7k
6
votes
Accepted
Improving query response speed when joining a non-spatial table with a spatial table on a non-spatial column
⛈️⛈️⛈️
Good news! Your query finishes rather quickly!
Bad news! It takes a long time to get do other stuff.
Given the long elapsed time here when compared to CPU, you can bet that the query was ...
5
votes
Accepted
How to store additional information about each point of a SQL Server geography line string?
All the points in your Geography have one common TrajectoryId, but each one of them has a relative position (first point, second point, ..., nth point).
I would consider that the natural key for ...
- 13.6k
5
votes
Problem getting zips in radius via MySQL
Any attempt to includ A will probably include D, E, F, G. The problem cannot be solved without having an exact path defining each zip code area.
Find such a database, then build a SPATIAL index ...
- 79.4k
5
votes
Accepted
Does MariaDB support ST_Distance_Sphere?
10.2+
This issue has been fixed and backported with MDEV-13467. It's available 10.2.38, 10.3.29, 10.4.19, 10.5.10
Find their support matrix here.
- 64.7k
5
votes
Accepted
For Postgres, efficiently get points within X distance of point Y?
I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this ...
- 64.7k
5
votes
How to setup solution for multi-dimensional distance search with SQL Server spatial data types
There might be some spatial methods that are useful to some degree, but your biggest problem is going to be the Z value:
Z-coordinates are not used in any calculations made by the library and are ...
- 10k
5
votes
MySQL Geo Spatial Query is very slow although index is used
"I have a point column defined as SRID 4326. I'm using MySQL 8.0.12."
I have a similar problem and changing the SRID to 0 improves performance significantly. I don't know if the side effect are ...
5
votes
Accepted
PostgreSQL Geo Spatial Query is slow
First, use EXPLAIN ANALYZE (not mere EXPLAIN) and show the result of \d on the table.
(psql). As a first point this,
ST_GeographyFromText('POINT(47.4667 8.3167)')
should be written as ST_MakePoint(...
- 64.7k
5
votes
Accepted
How do I force SQL Server to use my spatial index through a view?
Window functions & Views
Recently I answered a different question regarding views & window functions but not all answers given there apply here.
Two differences are that this example uses ...
- 16.5k
Only top scored, non community-wiki answers of a minimum length are eligible