What is the correct syntax if I want to query a masked region from PostGIS from inside QGIS ?
The data is from "planet_osm_poly" (imported via osm2pgsql) and I'd like to use a polygon from a different table (table=e2e_buffers with name=corridor01) of the same database as mask of the spatial query.
I seam to have found the answer to my own question, using the following SQL from within QGIS using the PostGIS-SQL-Editor:
SELECT clipped.*
FROM (SELECT pol.*, (ST_Dump(ST_Intersection(ST_Transform(corridor.geom,4326), ST_Transform(pol.way,4326)))).geom As clipped_geom
FROM buffer_ger AS corridor
INNER JOIN planet_osm_line AS pol
ON ST_Intersects(ST_Transform(corridor.geom,4326), ST_Transform(pol.way,4326))) As clipped
WHERE (
(ST_Dimension(clipped.clipped_geom) = 1)
AND ((osm_id > -70000) AND (osm_id < -60000))
)
;
Please note, that the AND ((osm_id... line in the end was only added to reduce query time while testing.
Additionally, I'll instantly start to convert all my basic / general tables to the same SRID (How to change the SRID of exisisting data in PostGIS?) - this should avoid a lot of headaches and speed up things (no need of ST_Transform).
When trying to find a way to clip, I always expected PostGIS to internally recognize and deal with different SRIDs.
Sorry, if my question did bore some of you :-/
