[postgis-users] pgsql2shp on a on-the-fly request

[Nicolas Ribot]

Hi,
Enclose the query string in double quotes:
pgsql2shp -f file.shp database "select * from ..."

Didn't you forget to add the database name in your command ?

Nicolas


On 20 November 2012 11:37, perikut <peroc79 at gmail.com> wrote:

> hello, i am trying to make not a usual postgis to shapefile dump, as the
> geometry is created on the fly along with other calculations.
>
> this Sql works fine, as you can see i create the geom on the fly
>
> select SUM(counter) as suma, astext(snapped_geom) as centroid,
> geomfromText(ST_AsText(ST_Envelope(GEOMETRYFROMTEXT('LINESTRING('||(st_xmax(snapped_geom)-2.5||'
> '||(st_ymax(snapped_geom)-2.5)||', '||(st_xmax(snapped_geom)+2.5)||'
> '||(st_ymax(snapped_geom)+2.5)||')'),4326)))) as pols from ( SELECT
> (regexp_matches(t::text,
> E'(Anguilla[\+])([0-9]+)'::text)::text[])[2]::integer as
> counter,snapped_geom from (select ST_SnapToGrid( ST_SetSRID(the_geom,
> 4326), 5) as snapped_geom,array_genus from simple_datapublisher_3 where
> array_genus ~ 'Anguilla'::text group by snapped_geom,array_genus) t ) t2
> GROUP BY snapped_geom;
>
> but when i directly put on a pgsql2shp operation, i get error like...
> Syntax error near to unexpected symbol « ( »
>
> pgsql2shp -f /www/test -p 5432 -h host -u user -P pwd -g polygons select
> SUM(counter) as suma, astext(snapped_geom) as centroid,
> geomfromText(ST_AsText(ST_Envelope(GEOMETRYFROMTEXT('LINESTRING('||(st_xmax(snapped_geom)-2.5||'
> '||(st_ymax(snapped_geom)-2.5)||', '||(st_xmax(snapped_geom)+2.5)||'
> '||(st_ymax(snapped_geom)+2.5)||')'),4326)))) as polygons from ( SELECT
> (regexp_matches(t::text,
> E'(Anguilla[\+])([0-9]+)'::text)::text[])[2]::integer as
> counter,snapped_geom from (select ST_SnapToGrid( ST_SetSRID(the_geom,
> 4326), 5) as snapped_geom,array_genus from simple_datapublisher_3 where
> array_genus ~ 'Anguilla'::text group by snapped_geom,array_genus) t ) t2
> GROUP BY snapped_geom;
>
> I am not sure if paremeter -g (set as polygons because the resulting
> geometries are named polygons) is correctly set, but even removing it i
> allways get the same error.
>
> I know the SQL sentence is a little complicated, just below a very basic
> structure to get an idea what is doing.
>
> geom1, Anguilla +30||specieY+10||specieZ+5
> geom2, specieX+10|| Anguilla +10
> ...
>
> thanks in advance,
>
> Pere Roca
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20121120/c4a6f2b2/attachment.html>