Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Geolocation/Spatial types support #1798

Open
teebot opened this issue Mar 10, 2020 · 57 comments
Open

Geolocation/Spatial types support #1798

teebot opened this issue Mar 10, 2020 · 57 comments
Assignees
Labels
kind/feature A request for a new feature. team/client Issue for team Client. team/psl-wg team/schema Issue for team Schema. topic: geo topic: native database types

Comments

@teebot
Copy link

teebot commented Mar 10, 2020

Currently prisma2 does not support spatial types. This is not exactly an edge case since according to google 9% of sql files on github carry out spatial queries.

I was thinking how that feature could work but it’s probably a bit naive given I don’t know the implementation details of prisma2 :

  • Have a schema annotation @@spatial so that migrate would create a geometry type in postGIS, a geojson field in mongo etc.
  • generate could type this to a geoJSON definition
  • client would translate queries to St_FromGeoJSON in postGIS, leave the geoJSON as is in Mongo

The rationale behind this is that you basically want migrate to create the right spatial type in your db, currently schema/code-first does not work and introspect detects spatial as a string. Yuo also want client to perform CRUD on tables containing spatial types, currently CRUD fails if the spatial type is in the select so a .raw request is the only workaround.

@janpio janpio added kind/feature A request for a new feature. topic: native database types labels Mar 10, 2020
@zifahm
Copy link

zifahm commented Mar 27, 2020

@teebot is there a workaround for this?

@teebot
Copy link
Author

teebot commented Mar 27, 2020

Yes!

  • When you introspect geometry types will be marked as strings in your schema
  • Exclude these types from your crud queries
  • when you need them make a raw query and expose as geojson then json parse the string

@zifahm
Copy link

zifahm commented Mar 27, 2020

I would love to see and example @teebot :)

@pantharshit00 pantharshit00 changed the title Spatial types support Geolocation/Spatial types support Jun 25, 2020
@tctrautman
Copy link

Hi all. First time poster here, coming to Prisma from the Redwood community. Thanks for the work you all are doing -- I've really enjoyed working with Prisma 2 and Prisma migrate so far.

Is there a place I can check to see where geo support falls on the Prisma roadmap or, if not, is that something you could speak to here? (Apologies if I missed this in my initial search.)

@johanmic
Copy link

@tctrautman
cant speak on actual support in the schema annotation but the real issue is the lack of custom types (i.e geography)
hopefully its covered by this #446
which is marked in the next milestone (2.4.0) https://github.com/prisma/prisma/milestone/41

once thats in im hoping that you dont have to fight against migrate that its trying to remove columns thats not in the schema etc. running raw queries is really not an issue for me personally

@tctrautman
Copy link

Thanks @jaywalklabs -- this is really helpful.

@thebiglabasky
Copy link

Hi,
Supporting GIS types is not currently planned. We have indeed started picking up native types as mentioned above here #446 but we first focus on the most common types which don't require specific client-side mapping yet.
The roadmap is available at https://pris.ly/roadmap if you want to have an overview of the topics we're tackling and ideas about our timelines

@bkiac
Copy link

bkiac commented Aug 12, 2020

@thebiglabasky My only real issue with unsupported GIS types is being unable to use the migration tool. Are you planning to / is it feasible to implement a type or function to flag a field as any type so the migration tool can ignore it?

@thebiglabasky
Copy link

@bkiac Interesting, that's good context, thanks.
I don't believe we have ironed out the details at this point, but I will surface that idea to the team.

@Hebilicious
Copy link

Hebilicious commented Aug 22, 2020

I believe supporting GIS types is extremely important, especially given the fact that all the databases that prisma support/plan to support has a way to work with GIS :

@thebiglabasky While I understand that prioritising things can be hard, what I would really like to see in the meantime is improvements when it comes to unhandled cases like this one.
I really like @bkiac idea and it would be nice to take it further by allowing "manual" migrations where custom logic can be written.
Consider a greenfield project that wants to use postgres+postgis with node. In the official example, there's a prisma/posgis/express example repository. In this case, having the ability to use prisma migrate instead of manually running sql scripts + prisma introspect would make prisma way more valuable imo.
It would be neat if we had the ability to generate custom migrations (with a Laravel-like syntax) :

