Erick Ndava

A geospatial scientist. Here I blog on things geo and data, the rumblings of an enthusiast.

© 2025. All rights reserved.

Week 7: Reflecting on CS50’s SQL as a Spatial Scientist.

05 Sep 2025

As opportunity would have it, this week I was on a work sponsored esri training course, ArcPro Advanced. One of the topics covered was Simple SQL Queries. CS50’s SQL Topic became an opportunity to re-learn SQL, dive deeper and master the proper crafting of queries.

The first part turned out to be a great easy-going refresher. As a spatial scientist I ask spatial data questions all the time. As geohipsters, we actually have a cute file format, geopackage which is essentially a sqlite database. In the course of the material. I couldn’t help reminisce PostGIS and SpatialSQL. I have a decent exposure to SQL but, further on with Week 7 content, queries became complex and my learning began to compound.

I noticed a personal preferance when it came to constructing queries, favouring

JOIN ... ON ... ---Inner Joins

versus

SELECT ... FROM ( SELECT ... ---Subqueries/ nested SELECT statements

Which was an interesting introversion of how I understood things. At the end of it, it was great mastering the alternative.

The brief discussion of SQL Injection attacks during lectue was very intriguing. A peek into cybersecurity.

Learnings for the geo-person

  1. Geospatial software leverage relational databases ubiquitously. As such, the topic of databases is one that cannot be side stepped in the profession. It is one that can actually be divergent, as some projects demand tools which emphasise data storage versus a spatial view. Few years back I was intrigued by a command line spatial data viewer which connected to PostGRES with a PostGIS extension. Not long DBeaver, a universal databae tool, debuted with a spatial viewer.
    Something that was domain specific to GIS.

  2. A great geospatial scientist should know above average SQL. Increasingly, it is apparent that spatial is just another column in the database. But the gis tech, has the advantage of spatial thinking.

  3. After completing this section, I can now do nested and more complex queries. Behind every icon in the spatial manipulation software GUI. The ready to use Intersect icon, is actually a

         SELECT spatial_features FROM spatial_table_A  
         JOIN spatial_table_B  
         ON spatially_congruent_features

  4. My greatest gain from this section was learning to work with database informaton programmatically. This came through the Fiftyville task of Problem Set 7.

  5. In all honesty, I underestimated the depth of topic for this section. It was a great break from general purpose programming and I learnt much much more on SQL. I now appreciate better, the responsibilities of a DBA. The prospects of the average geospatial scientist expanding into webmapping and scripting become greater.

That’s Week 7, now looking forward to HTML, CSS and JavaScript. Again topics not too foreign but another opportunity to re-learn stuff. As a parting note…Little bobby tables :laughing: .

`Robert'); DROP TABLE Students;--`?

Recent Posts