GIS Data types

[kcphysics]

I am working on an application that is geospatially enabled, and am using PostGIS to do it (for those that don't know PostGIS is a GIS library for the PostgreSQL database).

I see that no work has currently been put into GIS data types and models, and in other programming languages (like python), usually GIS data types and support are left for other modules.

I pose this question, should the inclusion of GIS data types (point, line, polygon, multiline, multipoint, multipolygon, etc ect) and the resulting function (ST_Contains, ST_Within, ST_Centroid, etc) be included as part of node-orm2, or should it be relegated to its own package?

If it is its own package, can someone point me to the plugin architecture for node-orm2?

[notheotherben]

Hi @lselvy, to answer your question in part, unfortunately there is no hard documentation for the plugin architecture used by node-orm2 - that being said, there are a number of plugins out there including node-orm-transaction, node-orm-mysql-fts and node-orm-timestamps which should give you the tools you need to get started.

Please feel free to ask questions if you get lost, I know the codebase can be a bit daunting for those unfamiliar with its various intricacies, and we'll try and help out wherever we can.

[kcphysics]

Ok, so the answer is to roll up a new project for GIS enabled node-orm?

Just wanted to know if we should natively support it.

[notheotherben]

I think integrating the functionality into the core is something @dresende would need to decide on, but there are really no disadvantages to anybody in using the plugin framework as it will allow people who want the functionality to easily integrate it while ensuring that the codebases remain separate for maintenance purposes.

[dresende]

I also use GIS in some projects, but I think many don't need it, so my opinion is to choose one this paths:

1. The GIS integration is simple and won't complicate the orm code, so we add it to the core.
2. The GIS integration might get complex and full featured and then it should be in it's own module. Changes to the plugin architecture can and should be made to help the module to fully interact with core.

I'm inclined to path 2 but if you have a different opinion or another path feel free to share.

[dxg]

I also wonder how much of this can already be done via custom properties.
This feature was created for & tested against PostGIS.
That said, it's not a reusable PostGIS 'package' nor does it allow JS querying; you'd have to search by SQL.

[kcphysics]

Given that custom properties should be used to implement the GIS data types, should we consider having them part of the core set?

In the long run I can go either way and have been brushing up on the plugin architecture so that I can add the appropriate data types and allow for JS querying. I think it would round out node-orm very nicely. I only know PostGIS, so the first implementation would be for PostGIS, but I could teach myself the other DBs GIS capabilities.

I think I am going to start with a plugin and see how far I can get. I will list the repo for it here after I have something working.

I would agree with dresende's point above. If we are talking a full featured implementation we should think plugin. If that means a change to the plugin architecture I'll report back on it.

[dxg]

Plugin sounds good 👍

[kapouer]

I'm trying to do just that, but i'm stuck at building a custom select expression for the geometry column, something like 'ST_AsGeoJSON(' + prop + ')::JSON AS ' + prop.
I don't know how to tell orm to do that.

[kapouer]

Uggh

var driver = orm.db.driver;
driver._find = driver.find;
driver.find = function (fields, table, conditions, opts, cb) {
    for (var i=0; i < fields.length; i++) {
        if (fields[i] == "geom") {
            // TODO db.use(myplugin) gives access to Model, then Model.properties can be
            // used to find fields[i] type instead of hard-coding "geom" column name
            fields[i] = function(h) {return 'ST_AsGeoJSON(geom)::JSON AS geom';};
            break;
        }
    }
    driver._find.call(this, fields, table, conditions, opts, cb);
};

a kitten is dying each time i spawn the app

[dxg]

Poor kittens :(
This relates to #375 which is next on my ORM todo-list.

With that in mind & once 375 is done, what would the PostGIS plugin need to do beyond defining PostGIS property types?

[dxg]

Now that #488 is merged, it should be possible to create a custom type which will automatically add ST_AsGeoJSON() when selecting.

Specifically:

datastoreGet: function (prop, helper) {
  return helper.escape('ST_AsGeoJSON(??)', [prop.mapsTo]);
}
// "SELECT `abc`, `def`, (ST_AsGeoJSON(ghi)) AS `ghi` FROM `table`"

[kapouer]

Now an obvious missing piece is the ability to define custom indexes types.
Examples:

CREATE INDEX index_geos_prop_myindex ON geos ((prop->>'myindex'));
CREATE INDEX index_geos_geom ON geos USING GIST(geom);

[dxg]

I would recommend writing the SQL for that by hand. If using the migrations library this is as easy as executing the required SQL in a migration. I'm a bit unwilling to add suport for this as it's postgres specific. One would need to implement this in an DB agnostic manner.

[kapouer]

I'm already doing this (i'll see if it's easier with node-migrate-orm2) with sql statements (no animal is hurt this time), and figured there was some kind of consistency in defining an index as bound to a type.
So it might make sense to be able to customize index "lifecycle" in the same place one defines a custom type.