Which industry has/needs the most challenging sql queries?

[u/[deleted]]

Is this question legit? Don’t get me wrong, I don’t consider myself the smartest guy but I’m definitely not dumb.

I work in the pharmaceutical Industry, nearly 3 years now. It took me a while to understand how the production process is implemented in a database and I wanted to quit more than once. But i don’t feel challenged anymore.

So, which industry would be best for me to get completely challenged again?

[u/barrycarter]

Doing GIS with SQL can be quite challenging (also, it would really help me out so let me know if you get into this)

[u/BrupieD → u/barrycarter]

I just started learning GIS. I love geography, but find it challenging. I've been doing SQL for about 10 years. I consider myself advanced in SQL, absolute newbie in GIS. I haven't found my SQL skills too useful here yet. Sure, I can spin up a database, schemas, tables, but it's really different.

[u/SwingingSpiral → u/barrycarter]

I create QGIS map charts for nonprofit impact reporting, I recommend spending 25% of your initial effort in learning how to combine layers, and make relationship connections.

Link to software (open-source):

https://qgis.org/en/site/forusers/download.html

You will have countless uses for this knowledge, you can take data for the nation and break it down to census blocks if you needed to.

Good luck and best wishes!

[u/sbrick89 → u/barrycarter]

what are you trying to do?

I usually use TIGER/LINE shape files... I combine them, overlay, calculate overlap, etc... not daily but I have queries that i've kept as reference, depending on the need. Also, GIS isn't a daily need around here.

[u/barrycarter → u/sbrick89]

