1

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?

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
|improve this answer|||||
  • 1
    I shall give that a whirl, and accept as answer. Thank you. – minisaurus Mar 5 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.