Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to export a table from PostGIS using pgsql2shp. The basic query to export the entire table works fine using the following format:

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp" -h
localhost -p 5432 -u postgres -P xxxx mapdata planning.dm_planning-apps

However when I add a query (which works fine in PGAdmin III) the command fails, due to the hyphen in the table name.

Working query in PGAdmin:

SELECT * FROM planning."dm_planning-apps" WHERE "dm_planning-apps"."Modified_Reference"
ILIKE '%np%'

Included in command:

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp"  -h
localhost -p 5432 -u postgres -P xxxx mapdata "SELECT * FROM planning."dm_planning-apps"
WHERE "dm_planning-apps"."Modified_Reference" ILIKE '%np%'"

Produces a "syntax error at or near "-" and highlights the hyphen in "...FROM planning.dm_planning-apps WHERE...". The double quotes obviously aren't doing the same job they were within PDAdmin where the query worked fine.

Can anyone suggest a way to make this query work without renaming tables?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

There's probably an issue with escaping double quotes in your final command. To bypass that you could create a view in your Postgres database and give it a simple name. Something like:

CREATE VIEW v_export AS
  SELECT * FROM planning."dm_planning-apps" 
    WHERE "dm_planning-apps"."Modified_Reference" ILIKE '%np%';

Then from the command line you could use

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp" -h localhost -p 5432 -u postgres -P xxxx mapdata v_export

It's kind of a cheat, but it should work.

share|improve this answer
    
Thanks this worked perfectly. –  geobar Mar 25 '13 at 10:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.