3

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()
  • It would be helpful to include the fact that you are accessing the conversion function through a separate application that requires certain parameters, like the search_path, to be set. Also, if you have found a solution, please feel free to add it as a separate answer below. – Get Spatial Aug 25 '14 at 16:57
1

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..

  • My mistake, what I meant is the postgis functions and spatial_ref_sys table are installed within a postgis schema, so that when I run the pgsql2shp utility, I get an error about postgis functions not existing: ERROR: function postgis_version() does not exist because its only searching the different_schema and not adding postgis to the search path – generaluser Aug 8 '14 at 17:51
  • Are U sure you're talking about schema not database? Default schema name is 'public'... maybe try to log in to this database and 'Create extension postgis;' or maybe I'm wrong and you need to alter extension, like here:postgresonline.com/journal/archives/… – Jendrusk Aug 8 '14 at 18:14
  • I am talking about a schema, I have a multi-tenanted application where each tenant is separated into their own schema and then the postgis functions/data are installed into a postgis schema. In the application's queries, the search path is set to include both the tenant schema and the postgis schema, and postgis functions work properly. I'm trying to do this same thing in the select query with pgsql2shp utility. If interested, the details of why are here: github.com/rgeo/activerecord-postgis-adapter – generaluser Aug 8 '14 at 18:23
  • Sorry, you were right... maybe try to login to database via psql and set search_path for user by altering role or for whole database by 'alter database mydb set search_path = "$user", public, postgis ;' so this will be set when you will connect with pgsql2shp – Jendrusk Aug 8 '14 at 19:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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