Given collection of points and polygons, determine which point lies in which polygon (or not)

Question

My question is almost similar to this. But in my case, the polygons are not necessarily touching/overlapping each other. They are present all over the space.

I have a big set of such polygons. Similarly, I have a huge set of points. I am currently running a RoR module that takes 1 point at a time and checks the intersection with respect to 1 polygon at a time. The database is PostGIS. The performance is quite slow.

Is there a faster or optimal way of doing this?

Answer 1

Can be done as one select statement, but for performance....look into a gist index on your polygons. FOr simplicity, lets say I have a table with a polygon field (geom data type) and a point field (geom data type). If you are doing a list of points in a list of polygons, do a cross join so each polygon and each point is compared.

select *
from t1 inner join t2 on 1=1
where st_contains(t1.poly,t2.point) = 't'

(modified to include the table join example. I'm using a cross join, which means every polygon will be joined to every point and compared. If we're talking a large record set, get those GIS tree indexes going)

I'm currently doing this to locate a few million points within a few hundred polygons. If you have overlapping polygons, this will return multiple rows for every point thats located in 2 or more polygons.

May fail pending on the data type your points are stored as. If they are in a geom field, it'll flow fine. If you are using text values, you'll need to use the st.geomfromtext statement to turn your characters into a point. This will look more like:

st_contains(poly, st_geomfromtext('POINT('||lon||' ' ||lat ||')')) = 't'

I used a lat/lon example...only thing to watch for here is the geomfromtext requires you to create the point using || to create the string from your field. Let me know if you need assistance with the st_geomfromtext concept.