Questions tagged [spatial]
Storing, querying, and indexing spatial data (including geometric and geographic representations).
267
questions
0
votes
0
answers
25
views
Getting "The Server could not load DCOM" on accessing any geometry column on SQL Server
I have a view that creates geometry points from latitudes and longitudes in the table.
This was running fine till yesterday but today I started getting the error "The server could not load DCOM.&...
0
votes
1
answer
52
views
Error Code: 3548. There's no spatial reference system with SRID 1498173264 - MySQL
I am trying to run a mysql query below:
select *
from geo
where st_distance_sphere(Point(-118.2842557, 34.0676834), `geometry`) <= 1609
where geometry is of type Polygon.
and get an error: Error ...
- 129
0
votes
1
answer
74
views
Optimizing geolocation query in Postgres with earth_box and earth_distance
I'm trying to optimize my geolocation query for a table of addresses of ~862k rows within a search radius using earth_box. My first initial query is not too terrible:
explain analyze SELECT
id
...
- 3
0
votes
0
answers
63
views
MySql query not using spatial index
We have the below query that we are calling to find all rows within another table's row geometry. When I do a explain, it shows the spatial index spidx_table2 as a potential index, but says it is ...
- 156
1
vote
1
answer
101
views
extracting ordered list of spatial points from JSON into PostgreSQL array
i have JSONB data in PostgreSQL that looks like the following:
{"datasetGPolygon": [
{
"datasetGPolygonOuterGRing": {
"gRingPoint": [
{
&...
1
vote
2
answers
98
views
db<>fiddle (Oracle): Query works in 18c, but not 21c
I have a Oracle query that I use to analyze the vertices of SDO_GEOMETRY polylines:
WITH lines AS
(SELECT SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING ((0 5 0, 10 10 10, 30 0 33.54),(50 10 33.54, 60 10 ...
- 1,387
0
votes
2
answers
27
views
Database design - GIS point data
I am considering whether I should add columns to my existing tables or use a new table. The database is about birds, there locations over time and there nesting locations.
The data that I need to ...
- 101
0
votes
0
answers
88
views
MongoDB vs PostGIS for geospatial data
My main concern is that I was I/O and bounding circle queries to be lightning fast. Does MongoDB spatial offer any advantages over POSTgis?
0
votes
1
answer
28
views
How to request an enhancement to SDO_GEOMETRY / Oracle Spatial
I want to suggest a few ideas to Oracle about enhancements to SDO_GEOMETRY / Oracle Spatial.
Example:
Requesting a function to select vertices, including the multi-part numbers of SDO_GEOMETRY ...
- 1,387
0
votes
0
answers
76
views
SQL Server 2019 Express for Linux on Ubuntu 20.04 - Bug - CU 13 - c0000005 EXCEPTION_ACCESS_VIOLATION when specifying @wkt in Table-valued function
Pretty sure I've found a bug in SQL Server 2019 (Express) for Linux (CU 13, version 15.0.4178.1 running on Ubuntu 20.04 LTS). After the following table-valued function dbo.bTest is created:
use devdb
...
- 121
0
votes
2
answers
62
views
Select SDO_GEOMETRY line vertices as rows -- using recursive WITH clause
I have an Oracle 18c table that has an SDO_GEOMETRY column (lines):
create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);
insert into a_sdo_geometry_tbl (line_id, shape)
...
- 1,387
1
vote
1
answer
726
views
Why is my spatial query slower in Postgres 13 than in Postgres 11?
Postgres versions
PostgreSQL 13.2 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.2.1_pre1) 10.2.1 20201203, 64-bit
PostgreSQL 11.11 (Debian 11.11-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by ...
- 113
1
vote
1
answer
63
views
Is it the right scenario to use spatial index?
I have used spatial index recently on one table to improve query which filter records by given date range, reference identifier and whether they are inside polygon.
Here's the example of the query
...
- 187
0
votes
1
answer
220
views
Extract SDO_GEOMETRY line vertices into a nested table column (in a query resultset)
Oracle 18c:
In a related post, I demonstrated how to use the GetVertices() function and the Table() function to extract the vertices of an SDO_GEOMETRY line:
Select SDO_GEOMETRY line vertices as rows
...
- 1,387
0
votes
1
answer
70
views
Select SDO_GEOMETRY line vertices as rows
I have an Oracle 18c table that has an SDO_GEOMETRY column (lines):
create table a_sdo_geometry_tbl (line_id integer, shape mdsys.sdo_geometry);
insert into a_sdo_geometry_tbl (line_id, shape)
...
- 1,387
0
votes
1
answer
146
views
MySQL table + partitioning + spatial data
We have a table that currently has every postal address for a single state, currently around 13 mil records. Among postal addresses, it also contains spatial coordinates for each address along with ...
- 156
2
votes
1
answer
176
views
Data truncation on spatial query after updating from MySql 5.7 to 8.0
I have the following table:
create table places
(
id bigint auto_increment
primary key,
position point SRID 4326 not null
);
create spatial index position
on places (position);
--...
- 151
0
votes
1
answer
23
views
A column of database type "geometry" results in two extra columns hidden from ssms
If I create a table like
create table test2 (foo geometry)
foo will show up in the table designer as expected
but when I query sys.columns for column information...
select
t.name as TABLE_NAME,
c....
- 634
0
votes
1
answer
161
views
Get ALL information about spatial index using SQL, including CELLS_PER_OBJECT and USING GEOGRAPHY_AUTO_GRID
When I create a spatial index I can specify some extra parameters, e.g.
CREATE TABLE dbo.Test(
Id bigint not null,
Polygon geography not null
)
GO
CREATE SPATIAL INDEX IX_Test_Polygon ON dbo....
0
votes
0
answers
201
views
Cannot use 'STWithin' as it can't be found for type 'Microsoft.SqlServer.Types.SqlGeography' (SQL Server V14)
I am currently using the STIntersects method to spatially match a geographic point to a geographic area. I am working with Geography types here not Geometry.
Historic code from the place I work shows ...
- 123
1
vote
3
answers
323
views
Query very slow when using spatial with radius
I have the following statement which is very slow (takes 6-30 seconds to get 21 records among 800k) using SQL Server 2019 Standard running 32GB ram (server does other things too) with 6 cpu cores ...
- 335
0
votes
1
answer
165
views
pg_restore using PostGIS/PostgreSQL Dump Changes Data in Geography Column
Two PostGIS databases (PostGIS 3.0, PostgreSQL 13.1) were setup on two separate machines to be as close to each other as possible using Docker images.
A dump of the database was taken from the first ...
- 1,051
0
votes
1
answer
191
views
How to load geometry point tables in sql spatial
Need help loading point data in sql spatial.
Similar to this post but I really dont know how to get XY data to be accessible by sql
I want to do what was done in the initial part of this post (...
- 1
1
vote
0
answers
73
views
Improving performance in a query with median
I have the following query that I would like to speed up, I ran it through the Tuner but it didnt suggest any new indexes so I am looking for some help to improve it. (using sql server 2019)
Actual ...
- 335
1
vote
1
answer
139
views
Searching for a close numeric match on spatial coordinates
I have a large Postgres table contain geographic positions (latitude and longitude) both fields are indexed, and both are defined as NUMERIC(9,6).
If I run a query looking for an exact position match, ...
- 893
0
votes
2
answers
514
views
Materialized view with FAST refresh on remote table: How to include a GEOMETRY column?
I want to create a fast refresh materialized view (18c) on a remote table. The MV would also have a GEOMETRY column.
Options for the GEOMETRY column datatype include:
ESRI's proprietary ...
- 1,387
0
votes
4
answers
56
views
Postgis reporting a point is 1216m away from itself using ST_Distance [closed]
I have a table containing a geometry column in 4236 projection. I take the ST_X and ST_Y values from one row, and create a point from them. I then transform the point and the original geometry column ...
- 59
1
vote
1
answer
282
views
How to speed up SQL Server 2014 Spatial functions
I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database.
Note that I have simplified the names of the tables and columns.
To illustrate I have ...
- 41
1
vote
1
answer
47
views
Is there a plugin to add a map behind the spatial results?
Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a ...
- 367
0
votes
2
answers
450
views
Improving Speed of Cross Apply
I posted a question on how to create a relation between 2 tables based on a spatial field
https://stackoverflow.com/questions/63769885/t-sql-passing-column-to-subquery-for-filtering
The performance is ...
- 335
0
votes
1
answer
33
views
Get count of near points based on 2 unrelated tables
I have 2 tables, cities and listings and there is not FK relationship or any other field that I can create a relationship based on.
They both have a geography field.
I would like to get list of all ...
- 335
1
vote
2
answers
949
views
Geo Route - Best way/db to store route path
We are developing an app in that UI need to show a path where a truck driver drives the truck.
Driver mobile will continuously send (example every 10 seconds) their live location.
Which database is ...
- 125
0
votes
0
answers
196
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 ...
- 156
1
vote
1
answer
791
views
Why does MySQL think I'm using SRID 1?
I can run the query on my local machine no problem (Server version: 10.4.6-MariaDB), but when I run it on my remote one (Server version: 8.0.17 MySQL) it poops out this error message:
ERROR 3548 (...
- 503
2
votes
1
answer
111
views
Best indexing strategy for spatial join and ranged lookup
Context
We have two tables (not the real tables used in the examples below. They are from a toy DB im using for testing):
Incidents_2 (Columns of interest are a geom, and a reported_at (int8))
...
3
votes
1
answer
688
views
How to add Spatial Reference ID 28992 to MSSQL server?
I want to use Spatial Reference ID 28992, as found on EPSG, in MS SQL Server. How can I add this to MS SQL Server?
My end goal is to get this dataset into MS SQL Server, with the Geography Markup ...
- 131
2
votes
0
answers
372
views
MySQL 8 - Optimize query using ST_Contains or ST_Within
I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows:
UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, '...
- 133
0
votes
0
answers
49
views
PostGIS buffer in miles not lat/long degrees
I am trying to take two points, add a buffer around them (in meters), draw a line between them, draw a buffer around that, and then query whether items are inside the area of the circles expanded line ...
1
vote
1
answer
64
views
How to move geographical data from MS Access to SQL Server
For 20 years I've been working with MS Access, with much VBA involved.
I have a lot of geographical data in Access and I'm preparing to move to SQL Server because it supports geographical data types, ...
- 11
0
votes
0
answers
49
views
In 8.0.17 EPSG Spatial reference systems are not available at installation? [duplicate]
In 8.0.17 INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS is not prepopulated with ORGANIZATION="EPSG" records, leaving the table with only a single record, that for the default SRID=0.
A count in ...
- 141
2
votes
3
answers
1k
views
MySQL 8 not using Spatial Index?
I have an issue where whatever I do I cannot get MySQL to use a spatial index.
A query like the following then takes 5 seconds!
Any way to force this to use the index at all?
WITH ranked_reports ...
- 123
1
vote
0
answers
110
views
MySQL 8.0.18 - ST_Area returns invalid value
I have a problem with ST_Area function in MySQL 8.0.16.
The result of the procedure call returns a different result than any library used to calculate the area of a polygon.
Example:
I have a ...
- 111
3
votes
3
answers
1k
views
RDS MySQL 8.0.16 has empty "INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS" table
On aws RDS I have an mysql 8.0.16 instance running.
My problem is now, that the INFORMATION_SCHEMA.ST_SPATIAL_REFERENCE_SYSTEMS table is empty.
This is a problem for me because I would like to create ...
6
votes
1
answer
372
views
MSSQL Geography STIntersects returns 1 when 0 is expected
I'm experiencing an MSSQL Geography issue I can't explain. Assume that I'm math-challenged, but it seems like the following point should NOT intersect the following polygon:
DECLARE @t_point ...
- 63
1
vote
0
answers
20
views
Reducing contiguous values in geographical data to a set of polygons?
I've got a MySQL table with about 200,000 rows in it. The table has four columns, lat, lng, and value. value is just a percentage, and represents your chances of seeing an aurora in the night sky for ...
- 113
1
vote
1
answer
754
views
How to create Polygon from Point datatype in MySQL?
I have a table like this:
CREATE TABLE `aois` (
`aois_id` int(11) NOT NULL DEFAULT '0',
`WS_A` point DEFAULT NULL,
`WS_B` point DEFAULT NULL,
`WS_C` point DEFAULT NULL,
`WS_D` point DEFAULT ...
- 33
2
votes
1
answer
257
views
Efficient way to store geographical routes in MongoDB
I need to store a taxi route into MongoDB, however I'm not sure what is the best way to do this.
Since the route is only needed for the taxi order, the first idea I had is to store it in the order, ...
0
votes
1
answer
1k
views
Sharding a MongoDB instance by a spatial key and a date
This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries ...
- 103
0
votes
1
answer
58
views
Oracle GIS database replacement [closed]
My company has a huge (hundreds of millions of records) Oracle database of GIS/temporal information. We are trying to switch to an open source alternative.
If it helps, we imagined the following ...
- 101
2
votes
1
answer
3k
views
RDS: permission denied for relation spatial_ref_sys when copying
I am copying tables using ogr2ogr from Oracle to PostGIS. When I run try to load these I get,
ERROR 1: ERROR: permission denied for relation spatial_ref_sys when copying <table>@AWS
- 213