[GIS] How to use pgsql2shp when PostGIS extensions installed in a separate schema as geometry

pgsql2shppostgispostgresqlshapefile

I have the PostGIS extension (postgis functions and spatial_ref_sys table) added to a specific schema called postgis. I'm wanting to run the pgsql2shp utility with a select query on geometries stored in other schemas that do not have postgis functions available within their respective schemas.

Normally from psql I would first set the search path to contain both the postgis schema and the schema with the tables I'm querying on, but I'm having trouble figuring out how to do the same thing with the pgsql2shp utility. How would I go about setting a search path or having it look in multiple schemas so it picks up the postgis functions?

Example Query (field_polygons is in different_schema):

pgsql2shp -f shapefile -h localhost -u user -P password gisdb "SET search_path TO postgis, different_schema; SELECT the_geom FROM field_polygons WHERE id = 1"

Results in error:

ERROR:  function postgis_version() does not exist
LINE 1: SELECT postgis_version()

Best Answer

First thing is that you're creating postgis extension on whole database not on single schema.
Second thing is you set the search_path if you don't wont to write schema name before every tablename, so notation 'different_schema.field_polygons' will work without setting patch.
I think the trouble is somewhere else... You're trying to create shapefile with only geometries - you have to select at least one more field, so this should work:

pgsql2shp -f shapefile -h localhost -u user -P password gisdb "SELECT row_number() over() as id, the_geom FROM different_schema.field_polygons WHERE id = 1"

If not please provide some more information like error message or sth..

Related Question