Intro

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

  1. Get latitude, longitude pairs
  2. Import into KML file
  3. Optionally, visualize with Google Earth
  4. Install spatial database and make queries

Get coordinates

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

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.

quick_kml

After it works, you can upload to Google Map. (Chrome it). Awesome.

map_snapshot

You can also add tags like <styleUrl> or <Icon> etc. into KML to make customized style. Learn more on KML documentation!

Word with spatial database

Pick one

There are several choices for the task.

  • Oracle 11g+Oracle Spatial
  • Postgres+PostGIS
  • MySQL
  • 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’.

Fine then.

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 Homebrew as

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 psql:

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

Create DB

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 psql
  • Right click ‘Databases’ subdirectory of DB just created to create a new Database. Make sure Owner is set to postgres.

After done, check PostGIS installation by

SELECT postgis_full_version();

Loading data

  1. 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 refresh to show created table.

  2. Load CSV

    Various kind of data can be imported, while CSV is the most straight-forward one. csv 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 Header in Mics. Options. Set Delimiter to comma of course.
    To check what has been imported, right click table and View Data -> View All Rows rows
  3. 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);
    

Query it!

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_):

  • 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.

convex_hull

  • 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:

  1. Install MatLab plot support for Python. pip install matplotlib
  2. Then draw the pattern in Python with (-118.288,34.021) as center.
    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: spiro_1 Then we can use python to write it into KML then import to Google Earth: spiro_2 Now you are a ninja Google Earth!