im trying to make a (simple-ish) PostGIS query and the query works via psql and a jdbc-based database UI, but I’m not able to get any data from the query via clojure. I was able to do a simple select * from country table and get back data and create json from it, but switching to my more complicated select statement has resulted in it not working… now I’m doing the format (let [answer (j/query conn "select ... " arg1 arg2) .. ]) but honestly I’m not sure if that’s right, I’m having a hard time finding examples of sql queries that actually use the results instead of querying into the void

@jgh: Have you got a SQL version of a query you want to do? I've done quite a bit of PostGIS from jdbc and it's a pretty direct mapping most of the time

yeah, the query itself works fine

i think i’m just screwing something up in clojure

If you post the query I should be able to translate to clojure jdbc - can DM if you want. If not I can probably find an example that is somewhat relevant

here’s my clojure function:

(defn ^:private get-unit [req lat lon]
  (let [answer (j/query (:db conf)
                  ["select units.id as unit_id, units.name as unit_name, country_division.code as division, country.code as country
                    from units, country, country_division 
                    where units.country_division_id = country_division.id 
                    and country_division.country_id = country.id 
                    and ST_Intersects(ST_SetSRID(ST_MakePoint(-79.389, 43.643), 4269), units.wkb_geometry) 
                    and isValid('units', units.id)"
                  ] {:as-arrays? true})]

    (if-not (nil? answer)
      (print-ans answer)
      (generate-string body-for-404)
    )
  )
)

the coords will be replaced with lat and lon parameters, this is just for trying to get the stupid thing to work

print-ans just prints to console and generates json

Ok give me a second - I've got the same type of query here in one of my projects but it's been a while so I can't remember the name of it 🙂

ah great, i appreciate the help 🙂

(jdbc/query geotrac-db
            [(str "SELECT j.jobtitle, j.meridian, j.range, j.township, j.section,
                 ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint(?,?),4326),3402),ST_Transform(t.geom, 3402)) AS township_distance,
                 ST_Distance(ST_Transform(ST_SetSRID(ST_MakePoint(?,?),4326),3402),ST_Transform(s.geom, 3402)) AS section_distance
                 FROM jobs j
                 LEFT OUTER JOIN twppoly t ON (t.m = j.meridian AND t.rge = j.range AND t.twp = j.township)
                 LEFT OUTER JOIN secpoly s ON (s.m = j.meridian AND s.rge = j.range AND s.twp = j.township AND s.sec = j.section AND s.ra IS NULL AND j.section IS NOT NULL)
                 WHERE j.jobtitle in " jobtitles)
             (utility/convert-to-double driver-longitude) (utility/convert-to-double driver-latitude)
             (utility/convert-to-double driver-longitude) (utility/convert-to-double driver-latitude)])

looks pretty similar superficially

I found it in the very memorable project intranet-1 🙂 I remember there being some issues with passing values in to PostGIS so that I had to do a bit more string concatenation in some areas than I wanted to, but I can't remember exactly what they were.

well it seems pretty sensitive to the numbers, but it at least throws an error in that case

i mean, you cant pass text as a number

@jgh The first thing I would do to start debugging this is to use the REPL and run the j/query expression as is and then remove pieces until you get a non-nil result. For example, remove {:as-arrays? true} and see if that changes the behavior, then remove the last and condition in the where and so on.

(i.e., run it outside the context of your Ring app)

yeah that as-arrays was just something i was trying from stackoverflow, it only enumerated the column headers though.

i wonder if it doesnt like the isValid function since that is doing its own select..

yeah its the isValid function

i took that out and now it works

PostGIS has an ST_IsValid function - do they do the same thing? In their docs it kind of looks like it may be an alias or something.

No, i have valid_to and valid_from fields in my tables to mark versions…isValid just checks that now is between those dates

Ahh - I have no idea how those work in Clojure so I think I'm at the limit of my ability to help 🙂 Glad you found the first problem at least!

maybe that would be better served as a clojure function though

ha yeah me too

Always better in Clojure 🙂

If you find yourself banging your head against a bug/problem, this talk by Stu from Conj a few years back is great advice https://www.youtube.com/watch?v=FihU5JxmnBg

(and his excellent REPL-driven development talk https://vimeo.com/223309989 which provides more insights into leaning on the REPL more and more)