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
_______________________________________________
gdal-dev mailing list
gdal-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/gdal-dev