//CustomMigration.ts
export const up = async () => {
//Raw SQL logic that creates tables/columns/install extensions etc
}
export const down = async () => {
//Raw SQL logic that tear down things created by "up"
}

This would generate a migration folder with a README.md, schema.prisma and steps.json, and would add the migration to the lockfile.

In the data-model the created columns with unsupported types would simply appear with a special annotation/flag.

While this is a little bit rough, at least it would allow the handling of a lot of edge cases and make prisma migrate a much more versatile tool. This would make prisma + prisma migrate a much better standalone tool (no need to manually run sql scripts/introspect anymore).

Having first class geometry/postGIS support would be much better in this example, but implementing something like this in the meantime seems much easier, and would be quite valuable (for example it gives you a way to add postgres functions using a similar manual migration approach).

@thebiglabasky
Copy link

Thanks for these details!
We are currently rethinking migrate to eventually push it out of the experimental state. At this time, the leading idea is to allow to customize a generated migration. This could serve one aspect of the problem. @albertoperdomo is focusing on migrate with the team and they're looking at how native types will be handled there so there will definitely progress in the right direction in the coming weeks/month.

@jebenois
Copy link

+1

1 similar comment
@theswerd
Copy link

+1

@albertoperdomo albertoperdomo added the team/schema Issue for team Schema. label Feb 3, 2021
@gilneto8
Copy link

gilneto8 commented Feb 5, 2021

Will this be rolling out soon?

@Jolg42
Copy link
Member

Jolg42 commented Feb 8, 2021

You can now try 2.16.0

@@ignore & Unsupported makes it possible to use PostGIS for example, as $raw queries, and Migrate will not drop these columns.

Release Notes:
https://github.com/prisma/prisma/releases/tag/2.16.0

Also check:
#4531 (comment)

Daniel created a PostGIS example using $raw queries with the client and using latest Migrate Preview features:
https://github.com/2color/interest-points/blob/master/prisma/schema.prisma

Truncated example:

model berlin_pois {
  gid    Int                      @id @default(autoincrement())
  osm_id String?                  @db.VarChar(10)
  code   Int?                     @db.SmallInt
  fclass String?                  @db.VarChar(28)
  name   String?                  @db.VarChar(100)
  geom   Unsupported("geometry")?
  @@index([geom], name: "berlin_poi_idx")
  @@map("berlin-pois")
  @@ignore
}

model spatial_ref_sys {
  srid      Int     @id
  auth_name String? @db.VarChar(256)
  auth_srid Int?
  srtext    String? @db.VarChar(2048)
  proj4text String? @db.VarChar(2048)
  @@ignore
}

We are looking for feedback and are planning to release Prisma Migrate out of Preview soon 😄

@benjamintd
Copy link

benjamintd commented Feb 10, 2021

Hi! Thanks for the example using the unsupported types.

Is it possible to use a middleware to handle these unsupported types?

A common use case would be:

  • on create/update, all geometry types can be parametrized as geojson, and the middleware would convert them using ST_GeomFromGeoJSON
  • on read, all geometry types are read as geojson using ST_AsGeoJSON.

Can this be done with the current middleware API, or do we have to resort to raw queries ?

@Jolg42
Copy link
Member

Jolg42 commented Feb 10, 2021

Hi @benjamintd

So you'll need to use raw queries since Unsupported fields are not surfaced in the client at all.

@baba43
Copy link

baba43 commented Feb 22, 2021

I have a POINT field in my MySQL db that gets annotated by Unsupported("point").

Is there any simple way or workaround that would allow prisma to at least fetch this field in regular queries?

@Jolg42
Copy link
Member

Jolg42 commented Feb 23, 2021

@baba43 you'll need to use the $raw queries, someone opened a related feature request #5683

@masihjahangiri
Copy link

