OSM Import Cannot find SRID (4326) in spatial_ref_sys

[four43]

When importing an OSM file (Minnesota) I am receiving the following error after the "Relation" step and most other steps complete:

St13runtime_error
pthread_join() returned exception: Throw location unknown (consider using BOOST_THROW_EXCEPTION)
Dynamic exception type: boost::exception_detail::clone_impl<boost::exception_detail::current_exception_std_exception_wrapper<std::runtime_error> >
std::exception::what: CREATE TABLE planet_osm_polygon_tmp  AS SELECT * FROM planet_osm_polygon ORDER BY CASE WHEN ST_IsEmpty(way) THEN NULL ELSE ST_GeoHash(ST_Transform(ST_Envelope(way),4326),10) END failed: ERROR:  GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys

Is there a way to populate that spatial_ref_sys table? Thanks.

[kwauchope]

It looks like this has probably been broken since osm2pgsql-dev/osm2pgsql@b2a9f72 which added 4326 as a requirement.

There are two options at the moment:

docker exec -it CONTAINER bash
su - postgres
psql -d gis "TRUNCATE spatial_ref_sys.sql"
psql -d gis -f /usr/share/postgresql/9.3/contrib/postgis-2.1/spatial_ref_sys.sql

This inserts all the SRS's. The truncate is required as 900913 has been in spatial_ref_sys.sql since PostGIS r12623 which means you will get duplicate key errors due to the insert called by createdb.

Alternatively, you can copy the required 4326 entry from spatial_ref_sys.sql and insert that by itself.

Happy to put in a pull request for either fix. Possibly sourcing all of spatial_ref_sys.sql (and remove the 900913 entry from run.sh) is the easiest and most fool/future proof rather than adding an individual SRS.

[four43]

I ended up using a different container: mdillon/postgis and that seems to be working well for our needs.

[SilentT-FR]

the result of your fixes

Importing /data/import.pbf into gis
800
osm2pgsql SVN version 0.87.4-dev (64bit id space)

Using built-in tag processing pipeline
Using projection SRS 900913 (Spherical Mercator)
Setting up table: planet_osm_point
2015-09-17 14:43:39 UTC ERROR: AddGeometryColumn() - invalid SRID

[SilentT-FR]

i use this files : https://raw.githubusercontent.com/postgis/postgis/svn-origin/svn-2.1/spatial_ref_sys.sql otherwise the 900913.sql

you have all ref (900913 and 4326) and its works for me

osm2pgsql SVN version 0.89.0-dev (64bit id space)

Using built-in tag processing pipeline
Using projection SRS 900913 (Spherical Mercator)

Reading in file: /data/import.osm
Using XML parser.
Processing: Node(1673k 98.5k/s) Way(155k 14.15k/s) Relation(2280 71.25/s)  parse time: 61s
Node stats: total(1673693), max(3738691346) in 17s
Way stats: total(155613), max(370144327) in 11s
Relation stats: total(2280), max(5498163) in 32s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads
Setting up table: planet_osm_nodes
Setting up table: planet_osm_ways
Setting up table: planet_osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        130611 ways are pending

Using 1 helper-processes
Finished processing 130611 ways in 34 sec

130611 Pending ways took 34s at a rate of 3841.50/s
Committing transaction for planet_osm_point
Committing transaction for planet_osm_line
Committing transaction for planet_osm_polygon
Committing transaction for planet_osm_roads

Going over pending relations...
        0 relations are pending

Using 1 helper-processes
Finished processing 0 relations in 0 sec

Committing transaction for planet_osm_point
2015-09-17 17:16:11 UTC WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_line
2015-09-17 17:16:11 UTC WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_polygon
2015-09-17 17:16:11 UTC WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
Committing transaction for planet_osm_roads
2015-09-17 17:16:11 UTC WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
Sorting data and creating indexes for planet_osm_roads
Stopping table: planet_osm_rels
Building index on table: planet_osm_rels
Sorting data and creating indexes for planet_osm_polygon
Sorting data and creating indexes for planet_osm_line
Sorting data and creating indexes for planet_osm_point
Stopping table: planet_osm_ways
Building index on table: planet_osm_ways
Stopping table: planet_osm_nodes
Stopped table: planet_osm_nodes in 0s
Stopped table: planet_osm_rels in 0s
Copying planet_osm_point to cluster by geometry finished
Creating geometry index on planet_osm_point
Copying planet_osm_roads to cluster by geometry finished
Creating geometry index on planet_osm_roads
Creating osm_id index on planet_osm_roads
Creating indexes on planet_osm_roads finished
Creating osm_id index on planet_osm_point
Creating indexes on planet_osm_point finished
All indexes on planet_osm_point created in 4s
Completed planet_osm_point
All indexes on planet_osm_roads created in 4s
Completed planet_osm_roads
Copying planet_osm_line to cluster by geometry finished
Creating geometry index on planet_osm_line
Creating osm_id index on planet_osm_line
Creating indexes on planet_osm_line finished
All indexes on planet_osm_line created in 11s
Completed planet_osm_line
Copying planet_osm_polygon to cluster by geometry finished
Creating geometry index on planet_osm_polygon
Creating osm_id index on planet_osm_polygon
Creating indexes on planet_osm_polygon finished
All indexes on planet_osm_polygon created in 23s
Completed planet_osm_polygon
Stopped table: planet_osm_ways in 26s
node cache: stored: 1673693(100.00%), storage efficiency: 52.03% (dense blocks: 63, sparse nodes: 1350389), hit rate: 100.00%

Osm2pgsql took 122s overall

[fredmaggiowski]

@kwauchope shouldn't this:

docker exec -it CONTAINER bash
su - postgres
psql -d gis "TRUNCATE spatial_ref_sys.sql"
psql -d gis -f /usr/share/postgresql/9.3/contrib/postgis-2.1/spatial_ref_sys.sql

be:

docker exec -it CONTAINER bash
su - postgres
psql -d gis -c "TRUNCATE spatial_ref_sys"
psql -d gis -f /usr/share/postgresql/9.3/contrib/postgis-2.1/spatial_ref_sys.sql

[zummach]

Solution implemented in PR #10 works! Why not yet merged?