Geography datatype

[JakobMiksch]

PostGIS has a Geography datatype. This is useful to store worldwide POI data and perform proximity analysis using ST_DWithin.
However, ( to my knowledge), osm2pgsql currently does not support importing the OSM geometry to a Geography column. Only these types seem to be allowed: https://osm2pgsql.org/doc/manual.html#the-flex-output
Would it be of interest for osm2pgsql to be able to create Geography columns? What do you think about this?

[joto]

It shouldn't be too difficult to add this. We'll just need a way to tell osm2pgsql in the config file that we want geography instead of geometry columns. We probably also need to check the SRID, because geography types only allow lat/lon SRIDs and we want to give a nice error message instead of failing with an SQL error. I don't have much experiences with the geography type, but I think that's probably it.

[pnorman]

Operating in EPSG:4326 and changing a couple bits of the WKB should be all that's necessary, but I haven't looked into WKBs in enough detail.

[joto]

PostGIS happily converts geometry WKBs from osm2pgsql into whatever geography uses internally. So we don't need to change anything there.

[JakobMiksch]

I guess the next step is to create an issue for that. My initial thoughts about necessary tasks in this context are:

• the implementation itself
• useful error message if user provides wrong CRS
• Tests
• Docs
• Example Lua Config

Do you have any further requirements?

Then I can create an issue on that ...

[joto]

Is there any need for supporting other projections except 4326 for the geography data type? If not then there is an easy backwards-compatible way to implement this. The projection parameter on geometry columns already support either a number (ie SRID) or a few strings like mercator and latlon (https://github.com/osm2pgsql-dev/osm2pgsql/blob/master/src/flex-table-column.cpp#L101-L125). This could be extended to allow geography as name, use -1 or something like that as m_srid and then use that in https://github.com/osm2pgsql-dev/osm2pgsql/blob/master/src/flex-table-column.cpp#L127-L178 to switch to geography type. Feel free to create a PR for that. And yes, we'll need some tests.

[JakobMiksch]

Only EPSG:4326 is relevant here. Thanks for the links to the code. I will have a look 👀

[mboeringa]

Only EPSG:4326 is relevant here. Thanks for the links to the code. I will have a look 👀

Not to sure about that, if we do want Geography support, it seems more natural to me to implement a generic solution also supporting all other geodetic SRSs PostGIS supports, to have maximum compatibility with PostGIS.

Support for other geodetic SRSs than EPSG:4326 was already added back in PostGIS 2.2 according to https://postgis.net/docs/using_postgis_dbmanagement.html#PostGIS_Geography, so should be well established and tested by now.

[joto]

Just a note here. This can currently be done using something like this:

{ column = 'geom', type = 'point', sql_type = 'geography(point)', not_null = true, projection = 4326 }

But it would be nice to have "official" support for this. If we restrict the use to 4326 projection we can do this:

{ column = 'geom', type = 'point', not_null = true, projection = 'geography'  }

If we want to allow all lat/lon projections:

{ column = 'geom', type = 'geography_point', not_null = true, projection = 4326 } -- 4326 would be the default

or

{ column = 'geom', type = 'point', geography = true, not_null = true, projection = 4326 } -- 4326 would be the default

Any opinions on this?

[mboeringa]

{ column = 'geom', type = 'point', sql_type = 'geography(point)', not_null = true, projection = 4326 }

@joto,

Does this syntax already support an arbitrary lat/lon SRID for the projection, so replacing the 4326 with another lat/lon based SRID? So in fact we already have everything that is needed to work with geography and arbitrary lat/lon SRID, maybe just no internal error checks in osm2pgsql to detect non lat/lon SRID numbers and properly error out in case of geography?

[joto]

Yes, you should be able to write this (note the two places where you need the SRID):

{ column = 'geom', type = 'point', sql_type = 'geography(point, 1234)', not_null = true, projection = 1234 }

[mboeringa]

Yes, you should be able to write this (note the two places where you need the SRID):

{ column = 'geom', type = 'point', sql_type = 'geography(point, 1234)', not_null = true, projection = 1234 }

@joto

I only tried this with SRID=4326 as input in my Lua file, but it worked like a charm and nicely outputted geography. Thanks for the tip :-)

[mboeringa]

@joto . In relation to @gdt 's question: if I had chosen another SRID number like 6319, but taken OpenStreetMap PBF with its inherit 4326, do I get the right output? How about the needed transformation? Does this require an extra transform step in the syntax?

[joto]

You could have just tried this in the time you wrote that question. But yes, this will just work. Exactly like it works with geometry types. You can do the transformation explicitly and if you don't, it will be done automatically for you.