PostGIS Geography and Geometry support

[EvanCarroll]

I have a table that I've created like this,

CREATE TABLE foo (
    coords geography(POINT)
);

When I try to select from it too a geo_types::Point, I get

thread 'tokio-runtime-worker' panicked at 'error retrieving column coords: error deserializing column 3: cannot convert between the Rust type geo_types::point::Point<f64> and the Postgres type geography', /home/ecarroll/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-postgres-0.7.5/src/row.rs:151:25

It would be nice if this functionality was provided with with-geo-types-0_7

I tried moving the type to geometry with,

ALTER TABLE foo ADD COLUMN coords2 geometry(POINT);
UPDATE viet.property SET coords2 = coords;

I get

thread 'tokio-runtime-worker' panicked at 'error retrieving column coords2: error deserializing column 29: cannot convert between the Rust type geo_types::point::Point<f64> and the Postgres type geometry', /home/ecarroll/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-postgres-0.7.5/src/row.rs:151:25

I also tried moving the type to WKT with ST_AsText, and I get

thread 'tokio-runtime-worker' panicked at 'error retrieving column coords: error deserializing column 3: cannot convert between the Rust type geo_types::point::Point<f64> and the Postgres type text', /home/ecarroll/.cargo/registry/src/github.com-1ecc6299db9ec823/tokio-postgres-0.7.5/src/row.rs:151:25

At this point, I don't understand what the with-geo-types-0_7 feature does in tokio-postgres. It would be nice to have it documented.

[EvanCarroll]

I see what this crate provides, it provides the the ability to go from the native geometric type to a geo_type. This is probably not very useful for a PostGIS user which is stores geography. Though you can convert with ::geometry::point

Ie,

SELECT geog::geometry::point  AS native_point
FROM ( VALUES (ST_MakePoint(0,0)::geography) ) AS t(geog);

---

Just to be clear the request here is for native support of PostGIS's geography and geometry types. And not just the non-geospatial postgresql types.

[jbg]

@EvanCarroll in case you haven't seen it, you may find the postgis crate useful.

[EvanCarroll]

@jbg yeah, a lot of problems with that though too.

• No Default impl on Point (submitted patch)
• No From for Tuples on Point Type (submitted patch)
• No conversion to GeoJSON. (offered patch on geojson)

But ideally this type would just support PostGIS types regardless. If rust-postgres's type crate is interested in support PostGIS I could write a patch for it.

[jbg]

But ideally this type would just support PostGIS types regardless. If rust-postgres's type crate is interested in support PostGIS I could write a patch for it.

Does rust-postgres currently support any postgres types that are not either core postgres types or provided by extensions included with the core postgres distribution? Support for third-party extensions seems better in separate crates IMO.

[EvanCarroll]

Does rust-postgres currently support any postgres types that are not either core postgres types or provided by extensions included with the core postgres distribution? Support for third-party extensions seems better in separate crates IMO.

I'm not sure why it being in the official contrib would matter. It's the official way to do GIS on PostgreSQL, the subject of multiple books, and the industry standard for any implementation of GIS for any database. It's also packaged by every major distribution and referenced in the docs.

• https://www.postgresql.org/download/products/6-postgresql-extensions/

[jbg]

Well, that's up to @sfackler, it's just a difference from the existing types that are implemented here (which as far as I can see are all part of PostgreSQL itself). FWIW, we're using postgis crate since quite a while, it works well, and the issues you identified are pretty minor API style things which I'm sure they will accept your PRs for.

[bjornharrtell]

I'm interested in this too. It's unclear to me how it could be externalized to another crate because to be truly useful.

In my case I found myself blocked on using BinaryCopyInWriter which is the most optimal way to bulk load data into PostgreSQL but I cannot use it for PostGIS geometry type, since that does not exist as a postgres_types::Type.

@sfackler what is your opinion here, are you interested in including first class PostGIS support in this crate?

