SQL Spatial Filter error with automatically adding quotes

I'm attempting to pull data from a database and filter within a polygon. Obviously I can pull it ALL down and filter within Alteryx, but that's a pretty big waste of resources. Using a dynamic input I'm trying to use the 'SQL: Spatial Filter'.

I've made a generic polygon and feed it into the dynamic input. It errors because it says the query is wrong (no columns returned). It looks like it's wrong because when it tacks on a WHERE clause for the polygon it puts the column headers in single quotes. If I copy that line from the error and remove the single quotes the query works fine in my database management software.

The problem is I don't see anything in the settings to leave off these single quotes in the WHERE clause. I changed the setting "Table/FieldName SQL Style" from "Quoted" to "None" but that hasn't fixed the error.

Input

Dynamic Processing

Spatial Analysis

Accepted answers

StephenMack

I found the stupid solution.

So when I enter my initial query in the dyanmic input 'Input Data Source Template' I apparently have to hit the 'Refresh' button so that it pulls down at least some example data. Once that example data is there THEN I have latitude and longitude fields appear in the SQL Spatial Filter dropdowns. I guess since I was typing them in it treated them as some form of text and decided that adding the ` was necessary for some reason (even though it would have worked just fine if it added nothing).

Thankfully that's done and I never have to do that again! Hope this thread is helpful to someone in the future

All comments

CharlieS

Well you're on the right track by taking advantage of the Spatial Filter filter on the Dynamic Input tool. As someone who primarily works in spatial analytics, it's an amazing feature that I get plenty of use from.

The Dynamic Input Spatial Filter will use quoted strings by default (as you can see in the query reported in the Results Window), and I believe the drop down setting doesn't apply to that string as you mentioned.

Off the top of my head, there are a few things to double check:

- Are any alias being used in the query? For example, are you joining tables and specifying A.LONGITUDE and A.LATITUDE in the query before the filter?

- Are the latitude/longitude fields in your database lowercase? Since the Spatial Filter function uses quoted strings, they will be case-sensitive.

if these items don't help, I think sharing the rest of the query (if possible) would be a helpful next step. Otherwise a more complex work around would be to not use the Spatial Filter modification and build your own latitude/longitude range specification before the Dynamic Input to perform a string replacement with.

StephenMack

I'm not using any aliases in the query, no names are altered using 'as'.

the cases are all correct, it just seems that MySQL doesn't like having the column names in single quotes.

This is my error:

Error: Dynamic Input (2): Error opening "Select hb_logs_bin1000.longitude, hb_logs_bin1000.latitude, hb_logs_bin1000.data_time From hb_logs_bin1000 WHERE `hb_logs_bin1000.longitude`>=-97.558594 AND `hb_logs_bin1000.longitude`<=-97.020264 AND `hb_logs_bin1000.latitude`>=32.472695 AND `hb_logs_bin1000.latitude`<=32.992539": No Columns Returned.

But when I copy the query from the error, and remove the single quotes it works in MySQL management studio.

Select hb_logs_bin1000.longitude, hb_logs_bin1000.latitude, hb_logs_bin1000.data_time From hb_logs_bin1000 WHERE hb_logs_bin1000.longitude>=-97.558594 AND hb_logs_bin1000.longitude<=-97.020264 AND hb_logs_bin1000.latitude>=32.472695 AND hb_logs_bin1000.latitude<=32.992539

If I could have the dynamic input add no quotes at all I'd be in business

StephenMack

Also worth noting: when I edit the SQL Spatial Filter, no latitude or longitude fields show up in the drop down boxes, I had to fill them in myself. Maybe that's a hint at something?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703