May you attention to our request, please.

@oyatek
Copy link

oyatek commented Dec 27, 2021

we need this too!

@MurzNN
Copy link

MurzNN commented Jan 27, 2022

For what it's worth I ended up going with separate lat and long fields in the end. Reason for this was that the ll_to_earth function takes those 2 arguments so the case to try and make a point column type work evaporated.

Do you store them as decimal numbers, or as string? And is there any technique available to filter points by coordinates+radius, using that storage type?

Also for storing simple GPS points - there is a separate issue #5984 - so maybe implementing that feature will be the good first step forward to this issue?

@aeddie-zapidhire
Copy link

aeddie-zapidhire commented Jan 27, 2022

@MurzNN

Stored as decimals. Migration file will look something like this:

CREATE EXTENSION IF NOT EXISTS "cube";
CREATE EXTENSION IF NOT EXISTS "earthdistance";

-- ...

CREATE TABLE "thing" (
-- ...
    "latitude" DOUBLE PRECISION NOT NULL,
    "longitude" DOUBLE PRECISION NOT NULL,
-- ...
);

Then in my query I have to do some funky stuff.

// This is a hack to force the Prisma to see the lat and long as real numbers and not whole numbers.
const FIX = 0.9999999999

// ...

// Getting the lat and long from the REST query params
const { lat, long } = params

// Running a raw query
const results = await this.db.$queryRaw<any[]>`
SELECT *, earth_distance(
  ll_to_earth(${+lat * FIX}, ${+long * FIX}),
  ll_to_earth(s.latitude, s.longitude)
) AS distance
FROM thing
ORDER BY distance ASC
`

So the lat and long are coming from the API query parameters, for example GET /things?lat=1&long=1.

I had to add the FIX because there seems to be a bit of a bug in Prisma when is sees a whole number in a template literal. It doesn't encode it properly so I had to fudge it so that the template literal was always a decimal. It's a dirty workaround but couldn't work around it any other way.

Not sure if that helps your use case, but that will give me, for example, a list of "things" in order of proximity to my current location.

@lucysutton91
Copy link

+1

@tmcw
Copy link

tmcw commented May 26, 2022

I don't know how much this would affect the folks in this thread, but #10634 did introduce the Gist index to Prisma. In my application, that was enough to work with spatial data pretty comfortably - Prisma's migrations now work fine with custom geometry columns and geo indexes, I do custom sql when i need to use geo queries, etc.

@freddydumont
Copy link

@tmcw Sorry to ping you but I'm wondering what you schema looks like to use a geometry column with Prisma? I understand you still need to write raw SQL queries. I don't quite understand what the Gist index brings though.

@tmcw
Copy link

tmcw commented Jun 6, 2022

Here's an example snippet:

model Blockgroup {
  id        String                                      @id
  geom      Unsupported("geometry(MultiPolygon, 4326)")
  water                                     Float
  Area Area[]
  @@index([geom], name: "blockgroup_idx", type: Gist)
}

PostGIS is more or less three components - a column type called geometry, an index type that works with that column type (in this example, a Gist index), and geometry functions.

If you use the geometry column but don't use the Gist index, the other index types will complain about being too large and they won't perform well. To do geo stuff like queries using intersection, you basically need to use the Gist index.

@freddydumont
Copy link

Thanks @tmcw that's really helpful.

@floelhoeffel
Copy link

Hello everyone 👋

We are kicking off design of GIS support for Prisma!

If you would like to help, please let us learn about your GIS needs!

Via survey:
Please fill out our GIS User Research Survey

Via email:
Alternatively, just shoot us an an email to feedback@prisma.io.
The questions we have are (all in the context of GIS):

  • what is your GIS use case?
  • what database do you use?
  • what column types do you use?
  • what queries do you use?
  • what indexes do you use?
  • what kind of defaults do you use?

Thank you!

@DanielSmith
Copy link

DanielSmith commented Sep 9, 2022

