my answer is based on: http://www.postgresqltutorial.com/creating-first-trigger-postgresql/ and https://stackoverflow.com/questions/39607334/postgres-trigger-creation-error-no-language-specified-sql-state-42p13
EDIT1: adjusting for ThingumaBob's comment
first you need to create a function to be used by your trigger action :
CREATE OR REPLACE FUNCTION ...
You can use ST_PixelAsPoints to get a geometry for each point and then to a "Cartesian join", ie, a full join between the points and the pixels, eg,
WITH pixels (x, y, geom) AS (
SELECT x, y, geom
(SELECT (ST_PixelAsPoints(rast, 1)).*
WHERE rid = 1
gid, x, y, ST_Distance(points....
The link you included in your question should do exactly what you're looking for (once max is swapped for min). The first part will make a raster layer with the min value within each polygon. The second part will compare this min layer with the original DEM to find all the pixels where they are the same (it should be one per polygon), and then set everything ...
You have two options I can think of off hand.
You could use the select by location tool, and select all polygons that intersect the points. Then reverse the selection in the attribute table, thereby switching the selection from those that have points within them, to those that do not interact with the points at all.
You could run the spatial join ...
A super simple solution: buffer your points by your desired distance, with a dissolve. Then, inward buffer by that same distance (use a negative distance value). Since the initial buffers were dissolved, the area between points will not be removed by inward buffering and will create those polygons for you.
Its a bit obscure, but the elide function in the maptools package can be used to move sp class objects.
This works for all point, line, and polygon classes. Following the data setup in help(elide) here's a river polygon shifted 0.10 units up and across:
> plot(elide(rivers1, shift=c(.10,.10)),add=TRUE)
For sf objects ...
I wish Ian had posted his response as an answer so I could give him the rep, but I guess I will write it here for now. Looks like polygons must be valid before you can run this tool so his suggestion was the route to solving this issue. I didn't think coincident vertices would be an issue that would stop this tool but there were two errors like this that ...
I would definitely go with option A. Having two tables for the same object (in different states) just doesn't feel right to me.
There are chances that you won't be needing a 'flag' column at all. Check your old vs new 'schema' and try to identify columns which are NOT NULL as indicators of whether a point lies in the old or new.
For instance, if in the old ...
I have a similar problem: I need to snap a point on a linestring. Even though I don't split the linestrings, I hope my solution would be useful for you.
My approach is this: Due to the rounding errors, the nearest point needs not to be precisely on a linestring. My solution is to add a new point to the linestring that is precisely the nearest point. (...