Created
September 20, 2013 15:45
-
-
Save wboykinm/6639559 to your computer and use it in GitHub Desktop.
Points on a map!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<?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 | |
*/ | |
# Connect to PostgreSQL database | |
$conn = new PDO('pgsql:host=localhost;dbname=mypostgisdb','myusername','mypassword'); | |
# Build SQL SELECT statement and return the geometry as a GeoJSON element | |
$sql = 'SELECT *, public.ST_AsGeoJSON(public.ST_Transform((the_geom),4326),6) as geojson FROM mytable'; | |
/* | |
* 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; | |
?> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment