0

I've used ogr2ogr to convert tables in PostgreSQL database to GeoJSON which works fine for the public schema. The question is how can I set the schema to non-public to access my tables? I've already using ACTIVE_SCHEMA option as follow but it gives me not existing relation error.

ogr2ogr -f "GeoJSON" out.json "PG:host=localhost dbname=test user=postgres ACTIVE_SCHEMA=line" -sql "select * from Road_a" 

error:

ERROR 1: ERROR:  relation "road_a" does not exist
LINE 1: DECLARE executeSQLCursor CURSOR for select * from Road_a
  • 2
    Try "select * from line.Road_a" to force the schema line – Sociabilis May 25 at 10:56
  • 1
    Remember the table name is case sensitive. If the table is Road_a, it must be double quoted – JGH May 25 at 11:11
  • I've used the double quote for table name and also added the line.Road_a without success. – nasser tahani May 25 at 12:00
0

I don't see the syntax of your connection string documented in https://gdal.org/drivers/vector/pg.html. The options that are supported within the connection strings are there common PostgreSQL options https://www.postgresql.org/docs/8.4/libpq-connect.html.

Because ACTIVE_SCHEMA is a GDAL open option try this:

ogr2ogr -f "GeoJSON" out.json "PG:host=localhost dbname=test user=postgres" -oo  ACTIVE_SCHEMA=line -sql "select * from Road_a"
  • Using your command make the same error: relation "road_a" does not exist. – nasser tahani May 28 at 6:01
0

Using the combination of Sociabilis and JGH hints the problem solved as follow:

ogr2ogr -f "GeoJSON" out.json "PG:host=localhost dbname=test user=postgres password=x" -sql "select * from line."\Road_a\"" 

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.