This post is part of a series [1], [2], [3], [4]
So, PHP supports SQLite out of the box (http://www.sqlite.org/, now at 3.7.10), making it a nice combo if you want to do some reads from your page. My impression is that SQLite is not recommended if you want stuff with database writes and you have more than a couple visitors. But reading seems to be fine.
I think this fits my use cases just fine as I just want to hang out some very basic utility services that can run on the “single beige box in the corner” or the “beige cloud in the sky” with few resources needed. First, I want to create a simple REST service that, when passed a pair of long/lat coordinates, will do nothing but return that name of the county they are in. Then I’ll do one for watershed identifiers (USDA WBD HUC12 to be exact), and eventually maybe I’ll work up to a nearest place service (http://www.geonames.org/) and so on. Maybe even some downstream/upstream routing with Spatialite’s network utilities [1], [2], [3].
Of course, these are spatial functions so I’m using the spatial extension to SQLite called Spatialite found at http://www.gaia-gis.it/gaia-sins/. I find Spatialite to be a profoundly elegant amalgam of existing projects (SQLite and GEOS) and new, efficient and pragmatic programming that fills an empty niche. Here that niche is don’t make me deploy anything more than I need. I simply want some basic, re-usable services that don’t do much, including have their data updated – and I don’t want to run a heavy spatial infrastructure just to cheaply answer some basic questions on a $6/month virtual, private, cloud LAMP box with 256MB of memory or this little Pentium 4 appliance running in my snack drawer at work.
So, I began compiling spatialite, and at the time I was using 3.0beta1a, so I just kept running with it. I’m still learning the basics of spatialite so I dinked around a bit. Then I followed the instructions for getting spatialite running within PHP [here] and/or [here] (not sure which one is the official guide. The site has been migrating to a new infrastructure lately).
After making way too many typos, I got it working and am getting the expected output. I also added some timer code which tells me that from my Ubuntu VM running on my 6-month-old laptop I’m completing these ~30,000 operations in about 6 seconds against the in-memory database, including opening and closing the connection to a database and tables that are created each page load.
[
My next exercise will be to figure out how to connect to an existing disk-based DB and try some simpler operations. My goal will be to get my operations out the door in about 1 second on modest hardware under no load.
Testing SpatiaLite on PHP
# Testing SpatiaLite on PHP
loadExtension('libspatialite.so');
# enabling Spatial Metadata
# using v.2.4.0 this automatically initializes SPATIAL_REF_SYS
# and GEOMETRY_COLUMNS
$db->exec("SELECT InitSpatialMetadata()");
# reporting some version info
$rs = $db->query('SELECT sqlite_version()');
while ($row = $rs->fetchArray())
{
print "
### SQLite version: $row[0]
";
}
$rs = $db->query('SELECT spatialite_version()');
while ($row = $rs->fetchArray())
{
print c
### SpatiaLite version: $row[0]
";
}
# creating a POINT table
$sql = "CREATE TABLE test_pt (";
$sql .= "id INTEGER NOT NULL PRIMARY KEY,";
$sql .= "name TEXT NOT NULL)";
$db->exec($sql);
# creating a POINT Geometry column
$sql = "SELECT AddGeometryColumn('test_pt', ";
$sql .= "'geom', 4326, 'POINT', 'XY')";
$db->exec($sql);
# creating a LINESTRING table
$sql = "CREATE TABLE test_ln (";
$sql .= "id INTEGER NOT NULL PRIMARY KEY,";
$sql .= "name TEXT NOT NULL)";
$db->exec($sql);
# creating a LINESTRING Geometry column
$sql = "SELECT AddGeometryColumn('test_ln', ";
$sql .= "'geom', 4326, 'LINESTRING', 'XY')";
$db->exec($sql);
# creating a POLYGON table
$sql = "CREATE TABLE test_pg (";
$sql .= "id INTEGER NOT NULL PRIMARY KEY,";
$sql .= "name TEXT NOT NULL)";
$db->exec($sql);
# creating a POLYGON Geometry column
$sql = "SELECT AddGeometryColumn('test_pg', ";
$sql .= "'geom', 4326, 'POLYGON', 'XY')";
$db->exec($sql);
# inserting some POINTs
# please note well: SQLite is ACID and Transactional
# so (to get best performance) the whole insert cycle
# will be handled as a single TRANSACTION
$db->exec("BEGIN");
for ($i = 0; $i < 10000; $i++) { # for POINTs we'll use full text sql statements $sql = "INSERT INTO test_pt (id, name, geom) VALUES ("; $sql .= $i + 1; $sql .= ", 'test POINT #"; $sql .= $i + 1; $sql .= "', GeomFromText('POINT("; $sql .= $i / 1000.0; $sql .= " "; $sql .= $i / 1000.0; $sql .= ")', 4326))"; $db->exec($sql);
}
$db->exec("COMMIT");
# checking POINTs
$sql = "SELECT DISTINCT Count(\*), ST_GeometryType(geom), ";
$sql .= "ST_Srid(geom) FROM test_pt";
$rs = $db->query($sql);
while ($row = $rs->fetchArray())
{
# read the result set
$msg = "Inserted ";
$msg .= $row[0];
$msg .= " entities of type ";
$msg .= $row[1];
$msg .= " SRID=";
$msg .= $row[2];
print "
### $msg
";
}
# inserting some LINESTRINGs
# this time we'll use a Prepared Statement
$sql = "INSERT INTO test_ln (id, name, geom) ";
$sql .= "VALUES (?, ?, GeomFromText(?, 4326))";
$stmt = $db->prepare($sql);
$db->exec("BEGIN");
for ($i = 0; $i < 10000; $i++) { # setting up values / binding $name = "test LINESTRING #"; $name .= $i + 1; $geom = "LINESTRING("; if (($i%2) == 1) { # odd row: five points $geom .= "-180.0 -90.0, "; $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= -10.0 – ($i / 1000.0); $geom .= ", "; $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= 10.0 + ($i / 1000.0); $geom .= ", "; $geom .= 10.0 + ($i / 1000.0); $geom .= " "; $geom .= 10.0 + ($i / 1000.0); $geom .= ", 180.0 90.0″; } else { # even row: two points $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= -10.0 – ($i / 1000.0); $geom .= ", "; $geom .= 10.0 + ($i / 1000.0); $geom .= " "; $geom .= 10.0 + ($i / 1000.0); } $geom .= ")"; $stmt->reset();
$stmt->clear();
$stmt->bindValue(1, $i+1, SQLITE3_INTEGER);
$stmt->bindValue(2, $name, SQLITE3_TEXT);
$stmt->bindValue(3, $geom, SQLITE3_TEXT);
$stmt->execute();
}
$db->exec("COMMIT");
# checking LINESTRINGs
$sql = "SELECT DISTINCT Count(\*), ST_GeometryType(geom), ";
$sql .= "ST_Srid(geom) FROM test_ln";
$rs = $db->query($sql);
while ($row = $rs->fetchArray())
{
# read the result set
$msg = "Inserted ";
$msg .= $row[0];
$msg .= " entities of type ";
$msg .= $row[1];
$msg .= " SRID=";
$msg .= $row[2];
print "
### $msg
";
}
# insering some POLYGONs
# this time too we'll use a Prepared Statement
$sql = "INSERT INTO test_pg (id, name, geom) ";
$sql .= "VALUES (?, ?, GeomFromText(?, 4326))";
$stmt = $db->prepare($sql);
$db->exec("BEGIN");
for ($i = 0; $i < 10000; $i++) { # setting up values / binding $name = "test POLYGON #"; $name .= $i + 1; $geom = "POLYGON(("; $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= -10.0 – ($i / 1000.0); $geom .= ", "; $geom .= 10.0 + ($i / 1000.0); $geom .= " "; $geom .= -10.0 – ($i / 1000.0); $geom .= ", "; $geom .= 10.0 + ($i / 1000.0); $geom .= " "; $geom .= 10.0 + ($i / 1000.0); $geom .= ", "; $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= 10.0 + ($i / 1000.0); $geom .= ", "; $geom .= -10.0 – ($i / 1000.0); $geom .= " "; $geom .= -10.0 – ($i / 1000.0); $geom .= "))"; $stmt->reset();
$stmt->clear();
$stmt->bindValue(1, $i+1, SQLITE3_INTEGER);
$stmt->bindValue(2, $name, SQLITE3_TEXT);
$stmt->bindValue(3, $geom, SQLITE3_TEXT);
$stmt->execute();
}
$db->exec("COMMIT");
# checking POLYGONs
$sql = "SELECT DISTINCT Count(\*), ST_GeometryType(geom), ";
$sql .= "ST_Srid(geom) FROM test_pg";
$rs = $db->query($sql);
while ($row = $rs->fetchArray())
{
# read the result set
$msg = "Inserted ";
$msg .= $row[0];
$msg .= " entities of type ";
$msg .= $row[1];
$msg .= " SRID=";
$msg .= $row[2];
print "
### $msg
";
}
# closing the DB connection
$db->close();
// End TIMER
// ———
$etimer = explode( ' ', microtime() );
$etimer = $etimer[1] + $etimer[0];
echo '
';
printf( "Script timer: **%f** seconds.", ($etimer-$stimer) );
echo '
';
// ———
?>