Tag Info

New answers tagged

1

PostgreSQL doesn't use indexes for functions, it uses indexes for operators only. What happens is function inlining. ST_INTERSECTS is defined as: CREATE OR REPLACE FUNCTION ST_Intersects(geom1 geometry, geom2 geometry) RETURNS boolean AS 'SELECT $1 && $2 AND _ST_Intersects($1,$2)' LANGUAGE 'sql' IMMUTABLE; And so the query gets rewritten to use ...


2

Note that planners have difficulty with subqueries, and your example can be rewritten without subqueries. A flattened query should look like this: SELECT A.* FROM osm_addr2 AS addr, osm_addr2 AS POI WHERE POI.osm_id=-332537 AND ST_Intersects(addr.geometry, POI.geometry); There's a relevant example in the manual (last two SQL examples), where a subquery is ...


0

Judging by the cost and quantity of rows estimated to be returned, the spatial index wasn't necessary. At least the optimizer didn't think it was and chose a seq scan instead. Is it running slow? How may rows do you expect back? How many rows are in the table altogether? Sorry if I overlooked that in your question but sometimes a full scan is faster. ...


1

I have found that rearranging the query so that the sub-query is at the same level as the initial select, essentially a Cartesian product, but then using the where clause to restrict the records read, will cause the indexes to be used and avoid a full table scan. SELECT * FROM osm_addr2 AS addr, (SELECT geometry FROM osm_addr2 WHERE osm_id=-332537) ...


1

# assume a list of feature ids returned from index and a QgsVectorLayer 'lyr' fids = [1, 2, 4] request = QgsFeatureRequest() request.setFilterFids(fids) features = lyr.getFeatures(request) # can now iterate and do fun stuff: for feature in features: print feature.id(), feature 1 <qgis._core.QgsFeature object at ...


1

For comparisons, look at More Efficient Spatial join in Python without QGIS, ArcGIS, PostGIS, etc. The solution presented use the Python modules Fiona, Shapely and rtree (Spatial Index). With PyQGIS and the same example two layers, point and polygon: 1) Without a spatial index: polygons = [feature for feature in polygon.getFeatures()] points = ...


1

In a blog post on the subject, Nathan Woodrow provides the following code: layer = qgis.utils.iface.activeLayer() # Select all features along with their attributes allAttrs = layer.pendingAllAttributesList() layer.select(allAttrs) # Get all the features to start allfeatures = {feature.id(): feature for (feature) in layer} def noindex(): for ...



Top 50 recent answers are included