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
62
votes
2answers
11k views

Acquiring ArcGIS-like speed in Postgis

I have been using Postgis 2.0 for 3/4 of a year now and while I really enjoy using it, excessive query processing time has rendered it basically unusable for my use case. I tend to do heavy ...
16
votes
3answers
5k views

How to speed up queries for raster databases?

I have a raster database in postgresql/postgis with these columns: (ID, rast, data_of_data). 'rast' is the column that has raster files in WKT format. An example query to find the DN value of a ...
16
votes
3answers
3k views

Optimize nearest neighbor query on 70 million point cloud on SQL Server 2008

I have about 75 million records in a SQL Server 2008 R2 Express database. Each is a lat long corresponding to some value. The table has geography column. I am trying to find one nearest neighbor for a ...
14
votes
1answer
1k views

sql server spatial index performance

I have a table with around 2 million records in it. I create a spatial index, using the defaults other than the bounding box. I've been noticing that some queries are extremely fast, and some are ...
13
votes
3answers
8k views

Understanding use of spatial indexes with RTree?

I'm having trouble understanding the use of spatial indexes with RTree. Example: I have 300 buffered points, and I need to know each buffer's intersection area with a polygon shapefile. The polygon ...
13
votes
3answers
7k views

Poor Performance when using Spatial Indexes in MySQL

Re-post of a question asked on Stack Overflow when it was suggested this would be a better forum. I'm trying a little experiment at pushing a data set which is not geo-spatial but fits it quite well ...
13
votes
1answer
350 views

Accessing the internal data structure storing Postgis spatial index (PostGres GiST)

I am curious about how the internal data structures and storage mechanism of the spatial GiST index in Postgis. In Oracle, I used to like to show how the SDO indexes were "just another table", and how ...
12
votes
5answers
4k views

Speeding up OpenStreetMap PostGIS querying

I have OpenStreetMap data for the Netherlands loaded into a PostGIS database (PostgreSQL 8.3 / PostGIS 1.3.3) using the osmosis schema. This means all tags are stored in a hstore field. In addition to ...
10
votes
2answers
3k views

ST_Distance doesn't use index for spatial query

I can't get PostGIS 2.1 running on PostgreSQL 9.3.5 to use a spatial index even for the simplest queries. The whole dataset is 8 million points (population count grid from here). The table is created ...
9
votes
1answer
450 views

Saving a spatial index in QGIS API?

I am trying to create a spatial index for some shapefiles using the Qgis API. I have followed the steps described in Nathan Woodrow's blog (https://nathanw.net/2013/01/04/using-a-qgis-spatial-index-to-...
9
votes
3answers
3k views

Create Spatial Index in PostGIS on an entire Schema

I have loaded a number of shapefiles using SPIT (QGIS plugin) to my PostGIS database. These layers did not have a Spatial Index created upon loading. I am wondering if there is a way to create a ...
8
votes
4answers
7k views

How to efficiently access the features returned by QgsSpatialIndex?

The PyQGIS Cookbook explains how to set up the spatial index but it only explains half of its usage: create spatial index — the following code creates an empty index index = QgsSpatialIndex() ...
8
votes
2answers
781 views

Basics about Spatial Indexes

I was asking on a forum if it could be a good idea to create spatial indexes on subsets of a table when your application display on map only those subsets and never the entire table. I was asking ...
8
votes
2answers
1k views

PostGIS Intermittent INDEX Performance

I have a table containing approximately 55 million data points ( point is a geometry with SRID 4326 ) and for my query I need to join this to an area table ( currently have ~1800 areas ) which ...
8
votes
1answer
11k views

Fastest strategy for proximity searches in SQL Server 2012

This is my very first question here, so bear with me! I'm implementing a back end for a mobile app which will have to do proximity searches to find nearby POIs (points os interest). I know it's a ...
8
votes
2answers
2k views

Are there trade offs between using a geohash vs using a quadkey as a spatial index?

QuadKey Bing Maps uses a quadkey structure for their tiling scheme. Here is an overview of the concept http://msdn.microsoft.com/en-us/library/bb259689.aspx. GeoHash A geohash seems to be the more ...
7
votes
2answers
743 views

Does PostGIS have separate implementation of GiST index from PostgreSQL?

I want to know if PostGIS has a separate implementation of GIST index or it reuses the GIST index implementation provided by Postgresql?
7
votes
2answers
1k views

How does a quad-tree cope with very large objects?

If I use a quad-tree to index my map data, how does it cope with a road that goes from one end of the map to the other? What other sort of indexes should I consider to get round this problem?
7
votes
1answer
1k views

Spatial index vs two coordinates indexes

I have a table with two columns: latitude and longitude. I want to get all objects inside a "rectangular" (well, rectangular in a lat/lon coordinate metric) bounding box: min-max latitude and min-max ...
7
votes
0answers
193 views

QgsSpatialIndex partial or complete overlap

I'm working on adding some functonality to a QGIS plugin. It currently uses a QgsSpatialIndex to efficiently compute whether the features of a vector layer intersect (potentially millions) of ...
6
votes
5answers
5k views

PostGIS doesn't use spatial index with ST_Intersects

