select Id, DistanceBetween(s.FromPoint(-73.935242, 40.730610)) as Distance, s.IsBetween(s.FromPoint(-73.935242, 40.730610)) as IsInArea from #gis.shapefile('map.shp') s

You could even combine it with other data sources. For example, if you have geometry data in a CSV:

select sfg.DistanceBetween(sfg.FromPoint(..., ...)) from #csv.file(...) c cross apply #gis.ShapeFromGeometry(c.Geometry) sfg

This will output pure json or csv. This way you can use other tools like jq, grep, csvtoolkit or whatever you need further process your data.

to dig deeper, just look at: https://github.com/Puchaczov/Musoq.CLI

After some thought: you could have also asked whether it's possible to add new data sources that you need to query, and the answer is of course yes! It's actually quite simple and there are many examples. Each data source tool is just a plugin implementing the appropriate interfaces. You can look at some example projects and see how they implement their data source here: https://github.com/Puchaczov/Musoq.DataSources

It allows you to query various things from niche ones like CAN DBC files, weird ones like C# code, interesting ones with Git querying to regular stuff like CSV, TSV and various others.

I am quite a bit experimenting with various things so I'm hybridizing the engine with LLMs or doing other weird stuff that are more or less practical :-)

I wanted also to share some recent developments in this little project as I hope it might be interesting to some of you.

New Experimental Plugins: * Git Plugin (Beta): I've been working on Git repository querying - managed to test it on the EF Core repo (16k commits) and it seems to work okay * Roslyn Plugin (Beta): Added basic C# code analysis capabilities

For the very first time: I've extended CROSS APPLY to use computed results as arguments! Now the operator can use values from the current row as inputs. Here's an example:

  SELECT
    f.DirectoryName,
    f.FileName
  FROM #os.directories('/some/path', false) d
  CROSS APPLY #os.files(d.FullName, true) f
  WHERE d.Name IN ('Folder1', 'Folder2')

  with ProjectsToAnalyze as (
    select
        dir2.FullName as FullName
    from #os.directories('D:\repos', false) dir1
    cross apply #os.directories(dir1.FullName, false) dir2
    where
        dir2.Name = '.git'
  )
  select
    c.Message,
    c.Author,
    c.CommittedWhen
  from ProjectsToAnalyze p cross apply #git.repository(p.FullName) r 
  cross apply r.Commits c
  where c.AuthorEmail = 'my-email@email.ok'
  order by c.CommittedWhen desc

- Moved to .NET 8

- Added CROSS/OUTER APPLY operators

- Made some improvements to error messages and runtime behavior

New piping features: I've been experimenting with piping capabilities: * Image Analysis with LLMs:

  ./Musoq.exe image encode "image.jpg" | ./Musoq.exe run query "select s.Shop, s.ProductName, s.Price from ..."

  Get-Content "ticket.txt" | ./Musoq.exe run query "select t.TicketNumber, t.CustomerName ... from #stdin.text('Ollama', 'llama3.1') t"

  { docker image ls; ./Musoq.exe separator; docker container ls } | ./Musoq.exe run query "..."

Other Changes:

- Made some improvements to OS and Archive data sources (OS can now query metadata like EXIF)

- Added a few fields to CAN DBC plugin

- Command outputs can now be used as inputs for queries

I'm hoping to:

- Improve stability and add more tests

- Flesh out the documentation

- Work on package distribution (Scoop, Ubuntu packages)

- Share some examples of source code querying with Roslyn

Ideas for later:

- WHERE robust analysis and optimizations

- DISTINCT operator implementation

- PROTOBUF schema support

- Performance improvements

- Query parallelization

- Recursive CTEs

- Subqueries

I'd really appreciate any thoughts or feedback!

The documentation section where I write a short analysis of EF Core with git plugin: <https://puchaczov.github.io/Musoq/practical-examples-and-app...>

Comments URL: https://news.ycombinator.com/item?id=42453650

Points: 56

# Comments: 15

Comments URL: https://news.ycombinator.com/item?id=39351418

Points: 1

# Comments: 0

Comments URL: https://news.ycombinator.com/item?id=24626272

Points: 2

# Comments: 0

Comments URL: https://news.ycombinator.com/item?id=21652021

Points: 1

# Comments: 0

Comments URL: https://news.ycombinator.com/item?id=21275236

Points: 1

# Comments: 0

Mostly, I'm using it for ad-hoc querying as it allows for semi-automatic columns detection. For example, my bank account file I am querying shows how I'm spending my moneys. It looks like below and I have few more advanced queries doing some different calculations on my CSV file.

select ExtractFromDate(DataOperacji, 'month') as 'Month', ExtractFromDate(DataOperacji, 'year') as 'Year', SumIncome(ToDecimal(Kwota, 'pl-PL')) as 'Income', SumOutcome(ToDecimal(Kwota, 'pl-PL')) as 'Outcome', SumIncome(ToDecimal(Kwota, 'pl-PL')) - Abs(SumOutcome(ToDecimal(Kwota, 'pl-PL'))) as 'Total' from #csv.file('C:\some\path\my_account.csv', ';', true, 37) group by ExtractFromDate(DataOperacji, 'month'), ExtractFromDate(DataOperacji, 'year')

from another point of usage, I'm sometimes using it to find files in folders as I can both query file properties and their content. Also, I was testing it with querying git repository (I was using https://github.com/libgit2/libgit2sharp), If you are curious about some statistics, then probably it would be usable for you.

Another thing I would imagine is that when you have such a huge compressed file that you can't unpack, then you could query such a file and unpack only what you needs. It doesn't have to be a packed file only. It can be such a big log file that most of editors wouldn't be able to open.

Another thing, If you are an administrator and have to move buch of files or create yourself a view how the files looks in more aggregated way, then it will be probably good for you (I'm not an administrator and never was so please take into consideration that my point of view may be clearly wrong)

Different thing, If you've got a bunch of IOT devices, you would end up writing plugin that gets data from such devices and provides you rich statistics about various things.

Another possibility, you need event (for scheduling purposes) that fits some sophisticated time pattern, you can't use CRON becouse it's syntax has some serious limitations, probably it will be better to query time for such a pattern and store it somewhere. Otherwise you would need writing a lot of single CRON expressions that all fits your requirements.

I will go right now into some crazy imaginations like:

You would end up writing a plugin that extract some statistics from photos (like is the image contains people, how many of them are, what are the color of sky, does it has sun?) and then query some huge photo sets about how many of images contains people and also have a sky as a background). It should be really fast as your plugin can use some C++ library to fastly process images. Because you are fully controlling with what and how you feeds evaluator, you can use parallelism to process multiple files at once :)

I was targeting to be succesor of LogParser more :)

You can also look how implemented are default plugins, especially flat file one, it's pretty easy

https://github.com/Puchaczov/Musoq/tree/master/Musoq.Schema....

Comments URL: https://news.ycombinator.com/item?id=17578355

Points: 71

# Comments: 14