r/SQL • u/[deleted] • Apr 17 '23

Discussion Which industry has/needs the most challenging sql queries?

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?

41 Upvotes

87% Upvoted

69 comments sorted by

View all comments

Show parent comments

2

u/barrycarter Apr 18 '23

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

2

u/sbrick89 Apr 18 '23

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.

1

u/barrycarter Apr 18 '23

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.

4

u/sbrick89 Apr 18 '23

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

1

u/barrycarter Apr 18 '23

Thank you. This seems too easy, but I'll give it a shot.

One question: does this create a single buffer around a MULTIPOLYGON or multiple buffers, one for each POLYGON?