Kysely•2y ago

Unable to insert geometry Postgres

Hey everyone 👋🏻 Greetings! I am trying to insert point data into my Postgresql table but getting error: parse error - invalid geometry error. Here is the code

await this.db.insertInto("location")
            .values({
                name: "Anom",
                coordinates: point({ x: -71.060316, y: 48.432044 })
            })
            .returningAll()
            .execute();

await this.db.insertInto("location")
            .values({
                name: "Anom",
                coordinates: point({ x: -71.060316, y: 48.432044 })
            })
            .returningAll()
            .execute();

and here is the schema:

export interface LocationTable {
    id: Generated<string>;
    name: string;
    coordinates: Point;
}

export interface Point {
    x: number;
    y: number;
}

export function point(pt: Point): RawBuilder<Point> {
    const point = `(${pt.x},${pt.y})`;
    return sql<Point>`${point}`;
}

export interface LocationTable {
    id: Generated<string>;
    name: string;
    coordinates: Point;
}

export interface Point {
    x: number;
    y: number;
}

export function point(pt: Point): RawBuilder<Point> {
    const point = `(${pt.x},${pt.y})`;
    return sql<Point>`${point}`;
}

and here is my database schema:

CREATE TABLE location
(
    "id"          UUID NOT NULL DEFAULT gen_random_uuid(),
    "name"        VARCHAR,
    "coordinates" geometry(Point, 4326)
);

CREATE TABLE location
(
    "id"          UUID NOT NULL DEFAULT gen_random_uuid(),
    "name"        VARCHAR,
    "coordinates" geometry(Point, 4326)
);

i tried solution provided by @koskimas in the https://github.com/kysely-org/kysely/issues/133#issuecomment-1209233409 but no luck. attaching the log generated by kysely


Query: insert into "location" ("name", "coordinates") values ($1, $2) returning * [91.9 ms]
$1: Anom
$2: (-71.060316,48.432044)

Unable to figure out whats wrong. Thanks.

GitHub

Postgres point type support for insert queries · Issue #133 · kysel...

I am using kysely with the PostgresDialect configuration as const db = new Kysely<Database>({ dialect: new PostgresDialect({ pool, }), plugins: [new CamelCasePlugin()], }); I am also using po...

4 Replies

koskimas•2y ago

Does this work

export function point(pt: Point) {
    return sql<Point>`ST_MakePoint(${pt.x}, ${pt.y})`;
}

export function point(pt: Point) {
    return sql<Point>`ST_MakePoint(${pt.x}, ${pt.y})`;
}

or this

export function point(pt: Point) {
    return sql<Point>`ST_SetSRID(ST_MakePoint(${pt.x},${pt.y}), 4326))`;
}

export function point(pt: Point) {
    return sql<Point>`ST_SetSRID(ST_MakePoint(${pt.x},${pt.y}), 4326))`;
}

I'm just googling stuff here

SwapOP•2y ago

Hey @koskimas , thanks for the reply. Both solutions are working as expected and i am able to insert point :). i am using following code to retrieve stored coordinates from the database

await this.db.selectFrom("location")
            .where(sql`ST_DWithin(                                                         coordinates::geography,ST_MakePoint(${lat},${long}),8000)`)
            .select(
                [
                    sql<string>`ST_X(coordinates)`.as("latitude"),
                    sql<string>`ST_Y(coordinates)`.as("longitude"),
                    "name"
                ]
            )
            .execute()

await this.db.selectFrom("location")
            .where(sql`ST_DWithin(                                                         coordinates::geography,ST_MakePoint(${lat},${long}),8000)`)
            .select(
                [
                    sql<string>`ST_X(coordinates)`.as("latitude"),
                    sql<string>`ST_Y(coordinates)`.as("longitude"),
                    "name"
                ]
            )
            .execute()

Above code is working as exepected but, i was wondering is there any other better approach for the above query?

koskimas•2y ago

I don't know if this is better but you could do this https://kyse.link/?p=s&i=Jezrbtdk2cOJeCymdl3b I didn't run the code so there might be some bugs But you get the gist. You can create helpers for everything to clean up the code and make it (relatively) type-safe

SwapOP•2y ago

Thanks @koskimas , it is more typesafe now. I really appreciate your time and efforts to build this wonderful library. 🙂

Gaming

Programming

Unable to insert geometry Postgres