PostGIS match geometry in another table

Question

I'm trying to find records from two tables with matching point geometry. The tables have different EPSG types. I do this:

SELECT count(*) FROM t1
WHERE st_intersects(t1.geom, (SELECT st_transform(t2.geom, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs', 3008) FROM t2));

but get

ERROR: more than one row returned by a subquery used as an expression

I've also tried:

SELECT count(*) FROM t1
WHERE st_intersects(t1.geom, (SELECT st_transform(t2.geom, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs', 3008) FROM t2 LIMIT 1));

and get 0 rows.

I'm unsure now if there are no matches (it is a possibility here) or if my SQL requries improvement?

Answer 1

I try with two layers :

• t1: polygon layer (EPSG:3008)
• t2 : polygon layer (EPSG:4326)

I know that 4 polygons of the t1 layer intersect the polygons of t2.

To solve your problem, you need to transform the projection of the t2 table beforehand: by creating a new table or a temporary table with a query with. You can try the following query :

-- transform_t2 is your t2 layer with the new projection
with transform_t2 as (select st_transform(t2.geom, '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs', 3008) as geom from t2)
select count(*) from t1, transform_t2
where st_intersects(t1.geom, transform_t2.geom)
-- The result is 4

+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs is optional, PostGIS detects the projection system of your layer. You can execute the following query :

with transform_t2 as (select st_transform(t2.geom, 3008) as geom from t2)
select count(*) from t1, transform_t2
where st_intersects(t1.geom, transform_t2.geom)
-- The result is 4