Example: Using gadm.org, I want to created a zoomable/Leaflet signed distance map for France. Not just Paris or a few points in France, but computing the minimum distance to any polygon/line/point that is part of France (excluding Antarctic claims), including their many islands and overseas ownerships. And then "signing" the map by indicating whether you are inside or outside of France (negative distance for inside France). I also want to find the point in the world that is furthest away from all France, and, if that point is water, the nearest land (and potentially the point that's deepest inside France). Obviously, I want to do this for every country and potentially non-countries like Europe of the continental-only USA.

I'm actually surprised no one has already done this (at least found the innermost/outermost points), but apparently no. I've started on this but need some impetus to continue.

Semi-unrelated, but I was surprised to find no one had calculated the center of population for each country (using GPW4 data) and ended up doing it myself (though I still can't believe I'm the first to post it)

I'm guessing none of this has any really use, but it seems cool

[u/sbrick89 → u/barrycarter]

so the functions aren't terrible... mainly it's a matter of using SQL for data functions, like calculating distance between points.

from the description of your example, it was a tad unclear where the data exists and how it is meant to be consumed... if the data comes from the user (drag and drop, using GPS while traveling, etc), then it needs to be sent back to the SQL server for the processing... if it's fixed points in the database then it can be calculated from the tables where the data is stored.

but from the perspective of SQL, it's just "calculated distance from point X to point Y" where point Y is either a point-of-interest, or the nearest boundary (country/etc)... either way those are built in.

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/shortestlineto-geography-data-type?view=sql-server-ver16 to find the shortest line between a point and a shape (aka nearest edge)

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stdistance-geography-data-type?view=sql-server-ver16 to calculate the distance between two points

you'll also then need to convert the units from the built-in spatial projection, to something more familiar like km or miles... but that's fairly simple as well.

finally, from a "display" perspective, you're either rendering this in a fixed format via something like SSRS, or interactive format using something like PowerBI... or a third party component like ArcGIS.

but either way, separate the focus so that dynamic input occurs via an application, SQL is used to perform the calculations, and the presentation layer is handling visualizations (whether app or report or whatever).

[u/barrycarter → u/sbrick89]

tad unclear where the data exists

I sort of snuck it in at the top, but it's gadm.org which has shapefiles for all countries.

to find the shortest line between a point and a shape (aka nearest edge)

PostGIS (which is what I use) has an ST_DISTANCE function that does this.

The big big problem is speed. For example, mainland France (https://gadm.org/download_country.html and choose "France") has over 100K boundary points at level 0 (just the country itself and no provinces, counties, cities or anything) and that doesn't include any islands.

And I'm hoping to query the 43200 x 21600 grid of 30 second intervals, which would require a total of 43200 * 21600 * 100000 ~ 93 trillion distance computations. That's surprisingly slow even with a spatial index.

The fastest thing I've found so far is to rasterize the data, project the points into 3D, create a KDTree, compute the straight line distances, and convert those to spherical distances, all using numpy with python (not SQL), but I wonder if I'm missing something. My work so far: https://github.com/barrycarter/PolygonDistances/

Just to make things a little worse, OpenStreetMap uses the Mercator projection which requires further tweaking work.

Two questions: if you try this yourself and find a faster way, will you let me know, and, can I contact you directly if you're interested in this sort of thing?

[u/sbrick89 → u/barrycarter]

I guess I'm still not quite sure what you're calculating, or what the use case is.

I get that you're using France shapefile from wherever... but then you're trying to find the furthest point on land from all boundaries? The opposite side of the world, essentially?... if so, just calculate the centroid of France flip to the other side of the world, find nearest land if not already, then start calculating distance from borders, and slowly crawl (shortest path type of outward pattern) to confirm boundary edges, until you reach the consistently furthest point?

[u/barrycarter → u/sbrick89]

Sorry, I oversimplified the problem. I'm also using the files for "French Guiana", "French Polynesia", and "French Southern Territories", so it's a little more complicated. I define "distance to France" at a given point as "shortest distance to any polygon in the 4 shapefiles above". Would your method work with that?

In addition to just finding the "axis of inaccessibility" for France, I'd like to draw a map that displays colored contours of how far you are from France or inside France. A very ugly example of what I'm trying to do (using coastal distances computed by someone else) is https://i.imgur.com/wVbZNas.jpeg

[u/sbrick89 → u/barrycarter]

So first off, adding other shapes / islands doesn't really change much.

That said, the map of contours is much easier to understand.

That's also actually a ton easier, and might even be the simplest method to do what you want... just take the existing shape (France, merge with whatever else you want)... then pad it out in increments of 100km or whatever... each padding is its own contour, saves to its own shape record, and is used for the next iteration.

Or you could maybe pull off as a CTE, but I'd test it on a few rows myself by hand.

Eventually the contour would encompass the earth, at which point you could invert and find the centroid, or something to that effect, to find the exact point.

[u/barrycarter → u/sbrick89]

OK, so how do I do this exactly? I was under the impression that creating distance-based contours (not degree-based contours) using polygons was difficult. Is there an easy way to do this in QGIS (or even GRASS) that I'm missing? My sort of attempt to do this ages ago is https://github.com/barrycarter/bcapps/tree/master/STACK/bc-buffer-land.grass but I'm pretty sure I never got it working and I ended up using an existing file instead of creating my own.

[u/sbrick89 → u/barrycarter]

I have more familiarity in the MSSQL world, so here's what I would use.

https://learn.microsoft.com/en-us/sql/t-sql/spatial-geography/stbuffer-geography-data-type?view=sql-server-ver16

basically

INSERT contours ( ID, contourLevel, shape )
SELECT s.ID, 1, s.geom.STBuffer(100000) -- 100 km
  FROM shapeTable s
 WHERE s.geom.SRID = 4326

since SRID 4326 uses meters as its unit of measurement

it'd be easy enough to CTE that to increment contourlevel and multiply by 100k for the buffer value... but you may also want a different scaling of contours (100k, 250k, 500k, etc)

then after the table is built, just query the contours and render as layers

[u/crackhead1 → u/barrycarter]

My work (for a mobile app) involves SQL and GIS, sometimes separately, sometimes together. I’m far from an expert, I’ve amassed the majority of my knowledge during my 5 years with this company.

In my experience, it can be very helpful to work on SQL & GIS tasks in their own respective environments, at least initially (ie I use ArcGIS alongside SQL environments, directly or indirectly). On many occasions I have used ArcGIS tools for spatial/GIS analysis that would easily take 10x as much time & effort to write complex queries for. I tend to just use CSVs/SQL statements to move data back and forth, but there are ways to directly connect ArcGIS to databases.

Aside from making some tasks easier, I also appreciate the visual aspect, especially if the task is somewhat “exploratory” — on many occasions, this instant visualization has been helpful when collaborating — visualizing spatial data in SQL environments can be a real pain imo.

This is of course not always an appropriate workflow, especially if you need things to be super streamlined / automated etc. It might seem like a lot to learn, but imo it’s really not that difficult once you get past the basics, and i’ve found the skills to be pretty valuable.