Skip to main content

Questions tagged [spatial-index]

In a database, a mechanism for optimizing access to data based on the spatial column(s) of a data table.

Filter by
Sorted by
Tagged with
1 vote
0 answers
41 views

Create PostGIS column of a specific H3 hexID/resolution

I've used H3 in the past just for speeding up my spatial queries, however I'm looking for the best way to store the index information itself alongside my data. Something like: CREATE COLUMN H3res14 --&...
Lucian B's user avatar
0 votes
0 answers
20 views

Is SRID not preserved in MySQL export/imports?

I have a database with spatial data stored as Polygons that I run queries such as ST_Intersects against. However if I export this database and import it the SRID appears to be lost, queries that ...
Mikael's user avatar
  • 1
1 vote
0 answers
68 views

Spatial indexes not being used despite existing

Here is my query: explain analyze with pings as ( -- ================= SELECTING JOINED data with join conditions -- ================= -- ================= -- ================= -- ================= -- ...
analyst92's user avatar
  • 857
2 votes
1 answer
42 views

Does geopandas overlay use spatial indexing

I have a general question about geopandas overlay method. I want to know whether it already uses spatial indexing or not. If not, how would I apply indexing? Background I do an intersection with ...
tetje's user avatar
  • 99
1 vote
1 answer
68 views

Does QgsSpatialIndex() support Z values, respectively 3D distances?

I am wondering if QgsSpatialIndex() does support Z values, respectively 3D distances? If too short to read: Looking at the underlying libspatialindex I cannot find information neither. However, rtree ...
MrXsquared's user avatar
  • 34.9k
4 votes
3 answers
336 views

PostGIS distance query using a dynamic radius

(Background: I'm more of a MySQL guy, I've only been using PostGIS for a few months to solve this particular problem set.) I have a table that contains a point and a radius: id | location ...
Daniel Orner's user avatar
3 votes
1 answer
173 views

Fast selection of intersecting geometries in GPKG file

I have a GPKG file, example.gpkg, with 2 layers, base and circles. There is only 1 geometry in base, and there is a large number of geometries in circles. I would like to select all the geometries in ...
zabop's user avatar
  • 2,224
8 votes
2 answers
369 views

Fast subtraction of geometries in GPKG file

I have a GPKG file, with 2 layers, p and q. I would like to create a third layer, r. I would like r to contain those parts of p which do not intersect with q. We could phrase this as r=p-q. Below is a ...
zabop's user avatar
  • 2,224
2 votes
1 answer
111 views

Trying to get accurate results using ST_DWithin with srid -1

I have a table where each row has a point Geometry(POINT(...)) [long lat]. These geometries don't specify an srid. Which means we're using a cartesian plane to do the calculations. All of this data is ...
Bombadillio's user avatar
1 vote
1 answer
165 views

Rebuild spatial index after massive changes

I have a question about spatial indexes. I'm using SQLite with VirtualSpatialIndex. I have a main table that contains millions of polygons. Let's say every 4 months I have to get a completely new ...
Sch's user avatar
  • 41
1 vote
0 answers
38 views

Are there any database that support spatial-temporal indexes? [closed]

I am looking for high performant database that support spatial-temporal index. It would be ideal if spacial part of it indexed based on S2 geo library.
user652061's user avatar
1 vote
0 answers
180 views

How to "create spatial index while using" Modeler?

It was possible in previous QGIS version but since a while, if i add Create a spatial index in the modeler i get a warning : L'algorithme “Créer un index spatial” ne peut pas être exécuté dans un ...
GuZzO80's user avatar
  • 11
2 votes
0 answers
61 views

ST_DWithin taking an unreasonable time with geographical spatial indexing

Here are the relevant CTE's from my query: campaign_ping as ( select * from campaign_pings cp left join campaign_pings_validity cpv on cp."ID" = cpv."pingID" where cpv."...
analyst92's user avatar
  • 857
4 votes
1 answer
110 views

Do both arguments of ST_DWITHIN have to be spatially indexed geographical columns to efficiently check proximity in meters

Here is a snippet from my code: ST_DWithin(c.convex_hull::geography , cp."geoJson"::geography, 20) Please note both "convex_hull" and "geoJson" are geometry columns (...
analyst92's user avatar
  • 857
2 votes
1 answer
136 views

PostGIS, creating spatial index on box2d column failes

I would like to store bounding boxes in a column of my table. Thus I wanted to use the box2d data-type for that column. However it seems like I can't create an index on that column. Here is my ...
Humppakäräjät's user avatar
1 vote
1 answer
791 views

How to speed up geopandas.sjoin_nearest using spatial indexing?

I have 2 data frames with routes and linestrings: df1 = { "Route": ["AL013-AL015", "AL013-AL014", "AL013-AL011"], "Linestring": ["LINESTRING (...
Andrei's user avatar
  • 13
0 votes
0 answers
147 views

Spatial index creation: MySQL vs Spatialite

I'm just starting to learn MySQL and I have a script that loads a large CSV file (4 million rows) into a table and then adds a spatial index to the GeometryLatLon column. The spatial index creation ...
Rojj's user avatar
  • 175
0 votes
2 answers
278 views

Double indexing and looping over raster lists in R

I have a list of 63 rasters. They are the same extent, resolution, projection, etc. Because I extended 62 rasters to the maximum extent of the 63rd, I introduced NA values. However, these are relative ...
Nick Masto's user avatar
0 votes
0 answers
151 views

Which collection data is suitable for calculating soil moisture index using Landsat 8?

I am trying to compute soil moisture index using Landsat 8 collection 1 level 1, but it seems that I am not computing it properly. My range is not 0 to 1, as it should. Values of my variables can be ...
Chris's user avatar
  • 123
1 vote
1 answer
320 views

How to create a spatial index in a GeoPackage with GeoTools?

I want to write a GeoPackage file with GeoTools and create a spatial index in the end. After consulting the documentation and also the implementation I still can not figure out a way to do this using ...
evod's user avatar
  • 323
0 votes
0 answers
391 views

Shannon diversity index in ArcGIS Pro

As my data sets are very large I’ve been unable to compute anything in R. I’ve been successful doing things manually in ArcGIS Pro. Now I’m attempting to calculate the Shannon diversity index for ...
user434180's user avatar
3 votes
0 answers
417 views

How to create a GIST index on locations within a JSONB array column?

I have a JSONB column with an array of locations. I want to do a Postgis GiST index on each of the array items. For example: [{ "location":{ "type": "Point", ...
Darko Stanimirović's user avatar
4 votes
0 answers
266 views

Does GeoPandas generate a PostGIS spatial index automatically?

It is easy to save a GeoPandas dataframe to a PostGIS database using the gdf.to_postgis(...) command. But, it is not clear to me if it automatically generates spatial indices or if these need to be ...
jtam's user avatar
  • 357
1 vote
1 answer
39 views

Same Spatial Query on nodes table gives different read time

I have created 2 databases. First one has PGSnapshot Schema which looks like this. On this I fire query select id,ST_AsText(geom) from nodes where ST_Intersects(geom , ST_GeomFromText('POLYGON ((-...
Yasha Jadwani's user avatar
3 votes
2 answers
501 views

Finding nearest polygon in PyQGIS using QgsSpatialIndex

I am trying to write a custom processing tool for QGIS which joins the nearest attributes with some conditions. Therefore I'd like to use a QgsSpatialIndex to increase performance. This works ...
MrXsquared's user avatar
  • 34.9k
1 vote
0 answers
220 views

Get map to use function-based spatial index (ST_GEOMETRY)

SDE.ST_GEOMETRY — Oracle 18c — ArcMap/EGDB 10.7.1: I have a view that returns the startpoint of lines as an ST_GEOMETRY column: create or replace view bc_atn_startpoint_vw as ( select objectid, ...
User1974's user avatar
  • 1,172
3 votes
0 answers
841 views

GeoPandas spatial index - passing RTree parameters to index

I am quite familiar how to work with the Toblerity RTree library: import fiona import rtree with fiona.open(input_path,'r') as source: p = rtree.index.Property() p.filename = "index....
MartinT's user avatar
  • 876
0 votes
0 answers
615 views

Spatial indexes with RTree for polygons

I am trying to understand about how R-tree works for polygon data. In other words, polygon is a list of vertices (points), however, I am wondering how spatial index can recognize the order of points ...
Masoumeh's user avatar
3 votes
0 answers
123 views

Set a spatial index on a set of rows (without table) in PostGIS

I got a giant geometry that I retrieve from an API and I break it into smaller pieces with ST_Subdivide. Right after, I'm inserting it into a temporary table (which is indexed and only contains a ...
Bil11's user avatar
  • 81
0 votes
1 answer
128 views

Producing Shadow Indexes in Google Earth Engine using maximum values from a ImageCollection

I'm trying to generate a Shadow Index using the maximum values from a image composite (ImageCollection). Here is the link with the code: https://code.earthengine.google.com/...
Christoph Correia's user avatar
7 votes
1 answer
2k views

Actual PostGIS query execution time MUCH slower than expected

I've run into a performance problem from which I'm not sure how to move forward. I have a table with ~150M rows. And I have a query that in EXPLAIN has a cost of just cost=0.55..209.36, but in EXPLAIN ...
kszafran's user avatar
  • 111
1 vote
1 answer
452 views

Getting index of special pixel (x and y position) or transform lat/lon of pixel to index in Google Earth Engine

I am trying to find the index (x and y pixel position in an image) of a pixel (ex: the pixel with max DN value). however, it has been challenging for me. My code is: var geometry3 = ee.Geometry....
mojtaba's user avatar
  • 11
1 vote
1 answer
1k views

How to speed up PyQGIS code for finding intersection of features in the same layer

I an deleting one of the intersecting polygons from a vector layer using PyQGIS code mentioned below. It is too slow as I have many features in the layer. Is there a way to speed it up? index= ...
vigna purohit's user avatar
0 votes
1 answer
628 views

Adding multiple Image using Google Earth Engine

I want to make NDBI indices to a certain location. But when I run the code it can only show part of it //####################### Import SHP ####################### var countries = Indonesia //Import ...
imstilllearning's user avatar
3 votes
0 answers
1k views

How do I use spatial index to speed up a model?

I want to process data with a model in QGIS. It works fine, but the bigger the datasets get, the longer it takes. The reason is, I use GeoJSON layers and they don't have a spatial index. That is why I ...
Enzo Baldini's user avatar
  • 2,337
1 vote
0 answers
189 views

Optimized query to find nearby locations

I am working with the GeoNames database which has around 10 million rows of locations including longitude and latitude. I have tested around 6 various nearby location queries including spatial indexes....
Ryan NZ's user avatar
  • 119
4 votes
1 answer
384 views

Parallelization of rtree index

Is it possible to share rtree index in memory with multiple processes? I have succeeded to use rtree in multiprocessing environment with joblib, but the problem is that each process has its own copy ...
r0x0r's user avatar
  • 161
4 votes
1 answer
361 views

Choosing appropriate index for PostGIS data

I have been reading this blog by Paul Ramsey, and considering my own data. https://blog.crunchydata.com/blog/the-many-spatial-indexes-of-postgis I have a regular grid covering an area of the world. ...
TheRealJimShady's user avatar
1 vote
0 answers
583 views

Understanding Rasterio index coordinates

So I read in a .tif image with rasterio and what I get out when printing dataset.read(1) is what I understood a coordinate system in the "red" band of the image. The coordinate system is in ...
Oliver's user avatar
  • 195
0 votes
2 answers
734 views

Index‐Based Built‐up Index (IBI) no in the specified range

I'm trying to calculate Index‐Based Built‐up Index (IBI). This index has been mentioned in A new index for delineating built‐up land features in satellite imagery. As mentioned in the paper, the range ...
Badal's user avatar
  • 329
5 votes
0 answers
236 views

(QGIS 3.20) Adding features while spatial index is created makes them disappear

I am encountering a weird behaviour in QGIS 3.20 on Windows. Steps to reproduce: Create an empty project Create a SHP layer. Add a few features. Save the changes to disk At this point everything is ...
peauc's user avatar
  • 51
5 votes
1 answer
540 views

Is a spatial index on a ST_Transformed geometry simply ignored when the transformation has the same target CRS than CRS of the geometry?

I have such vanilla index on a PostGIS table: CREATE INDEX polygons_gix ON public.polygons USING gist (geom); Because of this: If using more than one transformation, it is useful to have a ...
swiss_knight's user avatar
  • 10.4k
7 votes
3 answers
1k views

Optimizing an intersection between a single massive multipolygon (WKT) and many features from PostGIS

Context I have a massive MultiPolygon (~90'000 vertices, super complex, spreading over 60km², with lots of holes and parts) resulting of some Python computation. This MultiPolygon is coming as a WKT ...
swiss_knight's user avatar
  • 10.4k
2 votes
1 answer
869 views

How to use spatial indexes in SQL queries made on geopackages

I'm trying to learn how to write geopackage SQL queries so that the r tree index is used, but I can't seem to find any good information. I'm just trying to write a simple query in the qgis db manager ...
Alex's user avatar
  • 187
5 votes
1 answer
2k views

PostGIS: Does re-creating a spatial index after inserts or updates affect performance?

Creating a spatial index on a geometry field is very important. The docs also state that you should perform a VACUUM ANALYZE after large update operations. But is it ever necessary to drop an existing ...
Encomium's user avatar
  • 3,143
2 votes
1 answer
1k views

GEE- Calculating Burned Area Index for Sentinel 2 (Filipponi et al. 2018) but getting binary result than index range

I am trying to calculate BAIS2 index for sentinel-2 collection over a study area in Google Earth Engine. I tried to create a function using expression but end result is binary image rather than index ...
Monish's user avatar
  • 131
2 votes
1 answer
163 views

Grab/Index the raster bands before and after a unique band for each pixel (depth-wise) in a raster stack

I'm working with a time series stack of 28 raster bands. Each band is an image for a specific date, spanning all of the year 2020, and they are stacked in order. The stack (change_point_raster) is in ...
KatieGeo's user avatar
3 votes
0 answers
252 views

BRIN Index on PostGIS Geography

I have about 1.5 billion points in a geography column which represent topography. I'll be retrieving all the points that are within a certain distance of a given long/lat. I was able to create a GIST ...
Opossum Posse's user avatar
4 votes
1 answer
522 views

Selecting features that intersect features of another polygon with spatial index

Based on the answer of @mgri here: https://gis.stackexchange.com/a/224954/164953 I tried to select the features of a multilines layer intersecting less or more than 2 features of a multipoints layer. ...
Linda's user avatar
  • 341
2 votes
1 answer
264 views

PostgreSQL/PostGIS KNN query using <-> operator in ORDER BY clause not using index

I am trying to set up a KNN query using the <-> operator in the ORDER BY clause and the query planner does not appear to be using the spatial index as expected. I am not using bleeding edge ...
Evan Rosenlieb's user avatar

1
2 3 4 5