Drizzle Team•7mo ago

Helpers for querying in the new PostGIS Geometry type

Hi, I was playing around with the new types and I wondered if there are some utility helpers for querying the point. For example:

const stores = pgTable("stores", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),  
  location: geometry("location", { type: 'point', srid: 4326 }),
});

const nearStores = await db
  .select({ id: stores.id, name: stores.name })
  .from(stores)
  .where(ST_DWithin(stores.location, [some_lon, some_lat], 1000))

const stores = pgTable("stores", {
  id: uuid("id").primaryKey().defaultRandom(),
  name: text("name").notNull(),  
  location: geometry("location", { type: 'point', srid: 4326 }),
});

const nearStores = await db
  .select({ id: stores.id, name: stores.name })
  .from(stores)
  .where(ST_DWithin(stores.location, [some_lon, some_lat], 1000))

The SQL should be something like this:

SELECT id, name
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(some_lon, some_lat)::geography, 1000);

SELECT id, name
FROM stores
WHERE ST_DWithin(location, ST_MakePoint(some_lon, some_lat)::geography, 1000);

Thanks 🙂

2 Replies

Blixkreeg•7mo ago

Do these types install postgis extension automatically?

Gary, el Pingüino ArtefactoOP•7mo ago

they don't

Gaming

Programming

Helpers for querying in the new PostGIS Geometry type