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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...