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.

tinlyx
  • 11,057
  • 18
  • 71
  • 119
Pallasch
  • 143
  • 7

1 Answers1

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

Community
  • 1
Pallasch
  • 143
  • 7