jfgodoy / knex-postgis Goto Github PK

I'm getting an Invalid GeoJSON error when calling st.geomFromGeoJSON on a MultiPolygon.

In a GeoJSON linter, I get the error that the GeoJSON doesn't follow the right-hand rule, although using geojson-rewind to fix this produces no change in result.

I'm stumped here and would appreciate any insight.

Hello,

let query = knex('polygon')
.select('name', 'way_area as area', st.asGeoJSON(st.transform('way', 4326)))
.where({ 'admin_level': '4' }).toString();

wanted to use something like you can see, withou st.transform it is running without any problems. My quick fix on this is, use .toString() method at the end on pipeline and call it directly through pg module.

Don't know where iam doing mistake.

Thank you for reply!

Hello all!
When I try to add an alias to raw sql like this:

fieldsToReturn.push(knex.raw(geo.distance('geo_location',geo.makePointGeography(location.lon,location.lat))).as('distance'));

knex('core.tabletest')
                .select(fieldsToReturn)
            .whereRaw(geo.dwithin('geo_location',geo.makePointGeography(location.lon,location.lat),1000))
                .orderByRaw(geo.distance('geo_location', geo.makePointGeography(location.lon,location.lat)));

I'm getting the error below (in index.js line 115):

TypeError: this.sql.lastIndexOf is not a function
    at Raw.as (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/knex-postgis/lib/index.js:115:34)
    at /Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/business/pick_up_point/pick_up_point_business.js:57:123
    at Generator.next (<anonymous>)
    at Function.findPickUpPointByNearLocation (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/business/pick_up_point/pick_up_point_business.js:64:11)
    at findPickUpPointByNearLocation (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/routes/api/v1/pick_up_point_router.js:34:52)
    at /Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/utils/router/router_util.js:3:9
    at Layer.handle [as handle_request] (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/express/lib/router/layer.js:95:5)
    at next (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/express/lib/router/route.js:137:13)
    at Route.dispatch (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/express/lib/router/route.js:112:3)
    at Layer.handle [as handle_request] (/Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/express/lib/router/layer.js:95:5)
    at /Users/danilodeveloper/Projetos/recebepramim/recebepramim-core-api/node_modules/express/lib/router/index.js:281:22

    "knex": "0.15.2",
    "knex-postgis": "0.7.0",
    "pg": "7.4.3"

Does anyone know how to fix this?
Tks!!

Can't seem to use this library with Typescript cause it looks like the DefinitelyTyped definitions are out of date (don't know if you maintain those or not): https://github.com/DefinitelyTyped/DefinitelyTyped/tree/master/types/knex-postgis

For reference, this is the type error:

Argument of type 'Knex<any, unknown[]>' is not assignable to parameter of type 'Knex'.
  Property 'clone' is missing in type 'Knex<any, unknown[]>' but required in type 'Knex'.

Usage:

const db = knex({ dialect: "postgres" })
const st = knexPostgis(db)

I saw the reply to #38 .

Not really sure how define extra function works though.

