Episode #125 of the Stack Overflow podcast is here. We talk Tilde Club and mechanical keyboards. Listen now

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
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 ...
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 ...
3
votes
0answers
103 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
113 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 ...
1
vote
1answer
647 views

How to disable spatial index creation by ArcSDE for Oracle Spatial

Working with ArcSDE 10.1 and Oracle spatial 11g. I set the feature class in load_only_io before loading shapefiles. I am using geoprocessing tool (append) in ArcGIS to load shapefile to geodatabase. ...
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 ...
6
votes
2answers
874 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. ...
2
votes
0answers
318 views

Destroying spatial index using PyQGIS?

QGIS 1.9 Is it possible to destroy a spatial index? I am noticing that if I generate a spatial index on a set of polylines (within a plugin), split one of the lines, and then recreate the spatial ...
0
votes
0answers
34 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
491 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
613 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 ...
2
votes
2answers
355 views

Postgis Functional Index on st_pointonsurface() Ignored

If I define the following table... CREATE TABLE tbl_vast_importance( id bigserial PRIMARY KEY, geom geometry(MultiPolygon,4326) ); CREATE INDEX ON tbl_vast_importance USING GIST (geom); CREATE ...
3
votes
1answer
2k views

Creating functional spatial index of ST_Geometry Feature Class in ArcSDE for Oracle?

Copied an SDE feature class (with blob(sdebinary(?) geom) into the same SDE GDB (Oracle 11g/SDE 9.3.1) but with the ST_geometry configuration keyword. I can see that the feature class was created, ...
2
votes
1answer
212 views

Does a feature need to be in the Spatial Index (QGIS) in order for NearestNeighbor to find its neighbours?

To be more specific: I have a feature class for which some features have an entry for a variable ('KnownBj') and some not (have a 9999 entry). I want for each feature to find the 5 nearest neighbors ...
3
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 ...
13
votes
3answers
9k 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 ...
9
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() ...
3
votes
2answers
789 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
195 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
201 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 ...
1
vote
0answers
624 views

Using spatial index in spatialite query?

I posted this question originally Using nearest neighbor query in SpatiaLite? Using the answer given I was able to get the query working on some test data. However I am having trouble applying a ...
2
votes
0answers
324 views

GeoDjango and spatial indexes

I have three questions about spatial indexes management in GeoDjango, and I can't find the answers in the documentation. First, I know GeoDjango creates a spatial_index for a geometry field by ...
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 ...
2
votes
1answer
602 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 ...
2
votes
1answer
267 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
277 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 ...
9
votes
1answer
476 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-...
2
votes
2answers
253 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 ...
1
vote
2answers
673 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
436 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
460 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 ...
3
votes
2answers
912 views

How to select polygons which contain at least one point with spatial index

I'm very new to using QGIS, what I have is a points shapefile and a polygon shapefile. I would like to select all the polygons which have at least one point in them. The problem I'm running into is ...
0
votes
1answer
483 views

Error while creating spatial index on geogarphy column in sql server

I used the following code to create geography column using latitude, longitude alter table [locate].[dbo].[BFLGeocodedResult] add geographyColumn as geography::STGeomFromText ('POINT('+convert(...
5
votes
0answers
333 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
1answer
181 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? ...
3
votes
1answer
150 views

Error when saving into .gdb file

I am supposed to copy a shapefile into an existing database (*.gdb) using QGIS. There is no problem opening the database, neither when pasting the elements into it. However, once I try to save the ...
3
votes
1answer
82 views

nearest N objects (points) [closed]

I'm searching for the nearest ten neighboring points from a given point. When I use this query: SELECT id, geom, ST_Distance(geography(geom), ST_GeographyFromText('SRID=4326;POINT(-0.22707 51....
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 ...
62
votes
2answers
12k 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 ...
1
vote
1answer
1k views

Using spatial index to intersect points with polygon, when points and polygon have same minimum bounding box?

I have a MultiPolygon representing the city boundary of Houston and it has an extremely complicated boundary. I also have a set of ~900,000 points (that has the same minimum bounding box as that ...
13
votes
1answer
352 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 ...
2
votes
1answer
792 views

Will rebuilding a spatial index recalculate the feature extent?

ArcGIS 10.3.1 ArcSDE 10.2.2 SQL Server 2012 I'm reading documentation on how to Rebuild a spatial index. Will rebuilding the spatial index automatically recalculate the feature extent of the ...
5
votes
2answers
891 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'...
3
votes
1answer
246 views

GIST slows down query?

I have an odd case where creating a GIST index slows down my query instead of speeding it up. The setup consists of two tables, one with multi-polygons, another with points. I want to calculate which ...
0
votes
2answers
399 views

Building efficient multi geometry type database with PostGIS?

I'm trying to build a PostGIS database which must be able to store (multi)points, (multi)linestrings and (multi)polygons. The goal is to efficiently querying the data with ST_Intersects and other ...
2
votes
0answers
204 views

display issue in qgis 2.14 : features appear and disappear

I have several virtual layers in my project. One of them contains 6 lines. Depending of the scale, at least one line disappears from the canvas for an unknown reason. I heard that sometimes, creating ...
1
vote
1answer
573 views

Using Postgis spatial index in st_distance operation based upon explicit SRID column

I have two large tables with data that spans over multiple regions of the U.S and needs a defined SRID column in atleast one the tables to denote which coordinate system should be used for area/...
2
votes
1answer
180 views

Running Gevel on PostGIS 2.0

I have found Gevel, a tool enabling the visualization of the content of the GiST indices in Postgres. In particular, I am interested to use it to visualize the RTree spatial index in PostGIS. Does it ...
2
votes
1answer
389 views

Bulk loading into pyrtree index

I'm using pyrtree to create an index for a point in polygon operation. I am currently trying to insert 1.8 million rows into the index and it takes more than an hour to do so. I am unable to use rtree ...