1

I am new to PostGIS.

I have two tables, first is a table with polygons containing areas of municipalities, the second table is one with all my bike rides and has a column with linestrings in it. Both have different SRID numbers.

I want to know which municipalities I've ridding through with my rides (for all my rides, and per ride). This returns nothing (there should be a couple of municipalities in it. What am I doing wrong?

table1 = municipalities (multipolygons, srid 28992)

table2 = rides (linestring, srid 4326)

SELECT 
   a.geom
   a.name
FROM 
   table1 AS a 
LEFT JOIN 
   table2 AS b 
ON 
   ST_Intersects(st_transform(a.geom, 4326), b.geom) 
WHERE b.id = <ride_id>

Another try that isn't working:

SELECT 
   a.name, 
   a.geom, 
   b.geom 
FROM 
   table1 a, 
   table2 b 
WHERE 
   b.id='<ride_id>' 
AND 
   st_intersects(st_transform(a.geom, 4326), b.geom)

Haven't found an aggregated solution either.

1 Answer 1

0

I tested out a similar query using osm data but with an inner join and it seems to work:

SELECT distinct a.id, a.name 
FROM   county as a 
INNER JOIN cycleways as b ON ST_INTERSECTS(a.geom,b.geom)
1
  • Tnx, I created another column for the county where the srid matched the srid of the multipolyline and your inner join made it work. On the fly st_transform of the column didn't work. Dec 30, 2022 at 21:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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