2

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.

1

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 :-/

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.