2

I have a table containing point

CREATE TABLE Points
{
  pointID BIGSERIAL PRIMARY KEY NOT NULL,
  thePoint GEOGRAPHY(POINT)
}

CREATE TABLE Polygons
{
  polygonID BIGSERIAL PRIMARY KEY NOT NULL,
  aPolygon GEOGRAPHY(POLYGON) NOT NULL,
}

I wish to find all the points that are contained in each polygon. i.e the result should look like

polygonID| pointID
-------------------
1        | 1
1        | 2
1        | 5
1        | 7
2        | 2
2        | 3
...

I managed to go point by point and to figure out if it's in the polygon using ST_CoveredBy(thePoint, aPolygon). Based on that the naive solution is going in a nested loop over all points and polygons but for sure there is a more efficient and correct way to achieve this.

halfer
  • 19,824
  • 17
  • 99
  • 186
liv a
  • 3,232
  • 6
  • 35
  • 76

4 Answers4

4

Here's one way, which works on geography types. BTW, might be worth reading the manual on geometry and geography data types. As far as I understand it, there are many more functions available for geometries, but you have to get involved with projections. The best choice depends on what you're doing...

SELECT polygonID, pointID
  FROM Points INNER JOIN Polygons 
  ON ST_covers(polygons.aPolygon,Points.thePoint  );
mlinth
  • 2,968
  • 6
  • 30
  • 30
1

postgresql has polygon @> point

select * from points join polygons on polygons.aPolygon @> points.thePoint;
Jasen
  • 11,837
  • 2
  • 30
  • 48
  • @> gave me an error. Also, @ doesn't work with geography data type. Finally, @Aleksandar Stojadinovic is right - @ only considers bounding boxes... – mlinth Jan 08 '15 at 08:52
  • <@ will work correctly (not limited to bounding boxes), at least for geometry types. – IamIC Apr 13 '16 at 10:43
1

The answer was sort-of in your question: "within". Use the ST_DWithin operator.

SELECT polygonID, pointID
FROM Points
JOIN Polygons ON ST_DWithin(Points.thePoint, polygons.aPolygon, 0);

The last argument 0 is the distance within the polygon. This is useful to also select points that are, for example, within 10 m of the polygon, which is useful if there are positioning errors in the source data.

ST_Intersects(Points.thePoint, polygons.aPolygon) should also work.

See DE-9IM if you want to learn more on what these operators mean, but not all have geography type equivalents.

Mike T
  • 41,085
  • 18
  • 152
  • 203
0

It's been some time now since I've done anything with PostGIS, but I'll give it a try.

SELECT polygonID, pointID FROM Points, Polygons WHERE ST_CONTAINS(Points.thePoint , polygonID.aPolygon);

Aleksandar Stojadinovic
  • 4,851
  • 1
  • 34
  • 56
  • ST_CONTAINS never worked for me always get the following error: No function matches the given name and argument types. You might need to add explicit type casts. ST_CoveredBy and ST_Intersects work. – liv a Jan 07 '15 at 23:57
  • Ok, I haven't tested the example, I might have missed something. Is there any issue with using the CoveredBy method? You can greatly improve your performance if you use indexing (if you are not already). The ST_* methods in PostGIS can utilize them automatically. – Aleksandar Stojadinovic Jan 08 '15 at 08:19
  • 1
    st_contains only works on geometry types rather than geography types. @liva that might be the reason for your error. Note also the difference between st_contains and st_covers - http://lin-ear-th-inking.blogspot.de/2007/06/subtleties-of-ogc-covers-spatial.html. Finally, the arguments are in the wrong order - i.e. you'd need st_covers(polygons.apolygon, points.thepoint) – mlinth Jan 08 '15 at 08:42