Can not insert into a "geography" field in postgresql.

[EvanCarroll]

I keep getting this error...

error retrieving column user_geog: error deserializing column 6: cannot convert between the Rust type core::option::Option<geo_types::geometry::point::Point> and the Postgres type geography

I don't believe it's possible to encode for a geography field in postgres with this module.

[netthier]

PostGIS supports casts between geometries and geographies.
Furthermore, you may need to cast the Point to a Geometry first, as that is what the geozero traits are implemented on.
As I've written on Discord, this example code using sqlx inserts and selects a geography from PostGIS:

query!(r#"TRUNCATE TABLE experiment_geog"#).execute(&pool).await?;
query!(r#"INSERT INTO experiment_geog (geog) VALUES ($1::geography)"#, my_point.to_ewkb(CoordDimensions::xy(), Some(4269))? as _).execute(&pool).await?;
let my_geom = query_scalar!(r#"SELECT geog::geometry AS "geog: wkb::Ewkb<Vec<u8>>" FROM experiment_geog LIMIT 1"#).fetch_one(&pool).await?.unwrap();
dbg!(my_geom.srid(), my_geom.to_geo()?);
let srid = query_scalar!(r#"SELECT ST_SRID(geog) FROM experiment_geog LIMIT 1"#).fetch_one(&pool).await?;
dbg!(srid);

producing the following output:

[src/main.rs:18:5] my_geom.srid() = None
[src/main.rs:18:5] my_geom.to_geo()? = POINT(50.0 10.0)
[src/main.rs:20:5] srid = Some(
    4269,
)

Due to #195 the .srid() call seems to not work, so getting it via ST_SRID is required. But generally geography support should work by casting through geometry.

It's also possible to avoid the casting entirely by using https://postgis.net/docs/manual-dev/en/ST_GeogFromWKB.html and https://postgis.net/docs/manual-dev/en/ST_AsBinary.html instead, at the cost of slightly longer queries

(EDIT: I initially thought geographies were limited to EPSG:4326, so used WKB instead of EWKB, but I was wrong about that)

[michaelkirk]

Disclaimer as I've said elsewhere: I don't currently use geozero's postgres integration....

Furthermore, you may need to cast the Point to a Geometry first, as that is what the geozero traits are implemented on.

But could we address this small part of the problem by implementing the geozero::Geometry for all the geo_types::Geometry subtypes? (e.g. geo_types::Point, geo_types::LineString, etc.?

Like this: https://github.com/georust/geozero/compare/mkirk/geo-types-variants?expand=1

It would be verbose, but not hard, and if we could save users a line or two of boilerplate it'd be worth it in my estimation. Partly because of the boilerplate reduction, but more importantly to address the confusion and wasted time of mismatched expectations.

I know this doesn't address anything about srids. I'm honestly not yet sure what would be best there. In my own projects, I tend to be working with wgs84 and one cartesian CRS, so it's pretty simple to just handle this on the edges of the application logic.