I'm running ogr2ogr for a PostGIS to GeoJSON export like this:

ogr2ogr -s_srs EPSG:4326 -f GeoJSON unique_geoms.json "PG:host=localhost dbname=local_data user=local_user password=local_pass" -sql "SELECT MAX(geom)::geometry FROM geom_table GROUP BY ST_AsBinary(geom)"

ogr2ogr isn't very bright, and tries this query on my database:

SELECT ST_SRID("max") FROM(SELECT MAX(geom)::geometry FROM geom_table GROUP BY ST_AsBinary(geom)) AS ogrpggetsrid LIMIT 1

So the database has to run the entire subquery to even get a response! This is fine for small tables, but for really big tables, the operation takes forever.

As you can see, I tried using -s_srs EPSG:4326 to override the built-in SRID detection, but it doesn't work.

My query is designed to de-duplicate my point geometry. I could create a new table based on this query, but I'm not sure that would help.

  • 1
    Have you tried setting t_srs also? I know this might be a daft suggestion but I am also in the middle of a battle with ogr2ogr which is a great app, but with somewhat obscure docs – John Powell Dec 16 '14 at 17:13
  • 1
    Yes, -s_srs and -t_srs should be used as a pait. If that does not work I would have a try by making a view from the subquery and registering the view into geometry_columns for arranging direct access to the metadata of the view. – user30184 Dec 16 '14 at 17:51
  • I tried using -s_srs EPSG:4326 -t_srs EPSG:4326 together and I can confirm it still hits PostGIS with SELECT ST_SRID ... @user30184 how do I register the view in geometry_columns? – Martin Burch Dec 16 '14 at 18:50
  • 2
    Postgis documentation is often useful postgis.net/docs/… – user30184 Dec 16 '14 at 19:03
  • Yes, do you see this geometry in geometry_columns. – John Powell Dec 16 '14 at 19:52

Your Answer

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

Browse other questions tagged or ask your own question.