Also, was looking through the library to see if it would be difficult to add new functions. Perhaps not now (as I'm rushing my project) but think I could help add more functions in the future!

I am using bookshelf, how could I shoe-horn this in as I have lots of geoms that need parsing.

here is the JSON data defined in js

const testPolygon = {
    "type": "MultiPolygon",
    "coordinates": [
        [
            [

                [
                    116.46163956486414,
                    39.90438934249642,
                ],

                [
                    116.46600979884725,
                    39.90462891295552,
                ],

                [
                    116.47775983186547,
                    39.905188936743684,
                ],
                [
                    116.47763985936191,
                    39.90180905082946,
                ],
                [
                    116.47757967765652,
                    39.89897903611148,
                ],
                [
                    116.47746042634834,
                    39.893389018788504,
                ],
                [
                    116.46142980608141,
                    39.89352933155751,
                ],

                [
                    116.46144980244195,
                    39.89447932762597,
                ],
                [
                    116.4614900725787,
                    39.897139356576616,
                ],
                [
                    116.4615301157297,
                    39.89899934337058,
                ],
                [
                    116.46155014465316,
                    39.89982933418268,
                ],
                [
                    116.46156923335123,
                    39.90071933695599,
                ],
                [
                    116.46158932861321,
                    39.90181933892202,
                ],
                [
                    116.4616094927369,
                    39.90319936330444,
                ],
                [
                    116.46163956486414,
                    39.90438934249642,
                ],

            ],
        ],
    ],
};
console.log(st.geomFromGeoJSON(testPolygon).toString());

it throws Error: Invalid GeoJSON

but if I use the same data directly in Postgres query:

select st_geomfromgeojson('{
  "type": "MultiPolygon",
  "coordinates": [
    [
      [
        [
          116.46163956486414,
          39.90438934249642
        ],
        [
          116.46600979884725,
          39.90462891295552
        ],
        [
          116.47775983186547,
          39.905188936743684
        ],
        [
          116.47763985936191,
          39.90180905082946
        ],
        [
          116.47757967765652,
          39.89897903611148
        ],
        [
          116.47746042634834,
          39.893389018788504
        ],
        [
          116.46142980608141,
          39.89352933155751
        ],
        [
          116.46144980244195,
          39.89447932762597
        ],
        [
          116.4614900725787,
          39.897139356576616
        ],
        [
          116.4615301157297,
          39.89899934337058
        ],
        [
          116.46155014465316,
          39.89982933418268
        ],
        [
          116.46156923335123,
          39.90071933695599
        ],
        [
          116.46158932861321,
          39.90181933892202
        ],
        [
          116.4616094927369,
          39.90319936330444
        ],
        [
          116.46163956486414,
          39.90438934249642
        ]
      ]
    ]
  ]
}')

it works totally fine

Problem

I was unable to compile knex-postgis with TypeScript.

How To Reproduce

I made a repository demonstrating the issue:

https://github.com/aq1018/knex-postgis-build-failure

Fix

I think the knex exports signature has changed in recent versions, and is causing the definition file to be incompatible.

Hello there,
I'm trying to create a polygon to insert into my database using geomFromGeoJSON, but I can't get it to work.

const testGeom = App.St.geomFromGeoJSON({
    "type": "Polygon",
    "coordinates": [
        [
            [
                28.740234375,
                57.468589192089354
            ],
            [
                31.113281249999996,
                57.468589192089354
            ],
            [
                31.113281249999996,
                58.63121664342478
            ],
            [
                28.740234375,
                58.63121664342478
            ],
            [
                28.740234375,
                57.468589192089354
            ]
        ]
    ]
});

I get:

(node:33452) UnhandledPromiseRejectionWarning: Error: Invalid GeoJSON
    at Object.checkGeoJsonGeometry (/home/remy/Travail/path-error-backend/node_modules/knex-postgis/lib/utils.js:30:15)
    at wrapGeoJSON (/home/remy/Travail/path-error-backend/node_modules/knex-postgis/lib/index.js:42:33)
    at Object.geomFromGeoJSON (/home/remy/Travail/path-error-backend/node_modules/knex-postgis/lib/functions.js:104:46)

According to https://geojsonlint.com, the GeoJSON is valid.

What am I doing wrong?
Thanks for your help.

@mapbox/geojsonhint was marked as deprecated, and contains dependencies with vulnerabilities identified via npm audit. See mapbox/geojsonhint#83

Possible replacements:

• https://github.com/placemark/check-geojson (from the original author of @mapbox/geojsonhint)
• https://www.npmjs.com/package/geojson-validation

I am trying to store a polygon to use as a geo fence and then query if a point is within the geo fence. Can you provide an example of how to store this data.

I have the following
table.specificType('polygon', 'geometry(Polygon, 4326)')

What would be the appropriate way to insert the data?

Thanks, sorry for the novice question.

With the newest knex.js, I get the following kind of errors:

insert into "kitchensinks" ("id", "type_boolean", "type_date", "type_datetime", "type_float", "type_integer", "type_json", "type_linestring", "type_point", "type_polygon", "type_string", "type_text", "type_uuid") values ($1, $2, $3, $4, $5, $6, $7, ST_geomFromGeoJSON($8), ST_geomFromGeoJSON($9), ST_geomFromGeoJSON($10), $11, $12, $13) - bind message supplies 10 parameters, but prepared statement "" requires 13

It seems the way raw types are handled introduce some incompatibility.

I wan't to investigate later but will stick with 0.7.x for now.

This issue relates to #35

I'm currently dealing with the same issue, attempting to use Knex-cleaner to truncate before each test:

node: 12.13.1
knex: ^0.21.13
knex-cleaner: ^1.3.1

import knexCleaner from "knex-cleaner";
import knex from ".././config/db-config";



beforeEach(() => {
      const options = {
            mode: "truncate", // Valid options 'truncate', 'delete'
            restartIdentity: true // Used to tell PostgresSQL to reset the ID counter
            // ignoreTables: ["Dont_Del_1", "Dont_Del_2"]
      };
      return knexCleaner.clean(knex, options).then(function () {
            console.log("the database is now clean");
      });
});

Getting the error :

Argument of type 'Knex<any, unknown[]>' is not assignable to parameter of type 'Knex'.
  Property 'clone' is missing in type 'Knex<any, unknown[]>' but required in type 'Knex'.ts(2345)
knex.d.ts(216, 5): 'clone' is declared here.

@jsonmaur @ctusch

knex.raw() uses the pg library for SQL parameter binding. pg adds quotes around number literals causing some queries to fail, see knex/knex#1001. In knex-postgis this behaviour results in the SQL generated for the transform() function quoting the srid parameter. The resultant query fails with the error:

ERROR: transform_geom: couldn't parse proj4 output string: '4326': projection not named

How do you represent a query like this in knex-postgis?

SELECT *
FROM mytable
WHERE mytable.geom && ST_MakeEnvelope(10.9351, 49.3866, 11.201, 49.5138, 4326);

http://postgis.net/docs/manual-2.0/geometry_overlaps.html
I couldn't find && operator anywhere.
st.makeEnvelope(minlon, minlat, maxlon, maxlat, 4326) exists though....

Thanks!

Is it missing?

Hi,

Thank your for your lib, it's very useful when working with Postgis and Knex!

In Typescript, I'd like to be able to use the ExtendedKnexRaw to be able to use it in an interface where I'm storing the result of setSRID

Example:

interface LeadRepositoryAttributes {
  coordinates: ExtendedKnexRaw
}

const leage: LeadRepositoryAttributes = {
  coordinates: setSRID(makePoint())
}

Would you accept a PR that do that, or is there a different way to achieve this?

Hi,

I just noticed that I got the same error for a closed issue regarding quotation around SRID.

poi.geom = st.transform(st.geomFromText(poi.geom, 4326),25832);
return
insert into assets ("asset_type", "geom") values ('test', ST_transform(ST_geomFromText('POINT(11.356373786926271 64.45708785376902)', 4326), 25832))
The returned SQL is fine and run correctly if I copy/paste it to run in pgadmin.
But I am getting an error
New poi error { error: transform_geom: couldn't parse proj4 output string: '25832': projection not named
If I add the quotation marks to the returned sql then I get the same error in postgis, so it looks like it is being parsed to a string at some point?

The version of knex I am using is 0.14.6

Just wondering if it would be possible to implement ST_Z? In order to extract the height coordinate.

Hopefully it's as simple as replicating much of what you've already done for st.x and st.y and creating a st.z function.

(P.s. thanks for a great package)

Hello,

Firstly wanted to say, thank you for this library, its been insanely helpful.

I have a question. I'm trying to bulk insert an array of records into postGIS. with knex I used to be able to do this
await db('alerts').insert(arrayOfData).onConflict('uuid').ignore()

Here is what I tried

        const arrayOfData = filteredAlerts.map((alert) => {
            return {
              lat: alert.location.y,
              long: alert.location.x,
              geom: st.geomFromText((alert.location.y, alert.location.x), 4326)
            }
          })
          await db('alerts').insert(arrayOfData).onConflict('uuid').ignore()

However, does not work.

I'm trying to figure out a way to insert all records into the db while generating a postGIS geom from a given lat and long.

I used to be able to do this using a generated column in postgres however due to our some AWS issues I'm restricted to Postgres 11.x but generated columns were created in 12.

Since installing this package, I am receiving the following warning:

# npm audit report

underscore  1.3.2 - 1.12.0
Severity: high
Arbitrary Code Execution - https://npmjs.com/advisories/1674
fix available via `npm audit fix`
node_modules/underscore
  nomnom  >=1.6.0
  Depends on vulnerable versions of underscore
  node_modules/nomnom

2 high severity vulnerabilities

To address all issues, run:
  npm audit fix

But running "npm audit fix" doesn't actually fix the issues. Not sure how that will affect this library, but figured I'd let the maintainer (if there is still an active one) know.

Hi,

I'm new to postgis, and I'm using it with AdonisJs.
I'm trying the method geomFromGeoJSON and the following :

{ "type": "GeometryCollection", "geometries": [ { "type": "Point", "coordinates": [100.0, 0.0] }, { "type": "LineString", "coordinates": [ [101.0, 0.0], [102.0, 1.0] ] } ] }

taken from there : https://docs.teradata.com/r/Teradata-Database-JSON-Data-Type/June-2017/JSON-Functions-and-Operators/GeomFromGeoJSON/Examples-GeomFromGeoJSON

All the other Geometry examples are working, but with GeometryCollection i'm getting this error :

Unable to find 'geometries' in GeoJSON string

I'm wondering why this example doesnt work ?

Putting the json in another column, and using geomFromGeoJSON(colName) works, but not putting the json directly in geomFromGeoJSON.

I hope this can help

Thanks !

Hi, first off, great work on this project!

I just upgraded to Knex 0.12 and am now receiving the following error:

/app/node_modules/knex-postgis/lib/index.js:12
  formatterProto = knex.client.Formatter.prototype

I use
'geom' : st.setSRID(st.geomFromGeoJSON({"type":"Point","coordinates":[39.915272,116.4792]}),4326)
and the result is

but the geojson is
SELECT ST_AsText(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[-48.23456,20.12345]}')) As wkt;

lost the single quotes。。。。
so can't insert the table...

The current implementation is susceptible to SQL injection. As a trivial example, knex.insert({id: 1, geom: st.geomFromText("Point(')); DROP TABLE points; SELECT concat(concat(')", 4326)}).into('points') will generate the following query:

insert into "points" ("geom", "id") values (ST_geomFromText('Point(')); DROP TABLE points; SELECT concat(concat(')', 4326), '1')

Using bindings as described in the knex docs avoids this, and will mostly negate the need for formatter.wrapWKT() and formatter.wrapGeoJSON().

Hi! I'm the lead maintainer of knex.js project. Would you be interested in integrating code from your plugin upstream so that it would be included in vanilla knex.js?

It's very difficult to understand what the functions in this module actually do. Some of the questions I have right after looking at the readme:

Why is there both a point and makePoint function?
Why wouldn't the point function also take a z input?
What does the transform function do?
What are the return types of these functions?

It would be nice if the list of functions included some basic info. Alternatively, the example could be expanded. I had to find a stack overflow answer just to figure out how to make a point column, and I still haven't figured out how to insert a simple {longitude: 10, latitude: 10} object into my table (my first thought was to use point or makePoint but apparently it's not that simple).

While running I get his error

Knex:warning - global Knex.raw is deprecated, use knex.raw (chain off an initialized knex object)

Thanks in advance!

the docs appear to be pushing us in this direction

const location = st.geomFromText(`POINT(${latitude} ${longitude})`, 4326)

but won't this lead to injection vulns? The signature for the method doesnt appear to have a paramerized version?

I'm creating a geometry column using the following syntax.

select AddGeometryColumn('users', 'coordinates', 4326, 'POINT', 2);

Then in my update method I'm doing something like.

knex('users')
  .where({ id: id })
  .update({
    coordinates: st.geomFromText('Point(0 0)', 4326),
    location_name: 'Origin'
  })

However, I get this error: column "coordinates" does not exist. I've checked the relation and confirmed that the coordinates column does indeed exist. Any idea what might be the issue?