↓Skip to main content
  1. Tags/

SQL

GEOG 868: Mapping the Class Roster

·6 mins
GIS SQL CLI database design map design cartography PostGIS pgcli ogr2ogr QGIS

class roster map

In Lesson 3 of GEOG 868 I played around with PostgreSQL and PostGIS for the first time. For the project at the end you are given a class_roster.txt file that contains a list of students and their postal codes. The task was to join that with data provided earlier in the lesson and then plot the students’ locations on a map in QGIS. Here’s how I went about doing that.

SQLite database WHERE clause condition not filtering rows

·5 mins
SQLite SQL database design

It is possible to create a table in an SQLite database where the columns have no type.

For example, the columns in this STATS table have no type specified: 

sqlite> PRAGMA TABLE_INFO('STATS');
cid  name       type  notnull  dflt_value  pk
---  ---------  ----  -------  ----------  --
0    PLAYER_ID        0                    0 
1    YEAR             0                    0 
2    TEAM             0                    0 
3    AB               0                    0 
4    HITS             0                    0 
5    HR               0                    0 
6    RBI              0                    0



This doesn’t seem to make much of a difference if you are storing strings / text data, and writing queries to retrieve things based on those strings.

However, if you are storing numbers, then you’ll notice a problem if you try queries like this: