2

I tried to export a shapefile from my PostGIS installation using pgsql2shp as follow

pgsql2shp -f tiles.shp -h <host> -u <user> -P <pass> <database> "SELECT id, the_geom FROM <table> WHERE ST_GeomFromGeoJSON('{ "type": "Polygon", "coordinates": [ [ [ -92.472398018272358, 18.086381878379395 ], [ -92.472398018272358, 21.705890714546868 ], [ -86.679183238981381, 21.705890714546868 ], [ -86.679183238981381, 18.086381878379395 ], [ -92.472398018272358, 18.086381878379395 ] ] ] }') && the_geom;"

and the command gives the error

Error executing user query: ERROR:  quoted object property name expected (at offset 2)

I suppose is a problem related with the GeoJSON query because when I run

pgsql2shp -f tiles.shp -h <host> -u <user> -P <pass> <database> "SELECT id, the_geom FROM <table> WHERE id = 1;"

the shapefile is generated without error.

  • 1
    this might be an issue with escaping of quotes and apostrophes in your json query. have you tried escaping them with a preceding backslash (\)? You might want to check this answer too. – Steven Kay Aug 12 '15 at 20:52
  • @StevenKay You are totally right. It's just a problem with quotes on the geojson, it resolved when quotes are escaped, just quotes, apostrophes are not necessary – jmbarrios Aug 13 '15 at 14:38
1

As @StevenKay mentioned, quotes on the GeoJSON should be escaped with preceding backslash.

pgsql2shp -f tiles.shp -h <host> -u <user> -P <pass> <database> "SELECT id, the_geom FROM <table> WHERE ST_GeomFromGeoJSON('{ \"type\": \"Polygon\", \"coordinates\": [ [ [ -92.472398018272358, 18.086381878379395 ], [ -92.472398018272358, 21.705890714546868 ], [ -86.679183238981381, 21.705890714546868 ], [ -86.679183238981381, 18.086381878379395 ], [ -92.472398018272358, 18.086381878379395 ] ] ] }') && the_geom;"

Another solution are presented on this question.

  • great! It would be nice if pgsql2shp allowed queries from files or stdin.. If you do this a lot, it might be worth writing a short python script to do the escaping and calling it via os.system() – Steven Kay Aug 13 '15 at 16:59

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.