NOVA Plays

Several years back Code for NOVA, the Northern Virginia brigade of Code for America, developed Project Play Northern Virginia, its first and still among its most successful projects.

This exploration will document the process of bringing NOVA Plays up to date, with the goal of swapping out several parts of the software stack on which it is built. The goal is to produce a smaller, lighter weight application, by migrating from Ruby on Rails to the Python microframework Flask and from PostgreSQL to the spatial version of SQLite.

Moving a PostgreSQL Database to SpatiaLite

SQLite is a lightweight server-less relational database management system (RDBMS. SpatiaLite adds spatial database features to SQLite.

NOVA Plays does not require multiple users or a large database server. Moving from PostgreSQL to SpatiaLite would simplify our application and make deployment and future development easier.

Installing SpatiaLite

Running the following will install everything needed for SpatiaLite on Ubuntu 18.04:

$ sudo apt install sqlite3 spatialite-bin spatialite-gui

The sqlite3 package adds a command interpreter for interacting with SQLite. With the spatialite-bin package, the spatialite command will launch a version of this same command interpreter that also supports spatial tables and queries.

Changing a PostgreSQL Database to an SQLite Database

The PostgreSQL data is in NoVaPlaysDB_Postgresql.sql. It uses PL/pgSQL extensions, while we plan to use Python in the new version. The database dump also contains several statements setting the ownership of each of the tables. These will not be needed with SpatiaLite. After removing these, there are three tables that contained the data needed for the application:

  1. aliases

  2. criteriakeys

  3. playgrounds

Some messaging of the tables and data is also required for SQLite, including:

  • Changing character varying(255) to simply character.

  • Changing the type boolean to integer and change the corresponding values false to 0 and true to 1.

Making these changes produces ProjectPlayNoVa.sql, which can be loaded into an SQLite database and accessed by the SQLite shell by running:

$ sqlite3 novaplays.db --init ProjectPlayNoVa.sql

From the shell, we can look at the schema:

-- Loading resources from ProjectPlayNoVa.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE aliases (
    id integer NOT NULL,
    playground_id integer,
    aliasname character
);
CREATE TABLE criteriakeys (
    id integer NOT NULL,
    criterianame character,
    scalevalue integer,
    textvalue text,
    description character
);
CREATE TABLE playgrounds (
    id integer NOT NULL,
    name character,
    mapid integer,
    agelevel character,
    totplay integer,
    opentopublic integer,
    invitation integer,
    howtogetthere integer,
    safelocation integer,
    shade integer,
    monitoring integer,
    programming integer,
    weather integer,
    seating integer,
    restrooms integer,
    drinkingw integer,
    activeplay integer,
    socialplay integer,
    creativeplay integer,
    naturualen integer,
    freeplay integer,
    specificcomments text,
    generalcomments text,
    compsum integer,
    modsum integer,
    graspvalue integer,
    playclass character,
    subarea text,
    created_at timestamp without time zone,
    updated_at timestamp without time zone,
    lat double precision,
    long double precision,
    google_placesid character,
    imageurl text,
    address character,
    is_school integer,
    pleasant_surrounding_rating integer,
    fencing_rating integer,
    disabilities_rating integer,
    musical_features_rating integer,
    swings_rating integer,
    bucket_swings_rating integer,
    sprayground_play_fountain_rating integer,
    skate_park_rating integer,
    streams_rating integer,
    slide_ratings integer,
    climbing_structures_rating integer,
    spinning_structures_rating integer,
    rocking_structure integer,
    balance_features_rating integer,
    sandbox_rating integer,
    playhouse_rating integer,
    stage_rating integer,
    path_for_wheeled_toys_rating integer,
    basketball_courts_rating integer,
    surface_marked_for_games_rating integer,
    open_space_for_ball_play integer,
    woods_rating integer,
    garden_rating integer,
    natural_elements_rating integer,
    other_cool_features text,
    disabilities_comments text,
    zip_code character
);

and some fields in the playgrounds table:

sqlite> SELECT id, name, lat, long FROM playgrounds ORDER BY id;
2|Windmill Hill Park|38.799668|-77.041243
3|Chinquapin Rec Center|38.821787|-77.081566
4|Eugene Simpson Stadium Park|38.82242|-77.053991
5|Charles Barrett Elementary School|38.841697|-77.076108
6|Dora Kelley Park Ford Nature Center|38.826905|-77.130914
7|Ben Brenman Park|38.808162|-77.113257
9|Hillwood Apt|38.831354|-77.126814
10|Meadow Creek Apartments|38.826801|-77.130727
11|Patrick Henry School|38.817606|-77.111073
12|Tarleton Park|38.808752|-77.108361
13|James K Polk School|38.822448|-77.116993
14|ARHA_Oronoco|38.809194|-77.042768
15|Exchange at Van Dorn|38.801673|-77.120344
16|Powhatan Park|38.817729|-77.048992
17|Samuel Tucker Elementary|38.805469|-77.127557
18|Maury Elementary School|38.812714|-77.064445
19|Cameron Parke Townes|38.805327|-77.094769
21|Ewald Park|38.811267|-77.108631
22|ARHA_Braddock|38.831044|-77.10713
23|South Port Apt|38.806045|-77.138761
24|Hooffs Run Park Green|38.809568|-77.059406
25|EOS 21 Condo|38.812743|-77.126318
28|John Adams School|38.834147|-77.124638
29|Beverly Park|38.837844|-77.072265
30|Beverley Hills Church Preschool Playground|38.838704|-77.068623
31|Douglas MacArthur School|38.816005|-77.08311
32|Jefferson Houston School|38.808049|-77.054765
33|Beach Park|38.813364|-77.065837
34|Chetworth Park|38.819637|-77.045953
35|Watergate at Landmark Condominium|38.808689|-77.14168
36|Stevenson Park|38.811435|-77.143257
37|Brookvalley Park-Ripley St. (Holmes Run)|38.818803|-77.126252
38|Mt Vernon Elementary School|38.827783|-77.059251
39|Armistead Boothe Park|38.805484|-77.127578
40|Lyles Crouch School|38.800132|-77.046654
41|East Del Ray Avenue Park|38.825626|-77.061592
42|Potomac Greens HOA|38.831731|-77.045639
43|George Mason Elementary School|38.828909|-77.072405
44|Nannie J Lee Recreation|38.796464|-77.053064
45|Park Fairfax|38.835797|-77.077732
46|Landover Park|38.835236|-77.061754
47|Fort Ward Park Hist Site|38.831184|-77.100571
48|James Mulligan Park|38.838489|-77.107302
49|Mt Jefferson Park Green|38.830651|-77.055817
50|Fairlington United Methodist Church|38.833049|-77.096171
52|Goat Hill Park|38.832367|-77.06101
53|Armory Tot Lot|38.803021|-77.043672
54|Charles Barrett Recreation Center|38.841731|-77.076124
55|Southern Towers|38.83263|-77.112798
56|Chatham Square HOA|38.808781|-77.04323
57|Landmark Mall|38.816134|-77.131862
58|George Washington Middle School|38.815219|-77.05637
59|Hume Springs Park|38.836815|-77.059173
60|Mason Avenue Park|38.820804|-77.062244
61|Sunset Park|38.807218|-77.062165
62|Chinquapin Park|38.821826|-77.081645
63|Lynhaven Park|38.836545|-77.056602
64|Newport Village Apt|38.837501|-77.111296
65|ARHA_Whiting|38.810169|-77.136896
66|Holmes Run Park-S. Jordan St.|38.812053|-77.109617
67|Angel Park|38.809893|-77.075151
68|Woodbine Park|38.829289|-77.082485
69|Buchanan Park|38.808607|-77.057203
70|Landmark Terrace|38.809175|-77.133931
71|ARHA_Henry|38.816198|-77.111823
74|Bennington Crossing Apt|38.82043|-77.132311
75|Saxony Square|38.822258|-77.133625
76|Four Mile Run Park|38.842267|-77.061253
77|Charles Houston Rec Center|38.812974|-77.047821
78|Hunter Miller Park|38.807861|-77.052248
79|Montgomery Park|38.814436|-77.041162
80|Cora Kelly School|38.838189|-77.058279
81|ARHA_Tancil Ct.|38.809627|-77.042141
82|Brookville Townhomes|38.819852|-77.124596
83|Old Town Village|38.80353|-77.054055
84|Mayflower Square Condos|38.820111|-77.135636
85|Summers Grove Townhomes|38.801192|-77.132686
87|Sentinel of Landmark|38.812348|-77.140862
88|840 N. Alfred Street Park |38.813761|-77.047391
89|Potomac Yard Park - Swann Ave.|38.8291477|-77.0479654
90|Jones Point Park|38.7932892|-77.0420609
91|Potomac Greens Park|38.830894|-77.045242
92|Woodmont Park Apartments|38.822193|-77.130257
93|Tower 2000 Apartments|38.819172|-77.136772
94|The Seasons Condos|38.818794|-77.134476
95|Chinquapin Park Rec Center Soft Playroom|38.821787|-77.081566
96|Chinquapin Park Recreation Center Pool|38.821826|-77.081645
97|Holmes Run Park-Trail|38.812053|-77.109617
220|Abingdon Elementary School|38.8398295|-77.09608945
221|Alcova Heights Park|38.86119583|-77.10241436
222|Arlington Hall West Park|38.8655682|-77.10692529
223|Arlington Mill Community & Senior Center|38.85640274|-77.1123008
224|Arlington Science Focus School|38.89008654|-77.10275772
225|Arlington Traditional School|38.87992896|-77.12206309
226|Ashlawn Elementary School|38.87322195|-77.13607776
227|Aurora Hills Community and Senior center|38.85744581|-77.05901106
228|Bailey's Branch Park|38.85419346|-77.1118644
229|Barcroft Elementary School|38.86161924|-77.10779214
230|Barcroft Park|38.85054828|-77.10190187
231|Barrett Elementary School|38.87281335|-77.11201466
232|Benjamin Banneker Park|38.88318332|-77.15795947
233|Big Walnut Park|38.89175639|-77.13216423
234|Bluemont Park|38.87354079|-77.13392696
235|Bon Air Park|38.87559187|-77.13311563
236|Butler Holmes Park|38.87271445|-77.0851106
237|Campbell Elementary School|38.85790082|-77.12611493
238|Carlin Hall Community Center & Park|38.86319523|-77.12616205
239|Carlin Springs Elementary School|38.8627056|-77.13189241
240|Carver Community Center/Hoffman Boston Elementary School|38.86153376|-77.07184055
241|Cherrydale Park|38.89890868|-77.10710644
242|Chestnut Hills Park|38.9006258|-77.14267301
243|Claremont Elementary School|38.8477288|-77.10588188
244|Clarenford Station Park|38.88732725|-77.11544902
245|Dawson Terrace|38.89942783|-77.08287215
246|Doctor's Branch Park|38.85612085|-77.09995473
247|Douglas Park|38.8527326|-77.09645016
248|Drew Park/Drew School|38.84818166|-77.08679851
249|Eads Park|38.84813564|-77.05520173
250|Edison Park|38.86931587|-77.12103516
251|Fairlington Community Center & Park|38.83450222|-77.08717344
252|Fillmore Park/ Long Branch Elementary|38.87496268|-77.08915535
253|Fort Barnard Park|38.84919854|-77.09247624
255|Fort Myer Heights Park|38.89071768|-77.0750816
256|Fort Scott Playground|38.84737009|-77.05860634
257|Foxcroft Heights Park|38.86868739|-77.07079628
258|Glebe Elementary School|38.89257194|-77.12106741
259|Glebe Road Park|38.92140953|-77.1257555
260|Glencarlyn Park|38.86397848|-77.11909705
261|Gunston Park|38.84717915|-77.07101377
262|Hayes park|38.88970774|-77.10419126
263|HB Woodlawn School|38.90049308|-77.11120983
264|Henry Clay Park|38.88130437|-77.09404741
265|Henry Wright Park|38.87163869|-77.11027028
266|High View Park|38.89373452|-77.12713826
267|Jamestown Elementary|38.91721561|-77.1394408
268|Jennie Dean Park|38.84352593|-77.08824858
269|Key Elementary School|38.89239259|-77.08687154
270|Lacey Woods Park|38.88456376|-77.12676417
271|Langston Brown Community Center &  Park|38.89577432|-77.12655976
272|Lee Community Center|38.89466645|-77.14319352
273|Lubber Run Community Center|38.87339412|-77.11398447
274|Lyon Village Park|38.89349116|-77.09472603
275|Madison Community Center Park/Fort Ethan Allen Park|38.92325141|-77.12371293
276|Madison Manor Park|38.88207468|-77.15023227
277|Maury Park|38.88173516|-77.1020934
278|Maywood Park|38.89753245|-77.10015946
279|McKinley Elementary|38.87865535|-77.14674172
280|Monroe Park|38.85741382|-77.09262083
281|Mosaic Park|38.87797627|-77.10692695
282|Nauck Park|38.85497251|-77.08216466
283|Nelly Custis Park|38.85259576|-77.05779601
284|Nina Park|38.85207851|-77.05977366
285|Nottingham Elementary School|38.90061269|-77.1509256
286|Oakgrove Park|38.89051345|-77.10886673
287|Oakridge Elementary School|38.85051088|-77.07024031
288|Parkhurst Park|38.88934493|-77.14179047
289|Patrick Henry Elementary|38.86578637|-77.08882425
290|Penrose Park|38.86860668|-77.08269487
291|Powhatan Springs Skate Park|38.872908|-77.139356
292|Quincy Park|38.88453276|-77.10762848
293|Randolph Elementary|38.85656421|-77.09806483
294|Rocky Run Park|38.88653988|-77.08803026
295|Rosslyn Highlands Park|38.89528066|-77.07690576
296|Slater Park|38.89329097|-77.12374178
297|Taylor Elementary School|38.90678547|-77.11364964
298|Thomas Jefferson Community Center Playground|38.86963833|-77.09611995
299|Towers Park|38.8668103|-77.07740744
300|Troy Park|38.8460191|-77.07232574
301|Tuckahoe Park|38.89131281|-77.15695605
302|Tuckahoe Elementary School|38.89131281|-77.15695605
303|Tyrol Hill Park|38.85989237|-77.11772723
304|Upton Hills Park|38.87163188|-77.14378491
305|Virginia Highlands Park|38.8584015|-77.05987238
306|Vornado Crystal City|38.8520877|-77.04913313
307|Walter Reed Playground|38.85793475|-77.08623201
308|Westover Park|38.88019414|-77.13570417
309|Woodlawn Park|38.88688605|-77.12072208
310|Woodmont Park|38.9023452|-77.09773971
sqlite>

The database at this point is not a spatial database, since the lat and long fields, which contain the location of the parks, are stored as separate floating point numbers. To be able to use them in spatial queueries, they will need to be combined into a point geometry data type together with a map projection for rendering on a two-dimensional map.

We will tackle that problem in the next part of this investigation.