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

[Pere Roca Ristol]

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)

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]

but I don't think any of this parameters may fit my needs, because as you
can see on the SQL code, i am reprojecting on the fly to calculate the area
for each code that is being clipped by the bounding box... so I am afraid I
need the complete postGIS SQL. In any case I tried also without calculating
the areas at the clip doesn't work either.

If this cannot be done via ogr2ogr, could you suggest me another tool? (*to
be executed programatically,* like a command line via PHP...)

thanks in advance



--
Sent from: http://osgeo-org.1560.x6.nabble.com/GDAL-Dev-f3742093.html