I imported the data from openstreetmap using osm2pgsql and copied it another table structure. If I made a query for containing rows using st_intersects, st_contain or someone else, postgis don't use ...
6
votes
2answers
825 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. ...
6
votes
1answer
2k views

Is it possible that a good spatial index just cannot be made?

I have about 6 million rows with geography spatial features - they are all 1000 meter radius hexagons all over the US. There are a couple hundred thousand that would have been NULL, which I updated ...
6
votes
3answers
818 views

Trying to combine nearby points into a polygon

We have collected millions of cell phone strength readings. I am trying to group points of the same strenght that are within a distance of each other in to a polygon. I asked on the postgis mailing ...
6
votes
2answers
230 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 ...
6
votes
2answers
389 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 ...
6
votes
0answers
64 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 ...
6
votes
1answer
275 views

3D time dimension (x,y,t) [closed]

I'm working with data points (x,y) and timestamp t as a separate attribute in my table. (x, y) are in WGS 84 latitude,longitude format while timestamp is in Unix Time. For instance (71.8, -13.5) and ...
5
votes
2answers
9k views

How can I use R-Tree to find points within a distance in Spatialite?

Create a table with 3D points: create table points (coords pointz); select CreateSpatialIndex('points', 'coords'); Insert 13k rows: insert into points values (geometryfromtext('pointz(...)')) Do ...
5
votes
2answers
2k views

How to delete a broken index in SpatiaLite?

I managed to screw up an index in my SpatiaLite database. The index was created using spatialite-gui. In the meantime, I deleted the original table and three out of four index files: ...
5
votes
2answers
247 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 ...
5
votes
1answer
557 views

What are the PostGIS Raster Constraints used for?

My question is quite basic, but I have not found a simple explanation of why raster constraints are useful. I have worked with rasters in PostGIS for a while but only recently I realized that I do ...
5
votes
2answers
872 views

3D KNN index in PostGIS

I am looking for a way to do fast KNN queries on a dataset of 10+ million 3D points (and growing). I was hoping PostGIS would do the trick with its new <-> and <#> operators, but they don't seem ...
5
votes
1answer
167 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(): ...
5
votes
2answers
854 views

Error when creating spatial index in MongoDB

I am wanting to use MongoDB for some basic spatial calculations (on some fairly large data), but I get an error when trying to create an index on the US Counties shapefile retrieved from the US Census'...
5
votes
2answers
3k views

How to speed up an st_intersects() query? [duplicate]

I have a "huge" spatial database: 72GB, 200 millions documents with geometries (points) I have a GIST index on the geometries the database: CREATE TABLE resources ( id bigint NOT NULL, accuracy ...
5
votes
2answers
608 views

Spatial Indexes on OS MasterMap Data

What are the optimal settings for setting up a SQL Server 2008 Spatial database with full UK OS MasterMap data in? Seperate tables for the different feature types, or a single table with everything ...
5
votes
0answers
315 views

Efficiency differences between quadtree and geohashes [closed]

I am a newbie to spatial indexing. Can someone please explain me the differences between a quad-tree and geohash. From what I understand both use similar hierarchial methods of dividing the space into ...
4
votes
4answers
4k views

Spatial index to speed up the query

I have spatial table in PostgreSQL 9.2 / postgis 2.0 that I have to extract some information from it. But my query is hopelessly slow. How could I make use of spatial index to improve the performance ...
4
votes
1answer
503 views

Does QGIS automatically use spatialite spatial indexes?

When you write a query in spatialite you have to manually join your query to the spatial index for that index to be used to speed up the query. When QGIS displays data does it take advantage of any ...
4
votes
3answers
4k views

How to properly utilize DWITHIN Spatial Filter in OpenLayers

Given the following code: var points = []; points.push(new OpenLayers.Geometry.Point(-110, 38)); points.push(new OpenLayers.Geometry.Point(-108, 40)); var lineString = ...
4
votes
1answer
2k views

SQL Server 2008 Spatial Index Nearest Neighbour

I am trying to query geopoints for nearest neighbour, Whether I use spatial index or not, I always get similar times and it takes about 6 seconds. How can I speed this query up? Here is my query: ...
4
votes
1answer
179 views

QgsSpatialIndex.nearestNeighbor() always returns 0 id

I'm creating a set of features and adding them to a QgsSpatialIndex to find the ones closest to a certain location. Why does nearestNeighbor() always return [0L] instead of the correct feature id? ...
4
votes
1answer
644 views

Shorter Version of this SpatiaLite Query

I've been doing some spatial queries using spatialite, and they're large enough that a spatial index becomes a must. An example of using an index is given in the tutorial spatialite> SELECT ...
4
votes
1answer
435 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 ...
4
votes
1answer
2k views

For which operations a PostGIS Geography index is useful?

I need to do KNN- and within-region queries for POI data. I store the POI locations in a geography column. Since I understand that ST_DISTANCE doesn't use the geography index, I added a geometry index ...
4
votes
0answers
238 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 ...
4
votes
0answers
93 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 ...
4
votes
0answers
754 views

Creating Shapefile spatial index programmatically in .NET?

I am creating a shapefile programmatically using DotSpatial using the following example syntax / pseudo code FeatureSet features = new FeatureSet(DotSpatial.Topology.FeatureType.Polygon); features....
3
votes
2answers
875 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?