[just to note: I was asked to leave a comment here by Nick: https://twitter.com/nikolasburk/status/1568143931199692800?s=20&t=EjAdvQpt2amoLTxIeVxIXQ ]

It's September 2022. I wanted to try Prisma, but not being able to search MongoDB by proximity means that I will stick with Mongoose for now. A snippet from my Node.js, where I am building up a query (and note: this was working fine in Mongoose in 2017):

  retVal = {
    "$near": {
      "$geometry": {
        "type": "Point",
        "coordinates": [ longitudeFloat, latitudeFloat ]                                
      },
      "$maxDistance": 2000
    }
  };

Mapping apps often need to search by proximity. Prisma needs to support this.

@murbanowicz
Copy link

@floelhoeffel Hi! Do you have any update on this? I am right on the edge of choosing an ORM for my next project where I need a lot of GIS stuff. Could you share anything?

@AdsonCicilioti
Copy link

Is there a plan to map it to a specific Release?

Working with GeoLocation data has been increasingly common and powerful nowadays.

@floelhoeffel
Copy link

@murbanowicz we are wrapping up discovery as we speak. We hope to pickup implementation in Q4 but can only tell after planning (which happens early December). Thank you for your patience 🙏

@DanielSmith
Copy link

It's amazing that this is taking so long (over 2.5 years...) Prisma is not someone's hobby project - it's a company with over $50M in financing. And..and.. this is very much a solved problem (Mongoose for > 5 years now, and others). Kick it up a notch.

@Oreilles
Copy link

Oreilles commented Dec 15, 2022

@floelhoeffel I'm planning to use Prisma for my current work projects, and support for geospatial data types will be a requirement, I'd love to get involved in the implementation process - I think I have a pretty good knowledge of the issues you might encounter, as my previous work at Directus specifically involved building an GeoJSON IO layer compatible with Postgres, MySQL, Oracle, SQLite and SQL Server, and supporting all specific geometric types ((multi) points, lines, polygons). Please get in touch if you're interested in getting me involved.

@janpio
Copy link
Member

janpio commented Dec 15, 2022

That sounds wonderful. How can we contact you? You are hard to google 😆 Feel free to send an email to jan@prisma.io and I will forward to @floelhoeffel.

@Oreilles
Copy link

Thanks Jan, I sent you an email 👍

@dylel
Copy link

dylel commented Jan 11, 2023

I see that on the roadmap this has moved from work in progress back to planned (after being WIP for about 2months). Does this mean that we won't be getting this anytime soon? I think a lot of us were hopeful that this would be in the December or January release. Can we get a rough eta, like will this be soon or months?

@janpio
Copy link
Member

janpio commented Jan 11, 2023

If you define "soon" as "less than a month or two months", then yes we will not get this done in that timeframe. With the complexity we uncovered, we unfortunately needed to revisit our prioritization and reflected that in our roadmap. We still want to and will build this - but not in the timeframe we initially hoped.

@MurzNN
Copy link

MurzNN commented Jan 11, 2023

@janpio Could you please consider the implementation of Point data type (described in #5984) as a first step? Seems it is not so hard to implement, as full Spatial features.

@janpio
Copy link
Member

janpio commented Jan 11, 2023

Leave a comment there in the issue how you would imagine this would work, what new types and API methods would be useful or required for that standalone. Thanks.

@Oreilles
Copy link

Hi @janpio, I didn't get an answer to the email I sent. Did you (or me) not receive it, or are you not interested anymore in collaborating on this specific issue ?

@dylel
Copy link

dylel commented Jan 11, 2023

@janpio I appreciate the update, as you still seem to be in the api design phase I would think looking at something like entity framework from c# would be useful as they have the best implementation/support I've seen for spatial queries and operations and is currently what I use.

@LeulAria
Copy link

LeulAria commented Mar 1, 2023

Do we have support for this yet or should i use another ORM

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
kind/feature A request for a new feature. team/client Issue for team Client. team/psl-wg team/schema Issue for team Schema. topic: geo topic: native database types
Projects
None yet
Development

No branches or pull requests