[sfackler]

You can retrieve a Type instance by preparing a query that uses the type, and pulling it out of the statement: https://docs.rs/postgres/latest/postgres/struct.Statement.html#method.params.

Extension types do not have fixed Oids, and so cannot have hardcoded Type values.

[bjornharrtell]

Thanks @sfackler, that might be the key information I needed. 🙂 And might mean it does fit well in an external crate.

[bjornharrtell]

I can get the type which is fx. Other { name: "geometry", oid: 4787952, kind: Simple, schema: "public" } but I fail to understand how I can provide the ToSql conversion logic for such a Type. Probably only a lack of current insight on my part, but can't hurt to ask if it should be possible to provide customized ToSql for the Other type?

[sfackler]

Enable the with-geo-types-0_7 Cargo feature and use https://docs.rs/geo-types/latest/geo_types/geometry/enum.Geometry.html.

[bjornharrtell]

I don't think that is what I need/want. What I want is fairly exotic perhaps, but it's specifically to be able to use the postgis geometry type as target for BinaryCopyInWriter. I have custom code that can produce the native binary representation of the postgis geometry type as a &[u8] and want to provide that as input to the write fn of BinaryCopyInWriter. That gives me this error:

Error { kind: ToSql(1), cause: Some(WrongType { postgres: Other(Other { name: "geometry", oid: 4787952, kind: Simple, schema: "public" }), rust: "&[u8]" }) }', src/main.rs:75:56

I intentionally do not want to go through another representation.

[sfackler]

If you already have the encoded bytes, then you can just make a ToSql implementation that directly writes them:

fn to_sql(&self, _: &Type, out: &mut BytesMut) -> Result<IsNull, Box<dyn Error + Sync + Send>> {
    out.extend_from_slice(&self.data);
    Ok(IsNull::No)
}

[bjornharrtell]

Might yet again be my lack of Rust experience that shows but I simply fail to understand how I can fit that snippet of code to apply to the moving parts here (the Other Type and &[u8]).

[sfackler]

struct MyGeometryWrapper {
    binary_encoded_geometry: Vec<u8>,
}

impl ToSql for MyGeometryWrapper {
    fn to_sql(&self, _: &Type, out: &mut BytesMut) -> Result<IsNull, Box<dyn Error + Sync + Send>> {
        out.extend_from_slice(&self.data);
        Ok(IsNull::No)
    }

    fn accepts(&self, ty: &Type) -> bool {
        ty.name() == "geometry"
    }

    postgres::to_sql_checked!();
}

let mut binary_copy_in_writer = BinaryCopyInWriter::new(copy_in_writer, &[geometry_type]);

let binary_encoded_geometry = encode_geometry_to_postgres_format();

binary_copy_in_writer.write(&[&MyGeometryWrapper { binary_encoded_geometry });
binary_copy_in

[bjornharrtell]

Much appreciated @sfackler, I got it working (after some tweaks) in my work in progress code to import osm data straight into postgis geometries.

A general implementation of the reusable parts should probably be contributable to the postgis crate, I hope to be able to do that at some point in the future.

[jjcfrancisco]

Hello, I am trying to implement this snippet but struggling to understand how do I pull the geometry_type part. I tried preparing a query but params comes out empty. Similar to @bjornharrtell, I am using the BinaryCopyInWriter and so knowing the type is essential.

Help with this would be much appreciated.

[HendrikJanssen]

I had the same problem and ended up selecting the type oid from the database and constructing the type manually

First pull out the OID from the database:

SELECT oid FROM pg_type WHERE typname = 'geography'; -- Or 'geometry'!

Then construct the type:

let geog_type = Type::from(String::from("geography"), <oid from select>, Kind::Simple, String::from("<schema>"));

This worked pretty well and let me use the BinaryCopyInWriter. Note that the oid is not stable between databases as mentioned here, so you probably have to do that manually in code every time you connect to a database.