Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for units #203

Closed
russss opened this issue Apr 12, 2018 · 10 comments
Closed

Support for units #203

russss opened this issue Apr 12, 2018 · 10 comments

Comments

@russss
Copy link
Contributor

russss commented Apr 12, 2018

It would be nice to be able to attach a unit to a column in the metadata, and have it rendered with that unit (and SI prefix) when it's displayed.

It would also be nice to support entering the prefixes in variables when querying.

With my radio licensing app I've put all frequencies in Hz. It's easy enough to special-case the row rendering to add the SI prefixes, but it's pretty unusable when querying by that field.

@simonw
Copy link
Owner

simonw commented Apr 12, 2018

I like this. I'd like to be able to attach a full description to a column as well. We could support these in metadata.json

@simonw
Copy link
Owner

simonw commented Apr 12, 2018

This also feeds into the visualization features I want to add - we could use this kind of metadata to automatically apply meaningful labels to graphs.

@russss
Copy link
Contributor Author

russss commented Apr 12, 2018

Looks like pint is pretty good at this.

In [1]: import pint

In [2]: ureg = pint.UnitRegistry()

In [3]: q = 3e6 * ureg('Hz')

In [4]: '{:~P}'.format(q.to_compact())
Out[4]: '3.0 MHz'

In [5]: q = 0.3 * ureg('m')

In [5]: '{:~P}'.format(q.to_compact())
Out[5]: '300.0 mm'

In [6]: q = 5 * ureg('')

In [7]: '{:~P}'.format(q.to_compact())
Out[7]: '5'

russss added a commit to russss/datasette that referenced this issue Apr 13, 2018
Add support for specifying units for a column in metadata.json and
rendering them on display using
[pint](https://pint.readthedocs.io/en/latest/).

ref simonw#203
simonw pushed a commit that referenced this issue Apr 14, 2018
Add support for specifying units for a column in metadata.json and
rendering them on display using
[pint](https://pint.readthedocs.io/en/latest/).

ref #203
@simonw
Copy link
Owner

simonw commented Apr 14, 2018

This is really cool - I'm very impressed by pint.

I'd like to figure out a sensible opt-in way to expose this in the JSON output as well. Maybe with a &_units=true parameter?

We should definitely expose the units section from the table metadata in the output of https://wtr-api.herokuapp.com/wtr-663ea99/license_frequency.json

@simonw
Copy link
Owner

simonw commented Apr 14, 2018

In #204 you said "I'd like to add support for using units when querying but this is PR is pretty usable as-is." - I'm fascinated to hear more about how this could work.

@russss
Copy link
Contributor Author

russss commented Apr 14, 2018

I'd like to figure out a sensible opt-in way to expose this in the JSON output as well. Maybe with a &_units=true parameter?

From a machine-readable perspective I'm not sure why it would be useful to decorate the values with units. Edit: Should have had some coffee first. It's clearly useful for stuff like map rendering!

I agree that the unit metadata should definitely be exposed in the JSON.

In #204 you said "I'd like to add support for using units when querying but this is PR is pretty usable as-is." - I'm fascinated to hear more about how this could work.

I'm thinking about a couple of approaches here. I think the simplest one is: if the column has a unit attached, optionally accept units in query fields:

column_units = ureg("Hz") #  Create a unit object for the column's unit
query_variable = ureg("4 GHz") # Supplied query variable

# Now we can convert the query units into column units before querying
supplied_value.to(column_units).magnitude
> 4000000000.0

# If the user doesn't supply units, pint just returns the plain
# number and we can query as usual assuming it's the base unit
query_variable = ureg("50")
query_variable
> 50

isinstance(query_variable, numbers.Number)
> True

This also lets us do some nice unit conversion on querying:

column_units = ureg("m")
query_variable = ureg("50 ft")

supplied_value.to(column_units)
> <Quantity(15.239999999999998, 'meter')>

The alternative would be to provide a dropdown of units next to the query field (so a "Hz" field would give you "kHz", "MHz", "GHz"). Although this would be clearer to the user, it isn't so easy - we'd need to know more about the context of the field to give you sensible SI prefixes (I'm not so interested in nanoHertz, for example).

You also lose the bonus of being able to convert - although pint will happily show you all the compatible units, it again suffers from a lack of context:

ureg("m").compatible_units()
> frozenset({<Unit('angstrom')>,
           <Unit('thou')>,
           <Unit('inch')>,
           <Unit('link')>,
           <Unit('foot')>,
           <Unit('survey_foot')>,
           <Unit('yard')>,
           <Unit('rod')>,
           <Unit('mile')>,
           <Unit('survey_mile')>,
           <Unit('league')>,
           <Unit('light_year')>})

@simonw
Copy link
Owner

simonw commented Apr 14, 2018

Presumably units only work for numeric fields? If that's the case then automatically processing them if the incoming query string argument has a unit suffix makes total sense to me.

Here's a pretty crazy idea: what if we exposed unit conversion to SQL as a custom SQLite function? That way it would be possible to optionally use units in actual custom SQL queries.

I'd have to think quite carefully about performance implications here - wouldn't want a poorly considered unit calculation over a 500,000 row table to lock up the server. But I think the 1s query time limit might still prevent that.

simonw added a commit that referenced this issue Apr 14, 2018
@simonw
Copy link
Owner

simonw commented Apr 14, 2018

I think I'm going to hold on to the custom sql function idea for the moment and implement it as an example plugin.

@simonw
Copy link
Owner

simonw commented Apr 15, 2018

I built a prototype of the convert_units() custom SQL function as a plugin over in #14 (comment)

@russss
Copy link
Contributor Author

russss commented Apr 16, 2018

Ah, I had no idea you could bind python functions into sqlite!

I think the primary purpose of this issue has been served now - I'm going to close this and create a new issue for the only bit of this that hasn't been touched yet, which is (optionally) exposing units in the JSON API.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants