Hoping someone can help me out with this issue.
I've got a table of polygons which are grid squares which get overlaid on a map. These are used when querying data so that points get grouped into each grid square. All this works fine till a point is on the boundary between the grid squares. I've included an example of this below. If I use ST_Contains I get no results as the point isn't contained within any of the polygons. If I use ST_Intersects the point intersects with the boundaries of multiple grid squares which isn't suitable.
Is there a way I can ensure that the point is only considered to be in one polygon? In are previous system before migrating to using PostGIS we used the PNPOLY algorithm that ensured this.
DROP TABLE IF EXISTS areasTemp; CREATE TEMP TABLE areasTemp ( id integer, areacode character varying(10), polygon geometry ); INSERT INTO areasTemp VALUES (634,'1x50',ST_GeomFromText('POLYGON((1 50,2 50,2 51,1 51,1 50))', 4326)), (663,'0x51',ST_GeomFromText('POLYGON((0 51,1 51,1 52,0 52,0 51))', 4326)), (633,'1x51',ST_GeomFromText('POLYGON((1 51,2 51,2 52,1 52,1 51))', 4326)), (664,'0x50',ST_GeomFromText('POLYGON((0 50,1 50,1 51,0 51,0 50))', 4326));
SELECT * FROM areasTemp WHERE ST_Contains(ST_GeomFromText('POINT(1 51)', 4326), polygon);
0 rows retrieved
SELECT * FROM areasTemp WHERE ST_Intersects(ST_GeomFromText('POINT(1 51)', 4326), polygon);
4 rows retrieved