Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Using SQL to filter tile results #6

Closed
eightysteele opened this Issue · 4 comments

1 participant

@eightysteele

So I added the following line to my Windshaft config.req2params object:

req.params.sql = "SELECT * FROM ranges where binomial = 'Puma yagouaroundi'";

When I tried to access a tile like http://localhost:4000/database/mol/table/ranges/3/2/4.png, I got the following error:

{
stack: "Error: :
ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT * FROM SELECT * FROM ranges where binomial = 'Puma ya...
                      ^
Full sql was: 'SELECT * FROM SELECT * FROM ranges where binomial = 'Puma yagouaroundi' LIMIT 0'
 (encountered during parsing of layer 'ranges' in map '/home/eighty/Dropbox/projects/')"
message: ":
ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT * FROM SELECT * FROM ranges where binomial = 'Puma ya...
                      ^
Full sql was: 'SELECT * FROM SELECT * FROM ranges where binomial = 'Puma yagouaroundi' LIMIT 0'
 (encountered during parsing of layer 'ranges' in map '/home/eighty/Dropbox/projects/')"
}

It looks like Windshaft is just expecting a nested query here? Easy workaround, I had to change the config.req2params object to this:

req.params.sql = "(SELECT * FROM ranges where binomial = 'Puma yagouaroundi') as q";

Then we get our tile!

Also, does it makes sense to support an SQL parameter for tiles? Something like:

/3/2/4.png?sql=(SELECT * FROM ranges where binomial = 'Puma yagouaroundi') as q,

@eightysteele

Huh, actually, I see in /lib/windshaft/server.js that SQL request params are supported, but req.query is empty for some reason. Looking into it now...

@eightysteele

Ok, ok, I think I found the problem with SQL request parameters not working. Check out line 125 in server.js:

https://github.com/Vizzuality/Windshaft/blob/master/lib/windshaft/server.js#L125

Here's a snippet:

req.query.sql = "(" + req.query.sql + ") as cdbq"

Basically the problem is that it's setting the sql on req.query instead of req.params. If you change that line to:

req.params.sql = "(" + req.query.sql + ") as cdbq"

Boom! I'm now happily filtering tiles based on custom SQL parameters. :)

@eightysteele eightysteele referenced this issue from a commit
Commit has since been removed from the repository and is no longer available.
@eightysteele eightysteele referenced this issue from a commit in eightysteele/Windshaft
@eightysteele eightysteele Patch issue #6.
In `server.js`, the SQL request parameter was getting set on `req.query.sql`
instead of on `req.params.sql`. This resulted in SQL request parameters getting
silently ingored.
a1aeba0
@eightysteele

Submitted a pull request for this change:

#7

@eightysteele

Ug, I'm really sorry, please completely ignore this entire issue. I was using an earlier revision of Windshaft. In HEAD, everything is working as it should. Closing issue. :|

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.