Support for PostGIS spatial objects

[bettdouglas]

There's a package Dart JTS which is a port of Java Topology Suite which Java ORM libraries like Hibernate Spatial use to support PostGIS datatypes.

If someone needs to implement support for a custom datatype, how can it be added to it.

Some Pointers about PostGIS(geometry) datatype.

• It has 2 major data types, Geometry or Geography.

• It stores the data as String in format called WKB(Well-known binary) and this package Dart JTS can decode & encode wkb formats and return the relevant datatype.

• It is the best and most amazing packages for spatial analytics with super fast speeds and has pretty nice functions.

So, I need pointers to how I can handle this as I would love to have ability to create models with geometric support.

The link on how to add support for custom datatypes is broken so any pointers to implementing this could be awesome.

[isoos]

@bettdouglas: thanks for the pointer. I have not much time to adopt this library here, but if you have create a PR, I'm happy to review it.

I have no idea about the broken pointer (in types.dart), maybe @joeconwaystk has some memories of it?

[bettdouglas]

Hi @isoos I've managed to make it read geometries from the database as well as done all switch statements as described in the adding new types.
I need help on the typesMap.

As per the requirements on types.dart,
Below is a summary of it. I need some direction on that.

Adding a new type:

1. add item to this enumeration (Done)
2. update all switch statements on this type (Done)
3. add pg type code -> enumeration item in PostgresBinaryDecoder.typeMap (lookup type code: https://doxygen.postgresql.org/include_2catalog_2pg__type_8h_source.html)(Issue: the oid changes per the database since it's an extension.)
4. add identifying key to PostgreSQLFormatIdentifier.typeStringToCodeMap. (Done assuming its the name of the datatype)

I've highlighted the issues as //TODO:s. I am still a junior dev so I'd kindly appreciate the assistance)

The fork is here

I need some help on that. I can't make a pull request because those features are still missing. Or should I?

I've made a companion dart-project that uses Alaska data to display the different types of geometric features which can be stored in databases. (Rivers, Lakes, Airports, RailRoads,regions). Instructions on setting up the example as well as postgis is included here Load PostGIS test data & Dart example

[isoos]

@bettdouglas: a really nice start, thank you for doing that! I think the best would be to create a pull request with a [WIP] (work in progress) title prefix, and it would be best to iterate on that. It also makes the change tracking easier (per-commit).

I'd strongly suggest to create a separate unit test for it. Your example project seems a bit too large for that, it would be nice to have a shorter and condensed example. Don't worry if it is not passing yet, but we'll need it anyway, and the earlier you have an automated way of testing its current state, the better off you are. You should also keep the example project to test the latest changes on broader test cases.

(The doxygen URL is broken.) The type identifier handling may need to be refactored for this use case - the simple unit test will also help me to do that. It may be that we should use a flag in the constructor, and if that is set, on connecting, it can scan the OIDs and cache them for the lifetime of the connection. Do we have such a query to run?

[bettdouglas]

Thank you for the pointers. I'll look into the unit tests and update you as we move along.
For the on connecting query, that is a great idea.
By running this query,
select oid,typname,typarray from pg_type where typname in ('text','int2','int4','int8','float4','float8','boolean','date', 'timestamp','timestamptz','jsonb','name','uuid','geometry', 'geography');
returns the respective oid, typname as described in the static PostgreSQLFormatIdentifier.typeStringToCodeMap.
Will this help?

[isoos]

Will this help?

Sure, yeah! However, I wouldn't query for the known/fixed ids, only the dynamic ones.

[bettdouglas]

I've added some tests on it. I am not sure if they're enough and the needed ones. I've added tests on insert and reading the inserted values and doing some tests to ascertain they're valid.
I'll follow up on creating a PR as you suggested.