PHP Spatialite to GeoJSON

gistfile1.aw

<?php
/**
* Spatialite to GeoJSON

It needs:

    *  apache2
    * php5-cli
    * php5-sqlite

    http://www.gaia-gis.it/spatialite-2.4.0-4/splite-php.html

* Query a Spatialite table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
*
* @param string $geotable The Spatialite layer name *REQUIRED*
* @param string $geomfield The Spatialite geometry field *REQUIRED*
* @param string $srid The SRID of the returned GeoJSON *OPTIONAL (If omitted, EPSG: 4326 will be used)*
* @param string $fields Fields to be returned *OPTIONAL (If omitted, all fields will be returned)* NOTE- Uppercase field names should be wrapped in double quotes
* @param string $parameters SQL WHERE clause parameters *OPTIONAL*
* @param string $orderby SQL ORDER BY constraint *OPTIONAL*
* @param string $sort SQL ORDER BY sort order (ASC or DESC) *OPTIONAL*
* @param string $limit Limit number of results returned *OPTIONAL*
* @param string $offset Offset used in conjunction with limit *OPTIONAL*
* @return string resulting geojson string
*
*
* Example usage:
*
* http://localhost/spatialite_geojson.php?geotable=contact&geomfield=geometry&fields=name,street,phoneno& parameters=street='Church Street'
*  
* // For example Spatialite Query for the above URL
*  Select name, street, phoneno, asGeoJSON(geometry) from contact where street = 'Church St';
*
* Note:
* PostGIS uses the_geom
* Spatialite uses geometry 
*/
 
# Retrive URL variables
if (empty($_GET['geotable'])) {
    echo "missing required parameter: <i>geotable</i>";
    exit;
} else
    $geotable = $_GET['geotable'];

if (empty($_GET['geomfield'])) {
    echo "missing required parameter: <i>geomfield</i>";
    exit;
} else
    $geomfield = $_GET['geomfield'];

if (empty($_GET['srid'])) {
    $srid = '4326';
} else
    $srid = $_GET['srid'];

if (empty($_GET['fields'])) {
    $fields = '*';
} else
    $fields = $_GET['fields'];

$parameters = $_GET['parameters'];

$orderby = $_GET['orderby'];

if (empty($_GET['sort'])) {
    $sort = 'ASC';
} else
    $sort = $_GET['sort'];

$limit = $_GET['limit'];

$offset = $_GET['offset'];

/*
# Connect to PostgreSQL database
$conn = pg_connect("dbname='mydbname' user='myusername' password='mypassword' host='localhost'");
if (!$conn) {
    echo "Not connected : " . pg_error();
    exit;
}
*/
# connecting some SQLite DB
# we'll actually use an IN-MEMORY DB
# so to avoid any further complexity;
# an IN-MEMORY DB simply is a temp-DB
$db = new SQLite3(':memory:');

# loading SpatiaLite as an extension
$db->loadExtension('libspatialite.so');

# enabling Spatial Metadata
# using v.2.4.0 this automatically initializes SPATIAL_REF_SYS
# and GEOMETRY_COLUMNS
# $db->exec("SELECT InitSpatialMetadata()");
# not needed in new version 2.4.x and 3.x.x


# Build SQL SELECT statement and return the geometry as a GeoJSON element in EPSG: 4326
$sql = "SELECT " . spl_escape_string($fields) . ", asgeojson(transform(" . spl_escape_string($geomfield) . ",$srid)) AS geojson FROM " . spl_escape_string($geotable);
if (strlen(trim($parameters)) > 0) {
    $sql .= " WHERE " . spl_escape_string($parameters);
}
if (strlen(trim($orderby)) > 0) {
    $sql .= " ORDER BY " . spl_escape_string($orderby) . " " . $sort;
}
if (strlen(trim($limit)) > 0) {
    $sql .= " LIMIT " . spl_escape_string($limit);
}
if (strlen(trim($offset)) > 0) {
    $sql .= " OFFSET " . spl_escape_string($offset);
}
//echo $sql;

# reporting some version info
# $rs = $db->query('SELECT sqlite_version()');

$rs = $db->query($sql);
if (!$rs) {
    echo "An SQL error occured.\n";
    exit;
}

/*
# Try query or error
$rs = pg_query($conn, $sql);
if (!$rs) {
    echo "An SQL error occured.\n";
    exit;
}
*/

/*
# Build GeoJSON
$output = '';
$rowOutput = '';

while ($row = pg_fetch_assoc($rs)) {
    $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type": "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';
    $props = '';
    $id = '';
    foreach ($row as $key => $val) {
        if ($key != "geojson") {
            $props .= (strlen($props) > 0 ? ',' : '') . '"' . $key . '":"' . $val . '"';
        }
        if ($key == "id") {
            $id .= ',"id":"' . $val . '"';
        }
    }
    
    $rowOutput .= $props . '}';
    $rowOutput .= $id;
    $rowOutput .= '}';
    $output .= $rowOutput;
}

$output = '{ "type": "FeatureCollection", "features": [ ' . $output . ' ]}';
echo $output;
*/


# Build GeoJSON
$output = '';
$rowOutput = '';

while ($row = $rs->fetchArray()) {
    $rowOutput = (strlen($rowOutput) > 0 ? ',' : '') . '{"type": "Feature", "geometry": ' . $row['geojson'] . ', "properties": {';
    $props = '';
    $id = '';
    foreach ($row as $key => $val) {
        if ($key != "geojson") {
            $props .= (strlen($props) > 0 ? ',' : '') . '"' . $key . '":"' . $val . '"';
        }
        if ($key == "id") {
            $id .= ',"id":"' . $val . '"';
        }
    }
    
    $rowOutput .= $props . '}';
    $rowOutput .= $id;
    $rowOutput .= '}';
    $output .= $rowOutput;
}

$output = '{ "type": "FeatureCollection", "features": [ ' . $output . ' ]}';
echo $output;

?>