... and then I encountered a bug in Postgis 2.0.4. Fuck. ST_geomfromgeojson rounds my z-values to integers, effectively making them useless.

I have a little example demonstrating this, and so I imagined to submit a bug. Unfortunately the bug-tracker requires an OS-Geo account, and to get that I need to write an email to get a "mantra" (see form ). Done that.

So what did I do (note just random-numbers):

insert into original_be_geometries(originally_type, originally_id, geom) 
values ('Test', 1, ST_setSRID(ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[1.23445,2.234455,3.33445],[4.12345,5.12345,6.56789],[7.012,8.111,9.0001]]}'), 31370) );

some random-numer, but if I then do something like select st_astext(geom) from original_be_geometries where originally_type= 'Test'; I get

LINESTRING Z (1.23445 2.234455 3,4.12345 5.12345 6,7.012 8.111 9)

Fuck. Instead if I use ST_GeomFromText it does work:

insert into original_be_geometries(originally_type, originally_id, geom) 
values ('Test', 2, ST_setSRID(ST_GeomFromText('LINESTRING Z (1.23445 2.234455 3.33445, 4.12345 5.12345 6.56789, 7.012 8.111 9.0001)'), 31370) );

returns the expected geometry

LINESTRING Z (1.23445 2.234455 3.33445,4.12345 5.12345 6.56789,7.012 8.111 9.0001)

So I am going to switch my workflow from exporting geojson to exporting WKT's which I can then use to import. Now if only oracle supported 3d geometries when exporting to WKT this would be easy :eye-roll: :le-sigh: :rolls-up-sleeves: :)

[UPDATE] My team-mate has Postgis 2.2.2 and there this just works. I did not find this bugfix in the changelog, but this is good news. Damn. Now I have to upgrade my postgresql/postgis. Using brew. OMG! Last time I lost a weekend trying to get it fixed, I think I will still write my own sdo2wkt3d instead ;) (and update later).

Developing rails websites with a geographic component we rely heavily on Postgis, so we use activerecord-postgis-adapter for the Postgis support, and I always use schema_plus because it allows me to define views. Until recently, I always had to use the structure.sql instead of the schema.rb because the geometric columns did not dump correctly.

But for a while now, activerecord-postgis-adapter handles this correctly and so we use the schema.rb file again. Only to discover a "new" error:

ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR: cannot drop view geography_columns because extension postgis requires it
HINT: You can drop extension postgis instead.
: DROP VIEW IF EXISTS "geography_columns"

Apparently specific Postgis views are also dumped in the schema file, and those views obviously cannot simply be re-created.

A very naive solution I kept using was to comment those create_view lines in our schema.rb file. But apparently there is a much better solution: you can configure which tables and views schema_plus should ignore.

So I added an initializer in /initializers/schema_dumper.rb with the following content:

ActiveRecord::SchemaDumper.ignore_tables = [
   "geography_columns", "geometry_columns", "spatial_ref_sys", "raster_columns", "raster_overviews"
]

And now my schema.rb is correct, and simple commands as rake db:setup or rake db:test:prepare just work. Hehe.