Postgis Grid Points on Boundary Issue

Question

Hi all hoping someone can help me out with this issue.

I've got a table of polygons which are grid squares which get overlayed 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

Answer 1

If you don't care which polygon the point is in, then you just need to add the LIMIT keyword:

SELECT * FROM areasTemp WHERE ST_Intersects(ST_GeomFromText('POINT(1 51)', 4326), polygon) LIMIT 1;

Which will choose whichever row PostgreSQL chooses first. This will be arbitrary but consistent.

If you need a more specific rule as to which polygon a point is in, then you'll need to add an ORDER BY part to the SELECT statement to ensure your preferred table row is at the top of the results list.

Edit:

Following on from your reply, you'll need to select your polygon as a subquery, and join against that:

SELECT points.*, grid.*
    FROM (SELECT * FROM grid WHERE ST_Intersects(points.the_geom) LIMIT 1) AS grid
INNER JOIN
    ... your join clause ...

Aplogies if the details are little sketchy, I'm not near a PostGIS database at the moment to play around with it, but a subquery would be the way to go.

An alternative might be to rewrite the PNPOLY function as a PL/pgSQL function and put that into your WHERE clause.