Spatial database is the basics of map technology. Now I got chance to implement it. Let’s check how it differs from conventional DBs, and visualize our results in Google Earth using KML format!
Let’s land a quick start
- Get latitude, longitude pairs
- Import into KML file
- Optionally, visualize with Google Earth
- Install spatial database and make queries
I did this in person… At least for me I had to…
A great way to drive a programmer out of chair, isn’t it?
I used IPhone Compass App, which is more convenient than I though it would be!
You have to convert minutes and seconds into degrees
one degree is subdivided into 60 minutes (60’), and one minute is subdivided into 60 seconds (60’’) - so for example, 30’15”, since it is equivalent to 1815”, would be eqvt to 1815/3600=0.504 degrees.
I felt like to have 9 points for today, so 9 of this kind of coordinates were all I got.
Import into KML
KML stands for Keyhole Markup Language (so many MLs these days). More details
A skeleton can be filled:
<?xml version="1.0" encoding="UTF-8"?> <kml xmlns="http://www.opengis.net/kml/2.2"> <Placemark> <name>A placemark</name> <description>Attached to the ground. Intelligently places itself at the height of the underlying terrain.</description> <Point> <coordinates>-122.0822035425683,37.42228990140251,0</coordinates> </Point> </Placemark> <Placemard> ... </Placemark> ...... </kml>
Insert names, descriptions and, most importantly, coordinates. Save it as spatial.kml.
visualization is a good way to know what you are really doing.
For quick check of KML file, go to this site. All needed to be done is a copy-paste-click.
After it works, you can upload to Google Map. (Chrome it). Awesome.
You can also add tags like
<Icon> etc. into KML to make customized style. Learn more on KML documentation!
Word with spatial database
There are several choices for the task.
- Oracle 11g+Oracle Spatial
- Other challenges…
I decided to dig into Postgres with its ‘BigSQL’. It involves:
- PostgreSQL. The main RDBMS.
- PostGIS. Spatial extension of Postgre.
Setups for Postgres (MacOS)
I do hate setting up … But I was told that’s
‘What real software engineers do’.
Go to Postgres, and install as instructed. If failed, try
sudo easy-install pip
in Terminal to install Python pip.
As well, install PostGIS on MacOS via
brew install postgis
If it brought postgis to wrong directory (bad luck for me), an alternative way is cd into
/Users/username/Postgres and run
./pgc install Postgis.
Now with everything set up, I am going to create my tables.
Working on spatial DB
Connect PostGIS to Postgres
PostGIS is a extension so by default it’s not mounted. To connect manually in
CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;
To create a new spatial DB on PostGIS, you can either:
- using ‘pgAdmin’ GUI
- using psql CLI
Both of them should work. I went for pgAdmin this time.
In pgAdmin, everything looks pretty straight-forward.
- Create a new server on Postgre by
File - Add Server.
- Password is what you set during installation.
- If authentication failed, try
ALTER USER postgres PASSWORD 'my_postgres_password';in
- Right click ‘Databases’ subdirectory of DB just created to create a new Database. Make sure
Owneris set to
After done, check PostGIS installation by
- Create table for geographic data
Run SQL to create table. Not much difference from conventional ways.
CREATE TABLE USC_placemarks ( name character varying(50), longitude numeric, latitude numeric );
Don’t forget to
refreshto show created table.
- Load CSV
Various kind of data can be imported, while CSV is the most straight-forward one. Make sure csv is formatted with header in first row.
Right click the table just created and click ‘import’. Check all needed column and check
Mics. Options. Set
Delimiterto comma of course.
To check what has been imported, right click table and View Data -> View All Rows
- Populate GEOM Field
We need Geom field to do Queries.
ALTER TABLE usc_placemarks ADD COLUMN geom geometry(POINT,4326)
UPDATE usc_placemarks SET geom = ST_SetSRID(ST_MakePoint(longitude,latitude),4326);
Data has been all set now! Let make some spatial queries!
- Convex Hull
a convex hull for a set of 2D points is the smallest convex polygon that contains the point set
SELECT ST_AsText(ST_ConvexHull(ST_Collect(t.geom))) As the_geom FROM placemarks as t
The query involves several PostGIS functions (start with
- ST_Collect: project all geom items in table
- ST_ConvexHull: calculate convex hull from given geom items and return in geom format
- ST_AsText: convert geom object to text (coordinates) With coordinates, it’s easy to import them into KML file and show in Google Earth.
- Nearest Neighbors
SELECT t1.name,ST_AsText(t1.geom) as coord FROM usc_placemarks as t1, usc_placemarks as t2 WHERE t2.name = 'Home' AND t1.name <> 'Home' ORDER BY ST_Distance(t1.geom,t2.geom) LIMIT 3;
To query 3 closest neighbors of home in database. Then make a KML file from derived coordinates to show as lines in Google Earth.
- For more spatial implementation and reference, refer to Docs
(Extra) Spirograph Curve
Last, let’s make a bonus plot with Google Earth: Spirograph Curve. The function expression of Spirograph Curve is:
x(t) = (R+r)*cos((r/R)*t) - a*cos((1+r/R)*t) y(t) = (R+r)*sin((r/R)*t) - a*sin((1+r/R)*t)
To plot this pattern:
- Install MatLab plot support for Python.
pip install matplotlib
- Then draw the pattern in Python with
import matplotlib.pyplot as plt import math import numpy cX, cY = -118.288, 34.021 xVals =  yVals =  R, r, a = 8, 1, 4 sin, cos, pi, nRev = math.sin, math.cos, math.pi, 16 for t in numpy.arange(0.0, nRev * pi, 0.02): x = (R + r) * cos((r / R) * t) - a * cos((1 + r / R) * t) + cX y = (R + r) * sin((r / R) * t) - a * sin((1 + r / R) * t) + cY xVals.append(x) yVals.append(y) plt.plot(xVals, yVals) plt.xlabel('x - axis') plt.ylabel('y - axis') plt.title('Spirograph Curve') plt.show()
It pops a graph telling it works: Then we can use python to write it into KML then import to Google Earth: Now you are a ninja Google Earth!