ogr2ogr incorrectly formating geometry column values

Question

On Windows, I'm trying to load in a shapefile (found here) to PostGIS in order to be able to do geospatial queries. Using the following ogr2ogr command:

set PGCLIENTENCODING=LATIN1

ogr2ogr -f "PostgreSQL" PG:"dbname=my_database user=docker password=docker port=54320" "Census2011_Garda_Subdistricts_Nov2013.shp" -a_srs "EPSG:29902" -nlt PROMOTE_TO_MULTI -skip-failures

I get no errors or failure on the console. All polygons are now MultiPolygons. ogr2ogr automatically created a table for me that looks like:

In addition to doing things like querying for points within these features, I'd like to be able to query these that table to return GeoJSON using:

SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(feature)
)
FROM (
  SELECT jsonb_build_object(
    'type',       'Feature',
    'id',         ogc_fid,
    'geometry',   ST_AsGeoJSON(wkb_geometry)::geometry,
    'properties', to_jsonb(row) - 'gid' - 'geom'
  ) AS feature
  FROM (SELECT * FROM public.census2011_garda_subdistricts_nov2013) row) features;

that I found on Stack Overflow here.

However, this query gives me the following error:

SQL Error [XX000]: ERROR: parse error - invalid geometry
  Hint: "{"" <-- parse error at position 2 within geometry

because the geometry table seems to be wrong:

Is this a problem with the ogr2ogr command that I can tweak? Is it an issue with the original shapefile? Is the table defined wrong?

Thanks for your help.

Answer 1

There is a faulty cast in your query.

You wrote

SELECT jsonb_build_object(
...
'geometry',   ST_AsGeoJSON(wkb_geometry)::geometry,

which basically creates a geojson that you try to cast back to a geometry, which fails.

You can just remove this cast, or cast it to jsonb as in the original query.