SQL support for PostGIS

[rns4731]

PostGIS functions like ST_3DDWithin are not supported right now. Is there any possibility of adding these functions? http://postgis.net/docs/PostGIS_Special_Functions_Index.html

[robcalcroft]

Second

[edudutra]

You can use the literals:

var query = foo
    .select(
    foo.literal('ST_3DDWithin(ST_Transform(ST_GeomFromEWKT(\'SRID=4326;POINT(-72.1235 42.3521 4)\'), 2163), ST_Transform(ST_GeomFromEWKT(\'SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)\'), 2163), 126.8) As within_dist_3d)'
    )
);

This will result into

SELECT ST_3DDWithin(ST_Transform(ST_GeomFromEWKT('SRID=4326;POINT(-72.1235 42.3521 4)'), 2163), ST_Transform(ST_GeomFromEWKT('SRID=4326;LINESTRING(-72.1260 42.45 15, -72.123 42.1546 20)'), 2163), 126.8) As within_dist_3d) FROM "foo"

[robcalcroft]

Ah awesome! That looks like it'll do the trick for the moment, thanks.

[robcalcroft]

How would I use parameterised variables with this method?

[edudutra]

I think you can't. Note this is just a workaround. You should be aware of SQL injections can happen.

[robcalcroft]

I ended up just using pg's built in SQL functionality:

{
    text: "SELECT ST_DISTANCE(lat,long) FROM Table WHERE username=$1",
    values: ["Jim Bloggs"]
}

[vallettea]

i'm also interested. @robcalcroft how did you manage in the end ? my query

            var query = places
                .select(
                    places.literal(
                        {
                            text: "SELECT * FROM places WHERE places.geom && ST_MakeEnvelope($1, $2, $3, $4, 4326)",
                            values: [bbox.minLon, bbox.minLat, bbox.maxLon, bbox.maxLat]
                        }
                    )
                )
                .toQuery();

doesn't work

[vallettea]

I ended using this

            var query = places
                .select("*")
                .from(places)
                .where("places.geom && ST_MakeEnvelope(" + bbox.minLon + ", " + bbox.minLat + ", " + bbox.maxLon + ", " + bbox.maxLat + ", 4326)")
                .limit(100)
                .toQuery();

but it is not awesome (sql injection) Maybe use a parseFloat that would return nan if it is not a number ?

[robcalcroft]

@vallettea this worked for me:

    {
        text: "SELECT \"User\".\"username\", \"User\".\"firstName\", \"User\".\"lastName\", \"User\".\"bio\", ST_Distance(geography(ST_MakePoint(\"User\".\"longitude\",\"User\".\"latitude\")), geography(ST_MakePoint($1,$2))) AS \"distanceFromYou\", \"User\".\"fieldName\" FROM \"User\" WHERE \"User\".\"username\" = $3",
         values: [
            req.user.longitude || null,
            req.user.latitude || null,
            username
        ]
    }

PostgreSQL can be pretty picky about table names etc, hence why I used all the quote marks.

[robcalcroft]

@vallettea Yours is going to evaluate to have two SELECT statements in there, you're using the node-sql module and then a normal SQL query inside it, so there's gonna be duplication. console.log() the result of your produced query and see what it comes out as; see it if makes sense.

[robcalcroft]

@vallettea You also don't need to bother with the .select('*') as .select() does the same thing. Same with the .from(), if its not specified the places table is automatically used.

[vallettea]

thanks @robcalcroft but where does this code goes ? do you do insert it like this in the

var query =   {
        text: "SELECT \"User\".\"username\", \"User\".\"firstName\", \"User\".\"lastName\", \"User\".\"bio\", ST_Distance(geography(ST_MakePoint(\"User\".\"longitude\",\"User\".\"latitude\")), geography(ST_MakePoint($1,$2))) AS \"distanceFromYou\", \"User\".\"fieldName\" FROM \"User\" WHERE \"User\".\"username\" = $3",
         values: [
            req.user.longitude || null,
            req.user.latitude || null,
            username
        ]
    }

db.query(query, function (err, result) {
...
})

?

[robcalcroft]

Yep that's it :)

On 12 Oct 2015, at 15:17, Alexandre Vallette notifications@github.com wrote:

thanks @robcalcroft but where does this code goes ? do you do insert it like this in the

var query = { text: "SELECT \"User\".\"username\", \"User\".\"firstName\", \"User\".\"lastName\", \"User\".\"bio\", ST_Distance(geography(ST_MakePoint(\"User\".\"longitude\",\"User\".\"latitude\")), geography(ST_MakePoint($1,$2))) AS \"distanceFromYou\", \"User\".\"fieldName\" FROM \"User\" WHERE \"User\".\"username\" = $3", values: [ req.user.longitude || null, req.user.latitude || null, username ] }

db.query(query, function (err, result) { ... }) — Reply to this email directly or view it on GitHub.

[bipvanwinkle]

So, I'm not sure if this was intended, but you can use the top-level function functionCallCreator to reference POSTGIS functions.

const SQL = require('sql')
const makePoint = SQL.functionCallCreator('ST_MakePoint')
const setSRID = SQL.functionCallCreator('ST_SetSRID')
Table.update({ pointColumn: setSRID(makePoint(0, 0), 4326) })

text = UPDATE Table SET point_column = ST_SetSRID(ST_MakePoint($1, $2), 4326)
values = [0, 0]

This way you can still used parametrized queries and avoid all the nastiness of SQL injection. Hope this is helpful.

If there is a better way I'd love to know it.