Questions tagged [spatial-index]
In a database, a mechanism for optimizing access to data based on the spatial column(s) of a data table.
1
vote
1answer
45 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 ...
6
votes
0answers
51 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
25 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
69 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
43 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
41 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.
1
vote
0answers
120 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
36 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
172 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
66 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 ...
1
vote
1answer
99 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 ...
0
votes
0answers
178 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 ...
0
votes
0answers
60 views
NetworkX - Indexed Spatial node queries?
I'm trying to extract a Road Network from a large set of GPS point data gathered from vehicles.
I plan on representing the final network in terms of a NetworkX graph of G(V,E)
I have implemented ...
1
vote
0answers
71 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 ...
1
vote
1answer
116 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 ...
4
votes
0answers
177 views
When does a 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 ...
0
votes
0answers
114 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
173 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
109 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
159 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
242 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
246 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
180 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
153 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
197 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
99 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 ?
...
4
votes
0answers
91 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 ...
3
votes
0answers
85 views
Most performant KNN method in postgis (<->, <<->>, st_distance)
My question is about the fastest way to order query results by approximate distance. I've seen conflicting information about this.
My data are lat/lng points stored using WGS84 (SRID 4326).
The ...
1
vote
1answer
45 views
Where do spatial indexes look for SRIDs?
I'm looking into optimizing the spatial indexes on some of our tables (stored in SQL Server 2008 R2), so I ran
exec sp_help_spatial_geometry_index
@tabname='myTable',
@indexname='...
1
vote
1answer
98 views
Spatial join by time in postgis
I have two tables in psql that I am trying to join based on certain attributes - they are very large - 17 M rows and 2.7 M rows respectively
mydb=> SELECT reltuples::bigint AS estimate FROM ...
5
votes
1answer
136 views
QgsSpatialIndex.nearestNeigbor() unexpected result
I have a Linestring layer for which I have build a QgsSpatialIndex with the following code:
my_linestrings_lyr_idx = QgsSpatialindex()
for f in my_linetraings_lyr.getFeatures():
...
6
votes
2answers
762 views
In PostGIS why does spatial index still matter for a table with only points?
According to this Boundless link, it turns out that in PostGIS, spatial query on table with spatial index uses two pass system: with the first pass approximates spatial relationship with bounding box.
...
4
votes
2answers
762 views
Creating Spatial Index for Geopackage using QGIS?
Working in QGIS 2.18.14 standalone on Windows, is there a way to create a Spatial Index for Geopackage (gpkg) layers?
0
votes
0answers
26 views
How to Drop Indexes on a Table in ArcCatalog
I am using an Oracle 11g database . The code below doesnt allow me to drop indexes on a table.
import arcpy
# Remove indexes and statistics for gis feature class delta tables
COND=r'E:/...
1
vote
1answer
356 views
Spatial index on PostGIS with ST_MakeEnvelope testing against point geometries (before vacuum full analyse)
I just updated this question as I was able to fix the problem by running a vacuum full analyse on the tables. I tested vacuum alone and vacuum analyse before which did not solve the problem. Not sure ...
2
votes
1answer
458 views
How do I decide what sort of spatial index to build for a SQL Server table with a Geometry column?
Let’s say an organization manages a lot of GIS data stored in Sql Server and leverages Esri software. To ensure that users can access the data with reasonable speed, I want to make sure the spatial ...
0
votes
1answer
528 views
Errors in creating spatial index on SDO_GEOMETRY column in Oracle 11g
I am using Oracle 11g (Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production) and I have a table that has column of type SDO_GEOMETRY. I want to create a spatial index on this ...
3
votes
2answers
681 views
Adding spatial index to spatialite file in python using OGR?
I have a spatialite data set without a spatial index on the geometry column and I would like to create it.
In OGR command line I can use the following to create it (OGR Spatialite driver):
ogrinfo ...
2
votes
1answer
166 views
PostGIS: Spatial Index not being used
Below is the query I'm using :
SELECT osm_id FROM planet_osm_line where ST_DWITHIN((ST_Transform(way,4326)), st_setsrid(ST_Point(9.17193, 48.76768),4326) ,50000)
and time_cost =0.1 limit 100000;
I ...
1
vote
0answers
168 views
When I should use Rtree/KdTree vs Morton z-index and s-curves?
I was always confused by those two families (?) of indexing.
I feel that the first one goes with the "normal" data, while z-index and s-curves work the best for tiling/grid/uniformly distributed ...
6
votes
1answer
363 views
Building spatialindex in Postgres with JNDI for GeoNetwork?
Which steps should be taken to build the spatialindex table inside PostgreSQL when using a JNDI connection?
Is the table built by GeoNetwork or does it need to be created manually?
What I have ...
2
votes
0answers
89 views
Adding Spatial Index for Geometric Network?
I am trying to create Spatial Indexes on all the feature classes in one of my databases. It has worked fine on most feature classes so far, but when I started on feature classes in a feature dataset ...
2
votes
1answer
530 views
Compare only valid polygon geometries in PostGIS
I'm trying to run a fuzzy geometry comparison on a large polygon dataset (>2 million rows), but am getting an error like so:
ERROR: ptarray_contains_point called on unclosed ring
SQL state: XX000
...
3
votes
1answer
233 views
Spatial Index error?
I am trying to sum the number of fires in each state of the US. I have a shapefile for the states and I have a shapefile for each fire. Vector->Analysis Tools-> Count points in a polygon isn't working ...
5
votes
2answers
228 views
Querying indexed vector layer (finding nearby point)?
I can find nearby features in a layer by using QgsSpatialIndex: Finding the nearest line to a point in QGIS? Using this method, we need to feed in all existing features and query it with a point to ...
2
votes
2answers
230 views
What is GiST page size using PostGIS?
How can I check the page size (and maybe modify) of the GiST index in PostGIS? From the PostGIS documentation, I quote:
"... GiST indexes support the concept of "lossiness" which is
important ...
4
votes
1answer
1k views
Find csv lat and long points in a shapefile polygon with geopandas spatial index
I have a csv with two sets of latitude and longitude points, and a shapefile with espg 4326. I want to find which points are inside the shapefile. I've been following the guide here which uses a ...
1
vote
2answers
560 views
SDO_GEOMETRY view in ArcGIS Desktop
I have an sdo_geometry table that I created using ArcGIS Desktop (by converting an esri.st_geometry table to sdo_geometry). I'm using Oracle Spatial's sdo_geometry datatype because it has linear ...
3
votes
1answer
363 views
How to find coordinates from large CSV within multiple shapefile polygons
I have a CSV file of around 230 million rows, which contains a latitude column, a longitude column, a Date stamp, and an ID column. It look like this (I added the header in this example to help ...
4
votes
1answer
404 views
Implement quad-tree as B-tree?
I've heard that quadtrees (as used in spatial-indexing) can be implemented on top of the B-tree structure for non-spatial indexes.
For example, comments in a blog says:
with R-trees you are ...
