Loading GML data into Postgres - No geom!

Question

I'm trying to load an Environment Agency GML file into Postgres/PostGIS using ogr2ogr. The file loads fine, all the other data is there, but when I check the table in the database there is no geometry column. As it is a polygon dataset this is a problem!

ogr2ogr command is:

ogr2ogr -f "PostgreSQL" PG:"host=* port=* dbname=* user=* password=*" RoFRS_v201701.gml -overwrite -progress --config PG_USE_COPY YES

The GML file has a XSD file with it.

Also the GML opens perfectly in QGIS, with polygons displayed as they should be.

Any suggestions for getting the geom into Postgres?

Update:

Results from ogrinfo:

Geometry: Polygon
Feature Count: 2130752
Extent: (134111.801000, 11510.471300) - (655988.124900, 657600.687500)
Layer SRS WKT:
(unknown)
OBJECTID: Integer (0.0)
PROB_4BAND: String (8.0)
SUITABILITY: String (25.0)
PUB_DATE: Integer (0.0)
RISK_FOR_INSURANCE_SOP: String (3.0)
SHAPE_Length: Real (0.0)
SHAPE_Area: Real (0.0)

• user30184: What ogrinfo RoFRS_v201701.gml -al -so shows?

Answer 1

The core postgis extension installs PostGIS geometry, geography, raster, spatial_ref_sys and all the functions and comments with a simple: CREATE EXTENSION postgis; command. [...] (Source: http://postgis.net/docs/postgis_installation.html)

It seems like your database is not a PostGIS one (only PostgreSQL), so geometries are not read. Before loading your data, simply execute:

ogrinfo PG:"host=* port=* dbname=* user=* password=*" -sql "CREATE EXTENSION postgis;"

Update:

PostGIS is enabled yet, so this is not the reason why the gml is not load.

ogrinfo doesn't recognize the Geometry Column (no geometryProperty is available), so the associated xsd file could not be valid for OGR. Setting the GML_ATTRIBUTES_TO_OGR_FIELDS open option to YES would do the job:

ogr2ogr -f "PostgreSQL" PG:"host=* port=* dbname=* user=* password=*" RoFRS_v201701.gml -overwrite -progress -oo GML_ATTRIBUTES_TO_OGR_FIELDS=YES

• Matt: Sorry afalciano, I should have specified. This is a postgis enabled database. I have other tables already with geometry.
• Antonio Falciano: @Matt Ok, I can read what ogrinfo shows now: the Geometry Column is not reported. That's why is not loaded correctly.

Answer 2

A simple solution as it turns out, but I will post it here in case anyone comes across the same problem in future:

I renamed the .XSD file so it was ignored, loaded the .GML in QGIS so that a new .GFS file was created, and then ran the ogr2ogr command again to load the data. With the new .GFS file the data load worked fine, complete with a geom column.

Answer 3

This may, or may not be, an answer to this question, but I seem to remember something exactly similar when I was having help setting up a PostGIS database, and loading Natural England data into it.

So this is the caveat. I haven't written this script, or claim to fully understand it at the moment, but it sorted out some geometry problems that I had, and remains as part of one of the core scripts in the repository. I run it in pgAdmin.

I have permission to share this script here.

I have left the comments in to help

- Ensures the geom column contains geometry of plain, 
 ST_MultiPolygon and not the variant with additional dimensions
 -- Run the queries below to check the type of geometry currently stored 
-- in the geom column
-- SELECT DISTINCT ST_GeometryType(geom) FROM schema.table; -- We want ST_MultiPolygon
-- SELECT DISTINCT ST_NDims(geom) FROM schema.table; -- We want 2
-- If the above reports > 2 dimensions, you can force it to 2d by:
-- Adding an additional column called geom_2d of the correct type and projection
ALTER TABLE schema.table
ADD COLUMN geom_2d geometry(MultiPolygon,27700);
-- UPDATing that new column by passing the original geometry through the ST_Force2D
-- PostGIS function
UPDATE schema.table SET geom_2d = ST_Force2D(geom);
-- Dropping the original geometry
ALTER TABLE schema.table DROP COLUMN geom;
-- Renaming the new column to geom
ALTER TABLE schema.table RENAME COLUMN geom_2d TO geom;
-- Clean up any duplicate vertices and other geometry problems
DELETE FROM schema.table WHERE ST_Area(geom) = 0.0;
UPDATE schema.table 
SET geom = ST_Multi(ST_Buffer(geom, 0.0)) 
WHERE NOT ST_IsValid(geom);
-- And adding a spatial index to the new column
CREATE INDEX sidx_table_geom
 ON schema.table
 USING gist
(geom);