Splunk Search

DBConnect Lookup fails for geospatial query

ibraylovskiy
Splunk Employee
Splunk Employee

I am trying to get the DBConnect lookup working against PostGIS for the following geospatial query:

select CASE WHEN speedlimit=0 THEN '25' WHEN speedlimit=99 THEN '>45' ELSE speedlimit::text END as maxspeed FROM sfdata.speedlimits ORDER BY geom <-> ST_MakePoint($lng$,$lat$) LIMIT 1

I am getting the error "Script for lookup table 'speedlookup' returned error code 1. Results may be incorrect". Any help is appreciated.

1 Solution

ziegfried
Influencer

The ST_MakePoint seems to expect arguments of type double precision.

When the advanced database lookup expects non-string values to be inserted, they needs to be converted. This can be done within DB Connect by adding datatypes to the placeholders:

select CASE WHEN speedlimit=0 THEN '25' WHEN speedlimit=99 THEN '>45' ELSE speedlimit::text END as maxspeed FROM sfdata.speedlimits ORDER BY geom <-> ST_MakePoint($lng:DOUBLE$,$lat:DOUBLE$) LIMIT 1

Another approach is to do the conversion in the SQL (which is probably slightly less efficient):

select CASE WHEN speedlimit=0 THEN '25' WHEN speedlimit=99 THEN '>45' ELSE speedlimit::text END as maxspeed FROM sfdata.speedlimits ORDER BY geom <-> ST_MakePoint(cast($lng$ as double precision),cast($lat$ as double precision)) LIMIT 1

View solution in original post

0 Karma

ziegfried
Influencer

The ST_MakePoint seems to expect arguments of type double precision.

When the advanced database lookup expects non-string values to be inserted, they needs to be converted. This can be done within DB Connect by adding datatypes to the placeholders:

select CASE WHEN speedlimit=0 THEN '25' WHEN speedlimit=99 THEN '>45' ELSE speedlimit::text END as maxspeed FROM sfdata.speedlimits ORDER BY geom <-> ST_MakePoint($lng:DOUBLE$,$lat:DOUBLE$) LIMIT 1

Another approach is to do the conversion in the SQL (which is probably slightly less efficient):

select CASE WHEN speedlimit=0 THEN '25' WHEN speedlimit=99 THEN '>45' ELSE speedlimit::text END as maxspeed FROM sfdata.speedlimits ORDER BY geom <-> ST_MakePoint(cast($lng$ as double precision),cast($lat$ as double precision)) LIMIT 1
0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...