veen Please excuse the delay. I've been in the middle of moving in with the S/O on top of a family visit that has thrown off my schedule without my full workstation... You've hit a loooooooooot of topics swirling on my mind for a few years now. Looking back on my certificate and MS in GIS. I've realized this profession could easily be taught in a trade school. A couple courses here (one on vector data and it's applications; followed by a course on raster), another there (types of data collection - in situ, remote sensing and its varieties). Maybe, just maybe some visualization via web dev (AGOL), storage/'big data' (SQL/PostGIS) or automation (ArcPy). That right there could get a solid 2 years' worth of 'full-time' schooling and be done with it. This isn't to say I'm happy to have a couple extra letters after my name, but - call it imposter syndrome if you want - I don't think the value of what I learned in grad. school significantly impacted the type of job or salary I got coming out. Even looking at the market now, there is very little in between from technician to developer work. From what I've found, most job descriptions don't have that much variation between Tech - Analyst - Specialist. Unless, of course, you're starting out as an imagery analyst. Which is a job that has its day's numbered. A shockingly small niche (over here at least) is the people who are good at writing queries and half-decent at GIS. PostGIS legitimately can replace 95% of the individual pre-made tools QGIS and ArcGIS has to offer. You can do much more complex things much faster. My largest project the past year ended up being 2300 lines of PostGIS/SQL code I wrote on my own. The first 30% is just data prep written in code - "make sure I properly join tables A thru G in the data type I want it to be without ever having to touch Field Mappings ever again". The rest is a bunch of clever geo-joins and a bunch of not clever regular joins of tables and features. Nothing special to anyone who already knows how to handle semi-long SQL queries; PostGIS is really just one new column type and a bunch of functions to do stuff with it. The tipping point at my last workplace was a senior GIS dev that replicated my 2 months of census analysis (via ArcGIS) under 2 weeks with PostGIS. Now, I adore this dev, they were the only mentor in that floor. It made me realize how much more there was out there in GIS (and how little I could contribute to the field with my current skillset). Most salient of the points made while watching said dev work was "You've done a great job grinding at this for the past two months, though QC'ing is not possible since ESRI writes fresh data every. step. of. the. way. Now scale that over the course of 2 months of troubleshooting, trial & error, etc." It's a no-brainer. Would you mind sharing the course you took by chance? My resources of yet are a couple textbooks by recommendation of the former GIS Dev co-worker, a 4 hour YT vid on PostgreSQL, and a coursera course on SQL.As a whole the GIS world is...surprisingly shallow. There are some technical niches for sure, but compared to what I've seen in other domains of engineering, one can get incredibly fast to a point where one can do 80% of all GIS work. Really, a basic GIS course combined with a modicum of data-wrangling chops and Google skills can get you very far. To speak from personal experience; I had 2 mandatory GIS courses at uni, took one PythonGIS elective
and learned enough on the job the past 4 years (all of ArcGIS Online PostGIS + ArcPy) that I can prolly apply for most senior GIS jobs out there. A lot of GIS work is just about getting the right input into the right GIS tool(s) and ✨presenting✨the result. I know people who have done nothing more than "load data into GIS, apply pre-made tools, visualize" for decades.
I learned Python/ArcGIS programming with the Zandbergen Esri Press book. PostGIS was a tailor-made course with some booklets that explain the most common hurdles that you'll run into going blind, but it was in Dutch so I don't think it'll be of much help. Honestly - there are likely a bunch of good free tutorials out there, and the rest you can learn by doing a lot of googling that starts with "postgis" followed by some geoprocessing function you already know how to use in ArcGIS. There's a lot of useful PG code on GIS Stack Exchange, but I would strongly recommend you put in the effort to figure out what all the puzzle pieces do in other people's code answers. PG for day to day GIS work is best learnt by doing. One piece of tribal knowledge: you can rename columns on the fly, simply by having any text after it. The official syntax is Basically, PostGIS is a big sack of useful functions and it's up to you to assemble them correctly. The biggest increases in productivity beyond basic operations, has been to understand a) how to cast data (e.g. '1234'::int becoming the int 1234), b) understanding when to use nested queries (mostly whenever your joins become too large for your memory to handle), and c) mastering the GROUP BY. The best learning tool for me has been to create one "Cheat Sheet" file. Every time I learn a new function or way of solving a problem, I add it to that file with a little one-line comment. Below a few useful ones for the most common geodata edits I keep at the top, which are the below. SELECT PostGIS_full_version(); --create column index create index idx_cbs_woonkernen_reistijd_weg_woonkern on bo_cbs.cbs_woonkernen_reistijd_weg using btree(woonkern); create index sidx_hx_tmp_geom ON hx_tmp USING gist(geom); --add primary key alter table bo_cbs.cbs_woonkernen_reistijd_weg add id serial primary key; --change coordinate system alter table bo_cbs.cbs_woonkernen_reistijd_weg alter column geom type geometry(polygon,28992) using st_transform(geom,28992); -- Set geometry type and drop z axis from points that have them alter table pr_dm_eindhoven_2022.deelautos_benchmark alter column geom type geometry(point,4326) using st_force2d(geom); -- alter geometry type ALTER TABLE my_table ALTER COLUMN geom TYPE geometry(MultiPoint,4326) USING ST_Multi(geom); ALTER TABLE pr_dm_heuvelrug_2019.tankstations ALTER COLUMN geom TYPE geometry(Point,28992); ALTER TABLE pr_dm_engie_2021.lms_wegen_2030 ALTER COLUMN wkb_geometry TYPE geometry(LineString,28992) using st_transform(st_setsrid(wkb_geometry, 4326),28992); select updategeometrysrid('pr_dm_engie_2021', 'lms_wegen_2030', 'wkb_geometry', 28992)
but the much more common lazy variant is to drop the 'as' and to use very short aliases for tables. So this is valid and common to see, because it's much faster to refer to a table by 1 letter even if it's harder to read for others: SELECT table.columname AS newcolumname, SELECT b.columname a FROM schema.table b -- find out your current PG version
Bookmarked this very comment. Thank you very much. It's been hard not to get distracted with shiny gadgets like qgis2threejs while messing around with personal projects. While I realize I've come a little farther than I expected in a matter of days, there's quite a lot to polish and a lot of resources out there. Appreciate the back and forth on the topic. Hard to find opensource GIS discussion IRL in my town.
I'm learning/using PostGIS atm. It amazes me what can be done. Tangentially, I have a very very basic Postgres question: Can you query Postgres and just get the value(s) from a row without JSON or any other formatting? i.e. ..and if v1 is "foo", it only returns "foo". Not a table or JSON array with "foo" in it, etc. SELECT v1 FROM things WHERE id = 3;
That's mostly likely a function of whatever thing / programme you're using to query. Through PGAdmin or QGIS I also always get a table back. But I get values back when I query via Python: psycopg's fetch functions return just the data in a semi-structured fashion.