[gdal-dev] ogr2ogr from postGIS to shapefile not clipping

[Even Rouault]

On vendredi 24 août 2018 05:35:46 CEST Pere Roca Ristol wrote:
> I want to run a SQL query, in postgis 2.3.2, converting the result in a
> shapefile (using OGR2OGR) This is the SQL query that* perfectly works* from
> pgADmin:
> 
> select geom,a_code as code,ST_Area(ST_Transform(ST_Intersection(TABLE1.geom,
> ST_GeomFromText('POLYGON((31.7 -13.1, 31.6
>  -13.04,31.96 -13.04,31.9 -13.1,31.7
>  -13.1))',4326) ),32736))*0.0001::double precision as area from TABLE1
> where...
> 
> Trying go execute in ogr2ogr (see below), the '*clipping*' part doesn't
> work,so I get the *full* postGIS table (but in the saved shapefile)

What do you call the 'clipping part' : the ST_Intersection uses inside the 
ST_Area ? That clips only for the purpose of ST_Area operation, but not in the 
geom column that is selected. So it is expected that your geometries in the 
geom column are not clipped (neither in pgadmin, ogr2ogr or whatever other 
tool)

> 
> ogr2ogr -f 'ESRI Shapefile' unzipped/test_sql.shp PG:'host=myhost user=user
> dbname=dbname password=ww!' -sql 'select geom,a_code as
> code,ST_Area(ST_Transform(ST_Intersection(TABLE1.geom,
> ST_GeomFromText('POLYGON((31.7 -13.1, 31.6
> -13.04,31.96 -13.04,31.9 -13.1,31.7 -13.1))',4326)
> ),32736))*0.0001::double precision as area from TABLE1 where...'
> 
> Later i discovered parameters like...
> 
> [-clipsrc [xmin ymin xmax ymax]|WKT|datasource|spat_extent]
>            [-clipsrcsql sql_statement] [-clipsrclayer layer]
>            [-clipsrcwhere expression]
>            [-clipdst [xmin ymin xmax ymax]|WKT|datasource]
>            [-clipdstsql sql_statement] [-clipdstlayer layer]

Those will only operate on the source or target SRS. If you need to clip in a 
intermediate SRS, then you need to do the clipping in SQL with ST_Transform 
and ST_Intersection in the column that selects the geometry.

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com