bmcbride / php-database-geojson

Query a database table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.

PHP-Database-GeoJSON

A collection of PHP scripts to query a database table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.

php-database-geojson's Issues

sql injection

Hi. I've been playing around with you code and producting some nice maps. However I showed your php to a friend who is hot on the subject and he said that this it would be easy to do sql injection to drop tables etc. Any thoughts on the subject, as I'd like to continue using your code for a map production piece I'm working on.

Multiple Line String

Hi Bryan,

I know this is not the right place to ask this question, but i'm going to try my luck anyway :), I adapt your routine/code instead of using wkb I use wkt instead to produce the data I want which is great, my question is can this code be used for Multiple Geometry and Multiple Line string?

How i can create a SQLite OGR database compatible with this example?

I have tried with Quantum GIS, to export from a KML file to a SQLite database (NO SPATIALITE), and it does not work.

I can't get it to work, and PHP show the following error:

[error] 27257#0: *73 FastCGI sent in stderr: "PHP message: PHP Fatal error: Allowed memory size of 1073741824 bytes exhausted (tried to allocate 31138083345 bytes) in /virumit.dev/public_html/demo/geoPHP/lib/adapters/WKB.class.php on line 100
PHP message: PHP Stack trace:
PHP message: PHP 1. {main}() /virumit.dev/public_html/demo/busRoutes_geojson.php:0
PHP message: PHP 2. wkb_to_json() /virumit.dev/public_html/demo/busRoutes_geojson.php:44
PHP message: PHP 3. geoPHP::load() /virumit.dev/public_html/demo/busRoutes_geojson.php:13
PHP message: PHP 4. call_user_func_array() /virumit.dev/public_html/demo/geoPHP/geoPHP.inc:73
PHP message: PHP 5. WKB->read() /virumit.dev/public_html/demo/geoPHP/geoPHP.inc:73
PHP message: PHP 6. WKB->getGeometry() /virumit.dev/public_html/demo/geoPHP/lib/adapters/WKB.class.php:44
PHP message: PHP 7. WKB->getLinstring() /virumit.dev/public_html/demo/geoPHP/lib/adapters/WKB.class.php:73
PHP message: PHP 8. fread() /virumit.dev/public_html/demo/geoPHP/lib/adapters/WKB.class.php:100

Only load the elements when it is converted to points, but i want to charge a bus route.

I will apreciate all kind of help
Thanks in advance

PGSQL geojson

Hi,
Sorry I'm contacting you from here.

I am facing some issues with the pgscript.
I have a tables working with schemas in my db i plugged in the pgscript code but I keep getting Connection errors.

Can you kindly help me fix this?

This is the code

`<?php
/*

• Title: PostGIS to GeoJSON
• Notes: Query a PostGIS table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
• Author: Bryan R. McBride, GISP
• Contact: bryanmcbride.com
• GitHub: https://github.com/bmcbride/PHP-Database-GeoJSON
  */

//# Connect to PostgreSQL database
$conn = new PDO('pgsql:host=localhost;dbname=gisdb','jdanso','');

//# Build SQL SELECT statement and return the geometry as a GeoJSON element
$sql = 'SELECT *, shape.ST_AsGeoJSON(dbo.ST_Transform((shape),4326),6) AS geojson FROM dbo.OH_CONDUCTOR_11KV_EVW';

/*

• If bbox variable is set, only return records that are within the bounding box
• bbox should be a string in the form of 'southwest_lng,southwest_lat,northeast_lng,northeast_lat'
• Leaflet: map.getBounds().toBBoxString()
  */
  if (isset($_GET['bbox'])) {
  $bbox = explode(',', $_GET['bbox']);
  $sql = $sql . ' WHERE public.ST_Transform(the_geom, 4326) && public.ST_SetSRID(public.ST_MakeBox2D(public.ST_Point('.$bbox[0].', '.$bbox[1].'), public.ST_Point('.$bbox[2].', '.$bbox[3].')),4326);';
  }

//# Try query or error
$rs = $conn->query($sql);
if (!$rs) {
echo 'An SQL error occured.\n';
exit;
}

//# Build GeoJSON feature collection array
$geojson = array(
'type' => 'FeatureCollection',
'features' => array()
);

//# Loop through rows to build feature arrays
while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
$properties = $row;
// # Remove geojson and geometry fields from properties
unset($properties['geojson']);
unset($properties['the_geom']);
$feature = array(
'type' => 'Feature',
'geometry' => json_decode($row['geojson'], true),
'properties' => $properties
);
// # Add feature arrays to feature collection array
array_push($geojson['features'], $feature);
}

header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
$conn = NULL;
?>
`

Displaying GeoJSON points with markerCluster plugin

Thanks for the very useful repo!

I want to use the Leaflet markerCluster plugin with GeoJSON points pulled from PostGIS. I think the markerCluster plugin needs to have all the data loaded first before it can cluster the points. How do I change the $.GetJSON method to include the markerCluster functionality? I've included a snippet of the code I am using with the markerCluster lines commented out:

//var markers = new L.markerClusterGroup();

    // Define point symbol
    var lampfaultsdata_Icon = L.icon({
        iconUrl: 'icons/lampfaultsIcon.png',
        iconSize: [20, 30],
        iconAnchor: [12, 28],
        popupAnchor: [0, -25]
    });

    // Define GeoJSON data
    var lampfaultsdata =  L.geoJson(null, {
        pointToLayer: function (feature, latlng) { 
          return L.marker(latlng, {
            icon: lampfaultsdata_Icon,
            title: feature.properties.assetname
          });
        },
        onEachFeature: function (feature, layer) {
          if (feature.properties) {
                var content = '<table border="1" style="border-collapse:collapse;" cellpadding="2">' +
                '<tr>' + '<th>Name</th>' + '<td><b>' + feature.properties.assetname + '</b></td>' + '</tr>' +
                '<tr>' + '<th>Date reported</th>' + '<td>' + feature.properties.faultdate + '</td>' + '</tr>' +
                '<tr>' + '<th>Fault Status</th>' + '<td>' + feature.properties.faultstatus + '</td>' + '</tr>' +
                '<tr>' + '<th>View Map</th>' + '<td>' + feature.properties.lamplocation + '</td>' + '</tr>' +
                '<table>';
            layer.bindPopup(content);
          }
        }
    });

    // Get GeoJSON data from PostGIS
    $.getJSON("layers/lamp_faults_geojson.php", function (data) {
            lampfaultsdata.addData(data);
            //markers.addLayer(lampfaultsdata);
            //map.addLayer(markers);
        }).complete(function () {
            map.fitBounds(lampfaultsdata.getBounds());
    });

In this case I am wanting to show faulty street lamps and I think I need to do something like this but can't work out where to fit it in:

        lampfaultsdata.on('data:loaded', function () {
        markers.addLayer(lampfaultsdata);
        map.addLayer(markers);
    });

    lampfaultsdata.on('data:loaded', function () {
        map.fitBounds(lampfaultsdata.getBounds());
        }.bind());

Any pointers gratefully received.