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?
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.
