search

balderdashy / sails

Realtime MVC Framework for Node.js
https://sailsjs.com
MIT License
22.82k stars 1.95k forks source link

postgis geometry #5209

Closed jfgodoy closed 5 years ago

jfgodoy commented 10 years ago

I need to use a geometry field of postgis. I know that waterline can't have a so specific type.. but what about a work around?

attributes: {
  x: 'float',
  y: 'float',  
  geom: {
    type: 'string',
    geometry: {
      def: 'SRID=4326;Point',
      bind: ['x', 'y'],
      saveBindData: true
    }
  }
}

The idea is, if an attribute has a geometry property, then the adapter will ignore the type and will build the column definition using geometry.def. If saveBindData is true then the adapter will build the column definitions for that fields too, in other case its information will be used to build the geom data, but not will be saved in the database.

The bind variable can be also an array of coordinates following the geojson spec according to the type defined in geometry.def, or a string using the WKT spec, or even a json field using geojson spec.

what do you think about this approach?

cjbland commented 10 years ago

I would like to see this feature as well. I'd be willing to help with the implementation if need be. I've been using GeoDjango for a number of projects (years) and am starting to shift everything towards Node.js and Sails.js.

CWyrtzen commented 10 years ago

@jfgodoy @cjbland hey guys, did you find a resolution to this issue?

cjbland commented 10 years ago

I had the flexibility to change databases to MongoDB 2.6 and just executed the .query() myself.

jfgodoy commented 10 years ago

I forked waterline, waterline-schema and sails-postgresql to add a new branch that I called geometrySupport in which I added the geometry type.

CWyrtzen commented 10 years ago

Thanks for contributing~

tjwebb commented 9 years ago

@jfgodoy where is your fork?

jfgodoy commented 9 years ago

It's in: https://github.com/jfgodoy/sails-postgresql/tree/geometrySupport https://github.com/jfgodoy/waterline-schema/tree/geometrySupport https://github.com/jfgodoy/waterline-sequel/tree/geometrySupport

It's not up to date and doesn't have test :(.

tjwebb commented 9 years ago

Do you have the time to write some tests? I'm seeing more interest in this feature, and would happily merge a PR from you if you can clean it up a bit. Let me know, thanks.

kevbaker commented 9 years ago

So it isn't possible to use Waterline "Custom Types" to make a geometry data-type then?

I also need this for my project.

If there is code (@jfgodoy) I can reference to modify the sails-postgresql module I can jump on it I think.

@jfgodoy, do we just need to rebase your geometrySupport feature and create some tests for it?

tjwebb commented 9 years ago

@jfgodoy, do we just need to rebase your geometrySupport feature and create some tests for it?

Probably. This would provide initial support for geom types.

In the long term I'd love to support deeper PostGIS features as well, probably in an extension.

jfgodoy commented 9 years ago

I just do the rebase on my forks. I do not have the time to do tests. Theses are the changes I did to get a basic support for geometry types. A lot of work is required to make it robust, so please, only use my changes as reference.

https://github.com/jfgodoy/waterline-sequel/tree/geometrySupport

https://github.com/jfgodoy/sails-postgresql/tree/geometrySupport

https://github.com/jfgodoy/waterline/tree/geometrySupport

Also geometry columns require an spatial index to improve query performance. The implementation only support EWKT format, wich is a PostGIS specific format.

Examples of use:

/* api/models/Point.js */
module.exports = {
  connection: 'somePostgresqlServer',
  attributes: {
    id: 'integer',
    geom: 'geometry(Point, 4326)'
  }
};

Now you can insert geometry data:

POST http://localhost:1337/api/point
{
"id":1,
"geom": "SRID=4326;Point(70 33)"
}

And retrieve all the point inside a given area

GET http://localhost:1337/point?where={"geom":{"intersects": "SRID=4326;Polygon((-50 -10, -50 -50 ,-75 -50, -75 -10, -50 -10))"  }}
kevbaker commented 9 years ago

This is great Jorge.

I'll start by implementing your changes so I understand them better. Then should have some time to help with the tests.

On Sat, Mar 14, 2015 at 11:12 AM, Jorge Godoy notifications@github.com wrote:

I just do the rebase on my forks. I do not have the time to do tests. Theses are the changes I did to get a basic support for geometry types. A lot of work is required to make it robust, so please, only use my changes as reference.

https://github.com/jfgodoy/waterline-sequel/tree/geometrySupport

  • modify select builder to get geometry columns as text
  • replace returning * on queries to allow return geometry columns as text
  • add geometry search criteria. I only added intersects

https://github.com/jfgodoy/sails-postgresql/tree/geometrySupport

  • allow type geometry(, ). This need to be improved, I only check the word geometry.

https://github.com/jfgodoy/waterline/tree/geometrySupport

  • allow type geometry(, ). This need to be improved, I only check the word geometry.
  • add validation for geometry values. I did not.

Also geometry columns require an spatial index to improve query performance. The implementation only support EWKT format, wich is a PostGIS specific format.

Examples of use:

/* api/models/Point.js */ module.exports = { connection: 'somePostgresqlServer', attributes: { id: 'integer', geom: 'geometry(Point, 4326)' } };

Now you can insert geometry data:

POST http://localhost:1337/api/point { "id":1, "geom": "SRID=4326;Point(70 33)" }

And retrieve all the point inside a given area

GET http://localhost:1337/point?where={"geom":{"intersects": "SRID=4326;Polygon((-50 -10, -50 -50 ,-75 -50, -75 -10, -50 -10))" }}

— Reply to this email directly or view it on GitHub https://github.com/balderdashy/sails-postgresql/issues/43#issuecomment-80651925 .

bwestergard commented 9 years ago

Any movement on this?

kevbaker commented 9 years ago

I've been using the native method on models for now. I actually switched to Mongodb for a few reasons. Not as fast as PostGIS, but does offer many of the key geospatial query features I needed as of version 2.6.

On Thu, Jul 30, 2015 at 12:58 PM, Bjørn Westergard <notifications@github.com

wrote:

Any movement on this?

— Reply to this email directly or view it on GitHub https://github.com/balderdashy/sails-postgresql/issues/43#issuecomment-126460030 .

dbkaplun commented 9 years ago

+1

warpedgeoid commented 9 years ago

+4326

sailsbot commented 8 years ago

Thanks for posting, @jfgodoy. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

Thanks so much for your help!

devinivy commented 8 years ago

Reopening until a PR to the roadmap is made. @tjwebb if I remember correctly, didn't you make some headway on this?

sailsbot commented 8 years ago

Thanks for posting, @jfgodoy. I'm a repo bot-- nice to meet you!

It has been 30 days since there have been any updates or new comments on this page. If this issue has been resolved, feel free to disregard the rest of this message. On the other hand, if you are still waiting on a patch, please:

Thanks so much for your help!

parallaxisjones commented 8 years ago

Any Updates on this?

EDIT: Nevermind. I went back to the postgres adapter feature description and read for myself that there was PostGIS support :dart:

tjwebb commented 8 years ago

The waterline-postgresql adapter has PostGIS support: https://github.com/waterlinejs/postgresql-adapter

parallaxisjones commented 8 years ago

@tjwebb thanyou for the reply! :)