I have a number of point and line tables in postgres in which I want to update an attribute according to which area of a 5km grid they fall into. I use the following postgis query to do this:
UPDATE point_table AS B SET tile = A.tile_name FROM 5km_grid_polygons AS A WHERE ST_Within (B.geom, A.geom);
This worked find for most of my data. However, when a point or line falls exactly on a grid boundary (as in screenshot below), then no attribute is copied over.
This has happened hundreds of times, so manually fixing is not an option. I am wondering if anyone can think of a way to create a rule that assigns one grid square to the point or line that is 'in limbo'?
The rule/query would have to be consistent, for instance, always assigning the attribute from the lower or left grid square to the point or line. It cannot pick one of the adjacent grid squares at random.