Questions tagged [spatial-index]
In a database, a mechanism for optimizing access to data based on the spatial column(s) of a data table.
190
questions
2
votes
1answer
32 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 ...
3
votes
1answer
61 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 ...
1
vote
1answer
44 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 ...
0
votes
0answers
23 views
Calculateg geo-hash index for bounding box
I have a set of aerial images and for each image, I have the four corner coordinates (let's say, (a1,b1), (a2,b2), (a3,b3), and (a4, b4). Thus, I know the bounding box for each image (please refer to ...
2
votes
1answer
28 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 ...
3
votes
0answers
80 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 ...
4
votes
1answer
69 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.
...
2
votes
1answer
52 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 ...
1
vote
0answers
81 views
Creating indexes with PostGIS using all CPU cores
I have to run a whole bunch of GIST index creation, on some tables containing lots of records (scale of many countries, OSM data):
CREATE INDEX CONCURRENTLY IF NOT EXISTS osm_footways_gist ON ...
0
votes
0answers
25 views
Creating Quadtree/Space Filling Curve Index atop GeoTiFF
I'm a complete beginner in GeoTiFF manipulation. I'd like to get some help in possible Java/Python/Scala project which performs spatial indexing (Quadtree, Space Filling Curves) atop given image(s). I ...
3
votes
2answers
86 views
PostGIS- slow spatial UPDATE queries on 50mil rows
PostgreSQL 11.8, AWS hosted RDS instance, SSD, 8GB RAM, 3.0 GHz Intel Scalable Processor. Plenty of storage space.
I'm finding running updates on a large table using spatial queries is taking a very ...
1
vote
1answer
46 views
Optimising PostGIS ST_DWithin across many tables for existence only
I have 13 tables containing MultiPolygons in the same projection (27700). I am trying to write an efficient query to get back a Boolean for:
Is one polygon in table A within X meters of any polygon in ...
0
votes
1answer
44 views
Can the QGIS spatial index be used to evaluate points contained in multiple polygons of shapefile layers using PyQGis?
I want to use the spatial index of QGIS to improve the performance in the spatial query of multiple points in multiple polygons, both are shapefiles, but has not given me good results in execution ...
1
vote
0answers
68 views
Using spatial indexes on point to polygon intersection query in SQL Server
I have a table (tab_point) with about 5 million points and another table with a few thousand polygons (tab_polyg). I need to find what points intersect with what polygon (to establish a count of ...
0
votes
1answer
38 views
ST_GEOMETRY: spatial column must appear immediately after spatial relationship function for spatial index to be used
The docs for SDE.ST_GEOMETRY have a blurb that says the following:
When are spatial indexes used with ST_Geometry?
...the spatial column must appear immediately after the
spatial relationship ...
0
votes
1answer
40 views
What is name of dataset in GEE to estimate level of urbanization for each county (district) in a country /region?
I am epidemiologist, not remote sensing expert. I am interested to find association between disease incidence /reported cases with urbanization (hypothesis " more bigger cities , more incidence&...
0
votes
0answers
19 views
Postgresql POSTGIS not using index field while having GIST index on geometry field [duplicate]
I have around 2M rows on my PostgreSQL table with GIST index on geo_point field but while I try to use ST_Distance or ST_DWithin it is not using the index instead it is doing Parallel Seq
Indexed ...
1
vote
0answers
59 views
PostGIS: Speed up intersect by filtering first
I have a table with 20 million polygons where queries take some time (with an gist index on the geometries). I thought I could speed up the queries by first filtering on an additional id (selectivity: ...
0
votes
0answers
9 views
Index accelerating complex feature queries and geometric computations?
As far as I know, the index on geospatial database only optimizes lookup of features, not computation themselves, eg, it will acceleration intersection between a large number of polygons with a small ...
2
votes
1answer
57 views
Function-based spatial index: Use sdo_geometry() constructor instead of custom function?
I have an Oracle 18c table that has XY columns, but not a SHAPE column. I've created a query on the table that dynamically generates an sdo_geometry SHAPE column.
sdo_geometry(2001, 26917, ...
0
votes
0answers
19 views
Fragmentation in multicoloum geometry data type
I have a table with place-identifier(big int) and geometry column combined together as b-tree unique index in PostgreSQL. Upon insertion of 20k records the dB table is getting fragmented around 45-50%....
0
votes
1answer
36 views
Determining area size of grid (reference grid) in ArcMap [closed]
How do I determine the area size (square kilometres) of a customised reference grid in ArcMap 10.7?
I have tried Data Driven Pages and it is not working for me. The idea is to know the area size of ...
6
votes
0answers
155 views
When NOT to create spatial index
As I realized once again connected with this answer to Joining attributes by location using QGIS, creating a spatial index can result in huge performance improvements.
However, I was wondering why ...
0
votes
0answers
65 views
Spatial indexing of GeoTIFF images (Java Library)
I have a requirement to spatially index each pixel of GeoTIFF images and store them in a scalable database. I intend to perform this in the following manner.
Divide each GeoTIFF into smaller tile ...
0
votes
0answers
221 views
Spatial Index warning from Spatial Query
I'm trying a spatial query using QGIS version 3.10 using a buffer layer and a set of species records. I wish to know how many species records fall within the buffer. When I perform the spatial query ...
1
vote
1answer
84 views
Improving PyQGIS distance matrix efficiency
I have written a custom processing tool, which I use within a large graphical model in QGIS. I am looking for a way to speed it up.
First step of this processing tool is to connect every point of one ...
4
votes
1answer
160 views
PostGIS speed up query on 9 million row polygon table
Table bl_data 9 million+ polygon rows with GIST index and the index is clustered
create index ii3 on bl_data using gist(geom);
CLUSTER ii3 ON bl_data;
I have a query that finds records with the same ...
7
votes
1answer
1k views
When to use GiST and when to use SP-GiST index?
I'm aware of the documentation for both of the indexes, but can't decide which one to use.
PostGIS documentation on these indexes
Example:
Let's say we have table data with the POINT attribute that ...
0
votes
1answer
44 views
Implement Rtree index to custom objects list
I'm trying to improve a spatial calculation, and I think the most efficient way is to add a spatial index, but I'm not sure how to do it.
In one hand I have a list of custom objects, with a geom ...
1
vote
0answers
86 views
Why does PSQL ignore spatial index in “where NOT ST_Contains”, but uses in “where ST_Contains”?
I'm creating a new table of trips in my PostgreSQL/PostGIS database, where trips are comprised of a series of individually identified points, based on where the points within each trip are. The idea ...
9
votes
0answers
371 views
Determining which QGIS layer has a spatial index?
I would like to loop over all layers within a QGIS project and determine which layer don't have a spatial index and cause QGIS to slow down.
I tried the method hasSpatialIndex() but this always ...
1
vote
0answers
56 views
PostGIS query to retrieve geometry that intersects both a bbox intersects and also matches other columns
I am relatively new to GIS, so have been searching around to try find a similar case to the one I am facing but have come up short.
Specifically I want to store multiple sets of geometry features in ...
-2
votes
1answer
881 views
SQL Server how to create spatial index? ( spatial query )
I know how to do spatial query,
Shape is my geometry type column.
bbox is well know text format of a bounding box
srid = 2229, you must specify this srid in where intersect clause
...
2
votes
1answer
118 views
Query Geopackage using spatial index in QGIS DB manager
I would like to do spatial join in QGIS DB manager on geopackage database.
The query should look like this
select * from Soil_C_cadaster a,
(select * from Soil_C_cadaster
where forest_c is null) b
...
0
votes
1answer
108 views
PyQGIS how to construct QgsSpatialIndex with FlagStoreFeatureGeometries flag?
In the PyQGIS documentation for QgsSpatialIndex.nearestNeighbor (https://qgis.org/pyqgis/master/core/QgsSpatialIndex.html#qgis.core.QgsSpatialIndex.nearestNeighbor)
we see the following (!) Warning:
...
1
vote
0answers
141 views
High cost of geometry bitmap heap scan
I have a PostGIS database with tables osm_admin (with contains all administrative boundaries from osm data) and osm_landusages (with contains all landuse areas form osm - like forests, industrial/...
1
vote
0answers
192 views
geometry point and varchar multicolumn index in PostgreSQL
Is it possible to create a multicolumn index on a geometry point column and a varchar column? For example, say I have a geometry point column called "location_point" and a varchar column called "zip". ...
4
votes
1answer
68 views
Spatial index not used in view which union several tables
I have multiple spatial tables each of which I have created gist index, and query for each table will cost 10-200ms as shown in the following example, the spatial index can be used.
Example 1(with ...
1
vote
0answers
40 views
How to get index of specific location in IDL
I am trying to get the index of specific lat and lon in IDL.
I know how to do it in python.
tmp = tmp.sel(lat=lat_loc,lon=lon_loc,method='nearest')
But I can't find a way to do so in IDL.
Any ...
2
votes
0answers
155 views
PostGIS runs out of memory due to the use of spatial index
I am running this query on PostgreSQL.
CREATE TABLE table3 AS (SELECT
t1.id
FROM
table1 AS t1
CROSS JOIN LATERAL
(SELECT
t2.geometry AS geom_closest
FROM ...
1
vote
0answers
245 views
Speed up PostGIS raster query
I have a simple PostGIS table as follows:
\d rad;
Table "public.rad"
Column | Type | Collation | Nullable | Default ...
0
votes
0answers
162 views
Spatial index not speeding up spatialite query containing Multipoint Geometry Data?
I am querying a sqlite format geodatabase in which there is a label for each geometry shape.
Think of data as :
label = name of a district in your state
geometry: polygon which specify the ...
1
vote
1answer
231 views
Multiprocess Spatial Join with Spatial Index
I'm a newbie in implementing GIS on Python, but the lack of any GIS software at the office has forced me to learn about it.
I have a huge grid (a multipolygon) that I'm currently filling out with ...
0
votes
1answer
848 views
Spatial index for a raster column in a table in PostGIS
I have a raster table in PostGIS. When I run ST_SetValue(rast, geom , value) on it to change the pixel value at a point, it works very slowly.
Do you know how can I create spatial index for a raster ...
0
votes
0answers
63 views
Transfer indexed Oracle geoloc table to PostgreSQL using ogr2ogr
I am copying tables using ogr2ogr from Oracle to PostGIS using
ogr2ogr -f "PostgreSQL" PG:"host=<host> port=5432 user=<user> dbname=<dbname> password=<pwd>" OCI:<USERNAME&...
0
votes
1answer
338 views
Export NDVI values from chart as JSON in Google Earth Engine
After filter OLI8 for a small area
var collectionFilter = ee.ImageCollection('LANDSAT/LC08/C01/T1_RT_TOA')
.filterBounds(geometry)
.filterMetadata('CLOUD_COVER','less_than',4)
.map(app);
print(...
1
vote
1answer
212 views
GIST index not used by query optimizer on geometry field
I'm trying to add elevation curves on an OpenStreetMap tile server I'm currently building.
I created a table in PostgreSQL to store the shapes of the whole world.
Structure was defined by gdal_contour ...
8
votes
0answers
316 views
Calculate volume of space defined by multiple overlapping 3D polygons
I have thousands of 3D polygons scattered in space but they mostly overlap with many of their neighbors in big extent.
Is there any way how to compute volume they define effectively?
I would love ...
1
vote
1answer
43 views
Finding Rows Inside other Rows
So I have database with which I want to do the following:
Find out which other rows are contained within the geometry of the row in question. To keep it simple, I am finding the envelope/bbox of the ...
3
votes
1answer
1k views
Spatial Index on 100s of Millions of Records Taking Days in PostGIS? [closed]
I have a table with ~200million spatial records in it, and it is taking days to build a spatial index on the one geometry column: should this be taking so long?
The geom column is of the type ...
