SQL Spatial Filter error with automatically adding quotes

[StephenMack]

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.

[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?

[StephenMack]

ok even stranger revelation. Alteryx isn't putting in single quotes at all, they're whatever is also on the tilda key left of #1.

' = single quote

` = what alteryx is putting in my query

if I replace those with single quotes the query runs fine

[StephenMack · Accepted solution]

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

[CharlieS]

The fields not populating is normal. It's an initalization/auto-configure/refresh thing that's a different discussion. I type them in every time to get things started.

I haven't worked in a MySQL environment, but is it necessary to specify the table with each field? Could the query be written like this?

Select

latitude

,longitude

,data_time

from hb_logs_bin1000

Alternatively, if you want to write your own replacement string before the tool, here's an example formula that could be written in a Formula tool before the Dynamic Input:

"WHERE LONGITUDE>="+tostring(ST_MinX([SpatialObj]))+" AND LONGITUDE<="+tostring(ST_MaxX([SpatialObj]))+" AND LATITUDE>="+tostring(ST_MinY([SpatialObj]))+" AND LATITUDE<="+tostring(ST_MaxY([SpatialObj]))