Database access via Python

In my ongoing quest to do absolutely everything through Python, I’ve been looking a lot lately at manipulating databases. I’ve been using arcpy to access GIS databases for years, and last year I finally got around to using pyodbc (and pypyodbc) for accessing SQL Server databases.

Now that I’m in an Oracle environment, Oracle has provided the cx_Oracle library to directly connect to databases. I have yet to test that though. What I’m interested in at the moment is creating and accessing databases for personal use.

I considered MongoDB for a while, but I don’t think I want to go NoSQL yet. This is why I have been experimenting with SQLite (through the sqlite3 library), as it is included in the Python install, and has the delightful SpatiaLite extension. The slogan goes against my one of my mottos (Spatial is Special) while supporting my other motto (Everything is Spatial).

Spatially enable a column in a SQL Server table

Lately I’ve had to dig up my knowledge of SQL from university for some tasks at work, as well as the module I recently completed through UNISA.

The coordinates are in two columns, and I had to present the data back to the client as a spatially enabled view. Thanks to my colleague Grobbelaar for the code, and for showing me some cool stuff in SQL Server Management Studio (in which I am a total n00b).

	SELECT GIS_ID, GEOMETRY::STPointFromText('POINT(' + CONVERT(varchar(50), Longitude) + ' ' + CONVERT(varchar(50), Latitude) + ' )', 4326) AS Location
	FROM tbl_random
	WHERE Longitude > 0 # Checks if there are valid coordinates for Eastern Hemisphere

view raw

convertVarcharToGeometry.sql

hosted with ❤ by GitHub

The where clause will fail if the coordinates are not in the Eastern Hemisphere. I could have gone for Latitude < 0 as well, but that still leaves me in the same situation. As all of the data for this client is in South Africa, it was safe to use this expression. It's not robust though.