GeoServer can't determine native CRS from PostGIS db with wkb_geometry column

Question

I am trying to extract a banks layer from daily OSM extract pbf files for Nepal. After using osmosis and ogr2ogr, I was able to get it into a PostGIS Database.

Here are the commands I used:

osmosis --read-pbf-fast file="latest.pbf" --tf accept-nodes amenity=bank,atm,bureau_de_change --tf reject-ways --tf reject-relations --write-pbf file="banks.pbf"

ogr2ogr -f PGDump banks.sql banks.pbf -lco COLUMN_TYPES=other_tags=hstore --config OSM_CONFIG_FILE conf/banks.ini

Relevant bits of the ini file:

[points]
# common attributes
osm_id=yes
osm_version=no
osm_timestamp=no
osm_uid=no
osm_user=no
osm_changeset=no

# keys to report as OGR fields
attributes=name,barrier,highway,ref,address,is_in,place,man_made
# keys that, alone, are not significant enough to report a node as a OGR point
unsignificant=created_by,converted_by,source,time,ele
# keys that should NOT be reported in the "other_tags" field
ignore=created_by,converted_by,source,time,ele,note,openGeoDB:,fixme,FIXME
# uncomment to avoid creation of "other_tags" field
#other_tags=no

Here's the first column from the PostGIS database:

ogc_fid |                    wkb_geometry                    |  osm_id   |        name         | barrier | highway | ref | address | is_in | place | man_made |                 other_tags                  
---------+----------------------------------------------------+-----------+---------------------+---------+---------+-----+---------+-------+-------+----------+---------------------------------------------
       1 | 0101000020E61000007D29972169545540B24F00C5C8AE3B40 | 425306730 | Nabil Bank Pulchowk |         |         |     |         |       |       |          | "amenity"=>"bank", "operator"=>"Nabil Bank"

I then performed these steps:

1. Created a new store in my GeoServer and linked it to this PostGIS db.
2. Clicked on Layers > Add Layer and chose the new store.
3. Found the banks layer on it and clicked "Publish".

On the publishing page, the native SRS was not set and the declared SRS was EPSG 404000. The bounding box was also empty.

On clicking Compute from data, I got an error. Compute from native SRS didn't work at all.

Therefore, I set the declared SRS to EPSG 4326 and tried to compute from data. It returns -180 -90 180 90

With this, I published the layer, but I can't preview it on the GeoServer. I don't think the CRS has been applied correctly.

How do I set the Native CRS correctly once the data is in the PostGIS db?

Answer 1

Normally the geometries imported from OSM are in EPSG:3857, try forcing it in the GeoServer UI.

As a side note, HSTORE data type is not supported at all in older versions of GeoServer, and not very well supported even in the latest ones. I would recommend importing the data using imposm3 instead: https://github.com/omniscale/imposm3

If you're not happy with the sample mapping file, give this one a try: https://github.com/aaime/osm/blob/master/osm/mapping.yml