Search code examples
sqlpostgresqlpostgis

Postgis function

I have a table in postgres with some areas and geography data type (polygon format) and another table with products name and geographycal postion (point, 4326). I tryed this query

“select st_intersects(schema1.tableA.geom1,schema2.tableB.geom2) from schema1.tableA inner join schema2.tableB on st_intersects(schema1.tableA.geom1,schema2.tableB.geom2)

Or this one:

”select prod_id from schema1.tableA inner join schema2.tableB on  st_intersects(schema1.tableA.geom1,schema2.tableB.geom2) where 
st_intersects(schema1.tableA.geom1,schema2.tableB.geom2).”

Both are running without any output and don’t stop. The purpose is to get products that are in the specific areas. Postgis extension is insterted and i could use it for other tasks on this db.

Solution

  • Your second query can only be executed with a nested loop join, which will always be slow if both tables are large. The first query has the join condition twice, which will not make a difference, except that the query will be even slower.

    On the bigger of the two tables you should create a GiST index on the geometry column, that will speed up processing as much as possible. If the polygons are large and have many points, that will make the query slow.