I am trying to run a custom function on each parcel in a PostGIS table, (in this case its a function that finds the standard deviation value of the pixels intersecting a raster) Here is the code for the function:
SELECT stddev((gval).val) As std_dev FROM ( SELECT ST_Intersection(rast,1,buf.geom) As gval FROM public.my_raster INNER JOIN ( SELECT * FROM public.my_parcels As geom WHERE geom.gid = aPolygonGID ) As buf ON ST_Intersects(rast,buf.geom)) As foo; I wrote code that does this in the QGIS Python console, making a query for each parcel:
caps = vlayer.dataProvider().capabilities() iter = vlayer.getFeatures()#Get all the layer features for feature in iter:#For each feature query = db.exec_("""SELECT stddev((gval).val) As avg_elesqm FROM ( SELECT ST_Intersection(rast,1,buf.geom) As gval FROM public.my_raster INNER JOIN ( SELECT * FROM public.my_shapefile As geom WHERE geom.gid = """+str(feature.attributes()[feature.fieldNameIndex("gid")])+""" ) As buf ON ST_Intersects(rast,buf.geom)) As foo;""") query.next() feature.setAttribute("test",str(query.value(0))) vlayer.updateFeature(feature) However the code is very very slow, so I am hoping that running the whole thing as a single PostGIS query will be quicker, but I cant find an analog of
iter = vlayer.getFeatures() That will get all the features and then allow me to go through running the query for each of them. Does anyone have any ideas? and will it be faster than my current method?
أكثر...
SELECT stddev((gval).val) As std_dev FROM ( SELECT ST_Intersection(rast,1,buf.geom) As gval FROM public.my_raster INNER JOIN ( SELECT * FROM public.my_parcels As geom WHERE geom.gid = aPolygonGID ) As buf ON ST_Intersects(rast,buf.geom)) As foo; I wrote code that does this in the QGIS Python console, making a query for each parcel:
caps = vlayer.dataProvider().capabilities() iter = vlayer.getFeatures()#Get all the layer features for feature in iter:#For each feature query = db.exec_("""SELECT stddev((gval).val) As avg_elesqm FROM ( SELECT ST_Intersection(rast,1,buf.geom) As gval FROM public.my_raster INNER JOIN ( SELECT * FROM public.my_shapefile As geom WHERE geom.gid = """+str(feature.attributes()[feature.fieldNameIndex("gid")])+""" ) As buf ON ST_Intersects(rast,buf.geom)) As foo;""") query.next() feature.setAttribute("test",str(query.value(0))) vlayer.updateFeature(feature) However the code is very very slow, so I am hoping that running the whole thing as a single PostGIS query will be quicker, but I cant find an analog of
iter = vlayer.getFeatures() That will get all the features and then allow me to go through running the query for each of them. Does anyone have any ideas? and will it be faster than my current method?
أكثر...