282 questions
2
votes
0
answers
145
views
Performance issue with ST_CONTAINS
I have a performance issue with a query in MySQL. I need to compare location data and am trying to use ST_CONTAINS in a join. However, I am having a performance issue as it is quite slow, taking ...
- 46
0
votes
2
answers
61
views
Optimize "diff" movement over spatial grid
I'm trying to find an efficient way to update a spatial grid which contains information about which object is on which grid cell. The objects move. For example:
···AAA···
···AAA···
···AAA···
·········
...
- 53k
1
vote
1
answer
69
views
Is SRID not preserved in MySQL export/imports?
When importing spaces in polygon format from one DB to another something doesn't preserve.
It is not possible to run ST_Intersects successfully on the imported data even though MySQL knows what SRID ...
- 8,598
0
votes
1
answer
101
views
KNN Across categories in postgis using indexing
I have a dataset of points of different types. For every point in the dataset I want to find the closest point in every category. I can achieve this but the compute time is very long and I'm ...
1
vote
1
answer
188
views
MySQL 8.0.32 group by POINT() column with unused index slow compared to 5.7
We recently updated from MySQL 5.7 to 8.0.32 and had to rework a database POINT column that previously was very fast with a prefix-based (??) index, which I believe is not supported in MySQL 8.0.x.
I ...
- 706
0
votes
1
answer
83
views
Two similiar SPATIAL columns with the same INDEX working differently
I'm working in simplifying zip codes polygons in a MYSQL database (v 8.0), I'm reducing the number of coordinates for each polygon.
So, I have a table named zip_city, which contains a column named ...
0
votes
0
answers
58
views
spatial index is not included in possible keys
hi I am using MYSQL 8.0.32
and I have club table and there is club_point column which is point type.
I have 500,000 records in the table.
and as the picture below, I made a spatial index that only ...
0
votes
0
answers
1k
views
Unable to create spatial index due to ORA-29855 with nested ORA-13203:failed to read USER_SDO_GEOM_METADATA view
I'm new to Oracle Spatial and I've been having some issues with creating a spatial index in Oracle Database 19c. When trying to create the index I keep getting:
SQL Error [29855] [99999]: ORA-29855: ...
- 1
0
votes
1
answer
54
views
TSQL Efficient Spatial Query in tiled scenarios
I've got a large table of spatial data representing roads. The data contains a BeginNetworkId and an EndNetworkId which allows us to view the data over time. I.E. When the latest networkId was 2 for ...
0
votes
0
answers
94
views
Spatial Indexing for Nearby Way SQL Server
In my SQL Server database, I have a table Way with columns WayId (uniqueId) and LineObject (Created with expression Geography::STLineFromText('LINESTRING(long1 lat1, long2 lat12'))
I have to find out ...
- 1
1
vote
0
answers
990
views
Spatial Indexing and Bounding Box
I have two tables each with a SHAPE Geom Column, and I'm working on getting the intersected area between every shape in the first table with the second table.
The second table has millions of rows, ...
- 547
0
votes
1
answer
345
views
Spatial Query taking too long to execute
I have a table that contains geometry column for polygon data along with its location.
I am trying to execute a query to find the polygon in which my point resides. The current query that I have been ...
0
votes
1
answer
425
views
Creating a Cross-Schema Spatial Index in Oracle impossible with single user?
The oracle documentation here:
https://docs.oracle.com/en/database/oracle/oracle-database/21/spatl/indexing-querying-spatial-data.html#GUID-8E6AE949-758B-4A5E-9453-CC3D00647497
Talks about creating an ...
- 115
1
vote
1
answer
411
views
Can;t get MySql/Mariadb to use spatial index
This is using 5.5.68-MariaDB. Yes, I know this is very old but I have to use this version for this application for the time being.
The explain and the performance of the query clearly show the indexes ...
- 29
0
votes
0
answers
289
views
Best way to compare performance of R-tree and variants?
I am a beginner looking to measure and compare the performance of R-tree vs R*-tree vs Hilbert R-tree vs Priority R-tree for a school assignment. I am not married to comparing these particular ones, ...
0
votes
0
answers
240
views
Find the nearest or the k nearest line to a given point using spatial index
I have a layer of streets representing streets and some points which are specific events. I want to find the nearest (the first 4) street(s) to each event. My question is about indexing. I can use ...
- 1,034
-1
votes
1
answer
511
views
How do i configure Spatial mode in Hibernate search 6?
I'm in the process of migrating from hibernate search 5 to 6. Many annotation classes that were present in hibernate search 5 have been completely removed in hibernate 6. One of these annotations was @...
- 7,478
0
votes
0
answers
425
views
Spatial Index for a geography column in SQL Server
In a table with almost 50 million rows, there's a geography column.
I've already created a spatial index on that column and when I tried to execute the below query
SELECT
table_name.column_name....
- 31
1
vote
4
answers
388
views
Utilize function-based spatial index in SELECT list
I have an Oracle 18c table called LINES with 1000 rows. The DDL for the table can be found here: db<>fiddle.
The data looks like this:
create table lines (shape sdo_geometry);
insert into ...
- 366
1
vote
1
answer
2k
views
Using H3 to calculate travel times, but where would you get the route?
I am playing around with some location data and came across the Uber H3 library along with this paper on their travel time calculation methodology. There is one thing I can't seem to find an answer ...
- 1,783
1
vote
1
answer
232
views
Create a spatial Index of the shapefiles of a schema in PostGIS using pyQGIS
I am creating a spatial Index for each shapefile I have and then I import them in a schema and the spatial index is missing. How do I create the spatial Index again in the schema?
layers_fimport = ...
- 21
0
votes
0
answers
243
views
How to index road networks using R-trees?
I am new to R-Tree and I want to build a R-Tree indexing for a road network.
The road network have Nodes (node id, longitude, and latitude) and Edges (edge id, source id, destination id, distance ). I ...
- 117
2
votes
1
answer
805
views
How to plot hyperrectangles of scipy.spatial.kdtree
Im trying to perform a KD-tree on a set of x,y coordinate pairs for spatial indexing them. I found scipy.spatial.kdtree to perform this quickly. However I can't seem to manage plotting hyperrectangles ...
- 43
0
votes
0
answers
191
views
Spatial index for Point in SQL Server
Do we need spatial index for point type geometry in SQL Server?
If yes, how Tessellation and grid selections work in point type geometry?
- 15
0
votes
1
answer
472
views
Why I got such error message when trying to create a ttl index in azure cosmosdb?
I'm trying to create some TTL indexes in azure cosmosdb collections (mongo API), these collection's elements are all string or integer, but when I use the index spec
{
"expireAfterSeconds":...
- 77
3
votes
0
answers
398
views
How to create GIST spatial index on postgis with JPA annotations
I am using springboot, hibernate-spatial with postgis database to store and query spatial objects. I wonder if there is a way to create GIST spatial index on postgis with JPA annotations. I have only ...
0
votes
1
answer
328
views
SQL Server STWithin Spatial index does not work
1.Spatial index does not work(Query cost 10s)
SELECT geomFROM cj_pl_l WHERE geom.MakeValid ( ).STWithin (geometry :: STGeomFromText
('POLYGON ((……))', 4547 ).MakeValid ()) =1
2.Spatial index work（...
1
vote
1
answer
299
views
CosmosDB spatial index creation - bug?
I create a container (using CosmosDB Emulator) and then want to add a spatial index for a Point property that will be present within my documents. I am using the Microsoft.Azure.Cosmos (C#) SDK to ...
- 3,963
0
votes
1
answer
230
views
Which spatial Data structure to use when Coordinates of the features is updating frequently? I have tried R tree, 2d Grid
I have N numbers of geographical points and coordinates of the points are being updated frequently. And the number N is very big.
I need to find points within a rectangle.
I have tried searching by ...
0
votes
1
answer
2k
views
how does an oracle spatial index become corrupted?
So I have a table with an Oracle Spatial Index. After some DMLs the index breaks. I don't know exactly when this happens. Most of the time DELETE and UPDATE statements are executed on the table. Does ...
1
vote
1
answer
2k
views
ST_Intersects() query took too long
I'm working on a query using the PostGIS extension that implements a 'spatial join' work. Running the query took an incredibly long time and failed in the end. The query is as follows:
CREATE INDEX ...
- 493
0
votes
1
answer
566
views
Which NetTopologySuite Index is optimal for include and exclude rectangle search
I have a following problem: huge number of points and huge number of queries which need to provide as fast as possible any point that is inside include rectangle (red) and outside exclude rectangle (...
- 7,137
1
vote
0
answers
140
views
Encode interval as string for intersection query
The goal is to check if two intervals intersect one another. Easy... right? Just plug this guy in and we can move on?
intersects = itemStart < queryEnd && queryStart < itemEnd
Not so ...
- 11.1k
2
votes
1
answer
2k
views
MySQL 5.7 -> 8 migration: how to change SRID of existing MySQL geometry column?
I'm migrating a large database from MySQL 5.7 to 8. It contains geometry columns and spatial indexes. It's a live system and so downtime for the migration is an issue.
MySQL 8 ignores spatial ...
- 137
0
votes
1
answer
362
views
Mac m1, rtree 0.9.7, spatialindex 1.9.3, symbol not found
() isikkaplan@iamhere# python3 manage.py loadfixtures -s=testing fixture fixtures/knock/0_ct.json -v3
Traceback (most recent call last):
File "/Users/isikkaplan/Desktop/code/---/-/--/manage.py&...
- 3,042
0
votes
1
answer
217
views
How to partition a spatial index in MySQL or combine it with a BTREE index?
I have a table in MySQL that contains the individual points of geographic line strings that I am showing on an interactive map. Among others, the table contains the following fields:
`pos` point NOT ...
- 7,969
5
votes
3
answers
3k
views
Gist index on Postgres/PostGIS still slow
I am not an expert at Postgres/GIS subjects and I have an issue with a large database (over 20 million records) of geometries. First of all my set up looks like this:
mmt=# select version();
-[ ...
- 485
0
votes
0
answers
99
views
Using SQL, get ALL information about spatial index, 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....
- 1,435
0
votes
0
answers
158
views
Why my execution plan is the same with index and without and sometimes has missing index?
I tested my procedure with and without index to compare the result. My problem is I dont know why my spatial index in noy in the execution plan when I test but sometimes in the execution plan there is ...
- 29
0
votes
1
answer
251
views
Can you make Elasticsearch 7.x spatial queries more precise?
Background:
We have ~140 million polygons split into 5 indices (region-[1-5]) with 2 shards each. It was loaded with ES 7.10. The field containing the polygon is named 'shape' and is mapped as a ...
- 53
2
votes
1
answer
552
views
Using boost::geometry::index::indexable within an IndexableGetter in Windows
Building on top of this example:
https://www.boost.org/doc/libs/1_75_0/libs/geometry/doc/html/geometry/spatial_indexes/rtree_examples/...
- 21
1
vote
0
answers
535
views
Performing a location proximity search on a database using S2 Geometry Library
I am working on a project that requires fast performing proximity queries on a database with location data.
In my database I want to store locations with additional information. Idea is that user ...
1
vote
1
answer
292
views
Elasticsearch spatial search with terms
I am running a query on 140+ million documents with spatial data. Purely spatial queries are extremely fast (sub 1s). Adding a wildcard to the same geometry results in the query taking ~10-20s. I ...
- 53
0
votes
0
answers
488
views
How do I improve SQL spatial query performance?
I am running a spatial query inside a SQL procedure, and I am experiencing some inconsistent performance times. I've have tried to use various spatial indexes, however I cannot seem to get the query ...
- 51
0
votes
0
answers
539
views
How to perform spatial-autocorrelation test on mixed-effect model?
In my data frame I have my observations in blocks of time so in order to take the effect of year I decided to works with mixed effect model by specifying the year of sampling as random effect. This is ...
- 29
0
votes
1
answer
897
views
Getting different test results between Moran.I and moran.test
I tried to run Moran's I test for the spatial autocorrelation test by using the function Moran.Ifrom the package ape and moran.test from the package spdep I got different results by applying the two ...
- 29
0
votes
0
answers
75
views
Efficient algorithms for building an immutable bounding voume hierarchy?
I am experimenting with building a bounding volume hierarchy using only immutable data-structures. I hope to use the tree for broad-phase collision tests in a real-time simulation.
The tree structure ...
- 37.8k
0
votes
0
answers
442
views
How to use Spatial Smoothing on relativities at postcode level in Python
I'm after some help with how I can use Spatial smoothing on my data which I have attached.
What I'd like is for neighboring postcodes to not have such a large difference in relativities.
Just to ...
1
vote
1
answer
976
views
How to create a Gin index on a geometry column in Postgresql?
According to pgAdmin 4 4.21 documentation » Creating or Modifying a Table »
Select gin to create a GIN index. A GIN index may improve performance when managing two-dimensional geometric data types ...
- 2,377
0
votes
0
answers
225
views
build KDtree: non-sensical error during query
I'm trying to assess an interpolation scheme, so I'm looking to compare the input points (red) to the resultant grid (blue). I've built two K-D trees looking to find the nearest blue dot to each red ...
- 163