Questions tagged [spatial-index]
In a database, a mechanism for optimizing access to data based on the spatial column(s) of a data table.
168
questions
1
vote
1answer
65 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 ...
-1
votes
0answers
19 views
Habitat fragmentation Memory Error
I'm trying to calculate some habitat fragmentation parameters using the Lecos.
I calculated a buffer of 2,000 m of radious around each coordinate. Then, I intersected each buffer with the corine land ...
4
votes
1answer
96 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 ...
5
votes
1answer
108 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
16 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
38 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 ...
5
votes
0answers
147 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
48 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 ...
0
votes
0answers
42 views
How to average the NDDI index for a map in GEE
In previous codes I was able to generate the NDVI average, but since NDDI is a sub-index I would like to know if anyone would be able to generate this NDDI average in GEE
https://code.earthengine....
-2
votes
1answer
186 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
50 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
0answers
45 views
Raster Calculator conditions in QGIS [duplicate]
I'm trying to create an index using the Sentinel Imagens at Raster Calculater (QGIS), but I don't know how to put the following conditions:
if ((B2>B3) and (B3<B4) and (B4>B11) and (B4>...
0
votes
1answer
47 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
73 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/...
0
votes
0answers
126 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
54 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
35 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
83 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
157 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
95 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
144 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
0answers
445 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
56 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
231 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
145 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 ...
7
votes
0answers
213 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
41 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
683 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 ...
1
vote
0answers
46 views
PostGIS: Is it interesting to create an index on geom column and an alpha numeric field?
I would like to know if it's interesting to create an index on an other column than the geom one.
In some cases, I would like to do spatial analysis. If not, I would like to analyse only attributes.
...
0
votes
2answers
113 views
Creating index file in FME 2018?
Is there a transformer to create a index file for my shapefile output?
As a .qix file on QGIS.
2
votes
0answers
491 views
SP-GiST for geography type in PostGIS
This
CREATE INDEX geography_gdx ON schema.table USING GIST (geog);
works, but
CREATE INDEX geography_gdx ON schema.table USING SPGIST (geog);
gives me
[42704] ERROR: data type geography has no ...
1
vote
1answer
51 views
QgsfeatureRequest - Store result indexes
I recently posted a question. My original goal was to find a way to determine on which side of a line points are located. @xulnik answered this question with two codes. One returns a binary [0;1] ...
2
votes
0answers
467 views
Optimizing MySQL query to select all points with in a large polygon using spatial indexes
Re-posting it from stackoverflow as per their suggestion. Link to the thread in stackoverflow https://stackoverflow.com/questions/53182385/optimizing-mysql-query-to-select-all-points-with-in-polygon-...
0
votes
1answer
173 views
Chronological sort & indexing problems with QGIS Time Manager
I am trying to use QGIS Time Manager to create an animated map of property connected to polygon-geometry. The one article on animating polygons I've found on this site hasn't helped because my data is ...
2
votes
1answer
500 views
QGIS3 Creating spatial index in a standalone script as a part of output vector layer
I am making a buffer analisys in a standalone script. Is it possible to automatically during the analisys add index to the output vector layer (".qix" file)?
I tried to add after the analisys like in ...
1
vote
0answers
504 views
QGIS does not seem to make use of spatial index when using GeoPackages
I have a QGIS project with several Geopackage-Layers.
The project is quite slow in comparison to a project with shapefile or database layers.
I started QGIS together with DebugView to find out what ...
1
vote
0answers
154 views
Which approach is faster for getting all POIs from MySQL/MariaDB with PHP/Laravel [closed]
Correct me if I'm wrong.
There are three approaches to get the nearest homes, users have created in my website:
To create a table with two columns(latitude, longitude) that both of them are float ...
2
votes
1answer
193 views
Distance between point and line layers & spatial index issue
I am trying to compute on QGIS the distance from some clusters (ie points) to their closest road (ie lines); no need for all combinations, just the distance from each cluster to the closest road. To ...
5
votes
0answers
623 views
When does a QGIS .qix spatial index of a shapefile need to be re-created?
I understand the basics of spatial indexes but would like to understand when exactly a spatial index would need to be re-created or updated?
The reason I ask is because when a folder was moved to a ...
1
vote
0answers
337 views
Storing a Spatial Index in Sqlite
I would like to store an r-tree or quadtree index in a sqlite database using python.
Does anyone know if there is any code to do this?
A bit of background:
The dataset cannot be moved to another ...
3
votes
2answers
319 views
Query slowed down by gist-index on points
So I have this simple query, selecting all points in my table that are within a given polygon:
WITH my_polygon as (
SELECT ST_GeomFromEWKT('<EWKT polygon>') as geom
)
SELECT count(*)
FROM ...
3
votes
1answer
165 views
Getting the largest rectangle in the R-tree index of a PostGIS table?
Is it possible to use SQL or PL/pgsql to get the largest rectangle in the R-tree index (or other types of indices) of a table, so that it can be used as Bounding box for PostGIS table?
If possible, ...
3
votes
0answers
282 views
Geoserver ImageMosaic ignores existing datastore.properties (connect to PostGIS) and automatically generates index using shapefile
I am new to Geoserver. I am trying to publish a WMS service for a raster time series using the ImageMosaic plugin in Geoserver. I am using the sample dataset and instructions at this link
I want to ...
0
votes
1answer
456 views
indexes to optimize ST_Contains and ST_Overlaps for geoms in two tables
I'm a programmer getting into the wonderful world of GIS. I just had a fun day optimizing queries (took one from 60s to 3.4s, and another from 38s to 1.2s). Below is the last query that I can't seem ...
2
votes
0answers
469 views
Improve the speed for ST_DWithin
I need to get the outer polygon of the LSOAs within 10 miles of each postcode centroid. There are 180k LSOA polygons in table1 and 2.6 million postcode centroid points in table2.
I have created 2 ...
6
votes
2answers
447 views
Geometry distance PostGIS query with <-> operator suddenly much slower
One of my websites suddenly got much slower, and I've managed to track down the issue to a query that uses the <-> operator together with ORDER BY (2D distance between two geometries) to ...
2
votes
1answer
233 views
postgis: scaling / optimization of shortest distance between two geometries (seeking recommendations)
I have two tables in postgis:
one with 1B rows containing points (POINT, 4326)::geometry
the other, with 250k rows containing linestrings also in 4326
Both tables have a GIST index.
I am trying to ...
-1
votes
1answer
440 views
Does Spatial Index make feature class faster? [closed]
Does spatial index make dataset work faster?
Are there any disadvantages in having it?
All datasets in my sde don't have a spatial index. I thought I should add it to make things faster
0
votes
1answer
169 views
how to index measurements with spatial coordinates
I am beginner with the RTree I use libspatialindex, I will want to know how I insert a polygon / point with a measurement (statistic or measurement of weather)
do I have to change the structure ?
...
5
votes
0answers
105 views
Speed up identify in QGIS
I have just transferred my Postgis database onto an RDS server, and there is a bit of a performance hit when using the Identify tool in QGIS (3) compared to localhost. I presume this is mostly network ...
