How to use Geography functions

[bkanuka]

Postgres (via PostGIS) and BigQuery have a number of built in functions around geometry. Most starting with ST_. For example https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions and https://postgis.net/docs/manual-1.5/ch08.html

It's unclear if/how I can use these functions in PRQL. I likely need to define them as functions with s-strings, but again, I'm not sure exactly what that would look like.

[max-sixty]

For a one-off, it's easy to use an s-string for this:

from foo
select [my_lon, my_lat]  # assuming these columns exist
derive point = s"ST_GEOGPOINT({my_lon}, {my_lat})"

It's also possible to make a function (but at the moment it needs to be in every file, we don't have importing yet)

func geogpoint lon lat -> s"ST_GEOGPOINT({lon}, {lat})"

from foo
derive point = (geogpoint my_lon my_lat)

Both of these generate

SELECT
  my_lon,
  my_lat,
  ST_GEOGPOINT(my_lon, my_lat) AS point
FROM
  foo

---

For adding these to a standard library — very open to that, though maybe we want a separate namespace for them. Someone would have to think about which functions to add initially and how those would correspond to various dialects' current implementations.

[bkanuka]

This is extremely helpful. Thank you!

Unfortunate about the imports, but I suspect that's on the roadmap 😉

Personally I'm only familiar with Postgres and BigQuery implementations and roughly speaking, BQ supports a subset of the most common ST_ functions from Postgres. I believe MySQL also supports geometry functions but I'm not familiar with them.

What would be the best way of contributing? Would it be helpful to define the ST_ functions as PRQL functions?

I agree they should be namespaced.

[max-sixty]

Thanks for the offer to contribute!

I'm considering working on a module system soon; maybe this could be the first module...

I wouldn't want to suggest doing anything until we're confident it can have impact. So maybe watch out for the module system's arrival before writing code...

If you want to have a think about what the functions should be like, and post an issue for comments, that could be productive. For example, I'm guessing we can dispense with the ST_ prefix given we'll have them in a namespace.

[bkanuka]

I was thinking the module could be st ;)

For now I'll continue to try to use PRQL for other non-geometry things to get more fluent.