I am working with postgres/postGIS I have two tables I am interested in querying:

 table A: contains geometry values as (POINT)  such as (P1, P2,P3,...) 
 table B: contains geometry values as (POLYGON) such as (Polygon 1, polygon 2, ...)

I am trying to find each point lies within which polygon. The answer I am most likely looking for lists the point and the polygon it contains, for example:

what I tried is the following :

SELECT *
FROM area, points 
WHERE st_intersects(area.polygons, points.point) ;

And I am getting the following error: ERROR: ST_Intersects: Operation on mixed SRID geometries (Polygon, 4326) != (Point, 8307)

would much appreciate your help.

CodePudding user response：

As stated in the previous answer, you cannot perform spatial operations involving geometries encoded in different SRS. In other words, you must use ST_Transform, e.g.

SELECT * FROM area a
JOIN points p ON ST_Contains(a.polygons, ST_Transform(p.point,4326));

The observation regarding indexing is also well put. However, you do not necessarily need to create a further column in order to use the spatial index in a different SRS. It would mean that you need to 1) duplicate your columns, which depending on the geometries size can become pretty expensive and 2) have a significant overhead to keep both columns in sync. What you need is to create a separated index that takes the coordinates previously transformed, so that it does not need to do it in query time:

CREATE INDEX idx_points_geom_4326 ON points USING gist (ST_Transform(point,4326));

Demo: db<>fiddle - mind the node Index Scan using idx_points_geom_4326 on points p:

EXPLAIN ANALYSE
SELECT * FROM area a
JOIN points p ON ST_Contains(a.polygons, ST_Transform(p.point,4326));

                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.14..59.18 rows=1 width=64) (actual time=0.036..0.175 rows=94 loops=1)
   ->  Seq Scan on area a  (cost=0.00..1.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)
   ->  Index Scan using idx_points_geom_4326 on points p  (cost=0.14..58.16 rows=1 width=32) (actual time=0.029..0.161 rows=94 loops=1)
         Index Cond: (st_transform(point, 4326) @ a.polygons)
         Filter: st_contains(a.polygons, st_transform(point, 4326))
 Planning Time: 0.064 ms
 Execution Time: 0.193 ms

CodePudding user response：

Why you get an error:

You can't compare geometries in different spatial reference systems, so you'll need to transform one of the columns to match the other one's SRID. You could fix this on the fly:

SELECT *
FROM area, points 
WHERE st_intersects(area.polygons, st_transform(points.point,4326));
--or, dynamically: st_transform(points.point,st_srid(area.polygons))

Performance and usability

The above will perform poorly, being unable to use an index. Assuming that:

1. Your data is static, meaning that you import your data once, in order to perform your operations. That's in contrast to a system, where you maintain an incoming stream of new geometries.
2. You plan to perform more operations involving these two and more tables.
3. You know your area of interest (specific city, country, continent).

it makes sense to build a separate column on each table, or keep only an already transformed version of the data, all in one reference system.

alter table points add column point_4326 geometry;
update points set point_4326=st_transform(point,4326);
create index on area using gist(polygons);
create index on points using gist(point_4326);

SELECT *
FROM area, points 
WHERE st_intersects(area.polygons, points.point_4326);

If you can afford it, you can keep the original column as a reference - SRS transformations aren't always entirely lossless, so it's good to keep the original data.