1

I have a MySQL spatial database ('mydb') of one table ('tst'), I mean by spatial. I need to output the GeoJSON file from this spatial MySQL table. I downloaded geoPHP library and also MySQL to GeoJSON script which I adapted to my database settings, like below:

<?php
/**
* Title:   MySQL to GeoJSON (Requires https://github.com/phayes/geoPHP)
* Notes:   Query a MySQL 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
*/

# Include required geoPHP library and define wkb_to_json function
include_once('geoPHP/geoPHP.inc');
function wkb_to_json($wkb) {
    $geom = geoPHP::load($wkb,'wkb');
    return $geom->out('json');
}

# Connect to MySQL database
$conn = new PDO('mysql:host=localhost;dbname=mydb','root','test123');

# Build SQL SELECT statement and return the geometry as a WKB element
$sql = 'SELECT *, AsWKB(SHAPE) AS wkb FROM mytable';

# 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 wkb and geometry fields from properties
    unset($properties['wkb']);
    unset($properties['SHAPE']);
    $feature = array(
         'type' => 'Feature',
         'geometry' => json_decode(wkb_to_json($row['wkb'])),
         'properties' => $properties
    );
    # Add feature arrays to feature collection array
    array_push($geojson['features'], $feature);
}
header('Content-type: application/json');
//write to json file
$fp = fopen('mytable_geojson.json', 'w');
fwrite($fp, json_encode($geojson));
fclose($fp);
$conn = NULL;
?>

But when I execute the code on the browser they show that:

    out('json'); } 
# Connect to MySQL database 
$conn = new PDO('mysql:host=localhost;dbname=mydb','root','test123'); 
# Build SQL SELECT statement and return the geometry as a WKB element 
$sql = 'SELECT *, AsWKB(SHAPE) AS wkb FROM mytable'; 
# 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 wkb and geometry fields from properties 
unset($properties['wkb']); 
unset($properties['SHAPE']);
 $feature = array( 'type' => 'Feature', 'geometry' => json_decode(wkb_to_json($row['wkb'])), 'properties' => $properties ); 
# Add feature arrays to feature collection array 
array_push($geojson['features'], $feature); } header('Content-type: application/json'); 
//write to json file 
$fp = fopen('mytable_geojson.json', 'w'); 
fwrite($fp, json_encode($geojson)); 
fclose($fp); 
$conn = NULL; ?>
2
  • 1
    I've tried to recover the format of the second block of output but there's a chance I've messed it up - but at least its readable now. Please try and format your pasted code and output for legibility.
    – Spacedman
    Apr 2, 2021 at 9:44
  • @Spacedman thanks so much
    – Kawtar
    Apr 2, 2021 at 9:57

2 Answers 2

1

Late but hoping it would help someone. Without using geoPHP library:

getGeoJSON.php:    
// Connect to MySQL database
    $conn = new PDO('mysql:host=localhost;dbname=mydb','root','test123');

    // Build SQL SELECT statement and return the 'shape' as GeoJSON
    $sql = 'SELECT
             *,
             ST_AsGeoJSON(SHAPE) AS shape
             FROM mytable';

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

     // save features in list
        while ($row = mysqli_fetch_assoc($rs)) {
            $listeAttributes[] = array(
                "X1" => $row["X1"],
                "X2" => $row["X2"],
                "Xn" =>$row["Xn"],
                "shape" => $row["shape"]
            );
        }

// open a <script> tag for writte JS code

<script>
// Encode the feature list on JSON format
 var list_elements = <?php echo(json_encode($isteAttributes)) ?>;

// Function for get the GeoJSON
function getGeoJSON(list_features) {
    var geoJSON = {
        "type": "FeatureCollection",
        "features": [],
    };

// loop through the elements of the list
for(var i = 0; i < list_features.length; i++) {
        var element = list_features[i];
        // Create the feature object
        var feature = {
            "type": "Feature",
            "geometry": {},
            "properties": {}
        };
        for(var key in element) {

                    if(key == "shape") {
                        // getting the geometry
                        feature.geometry = JSON.parse(element[key]);
                    }
                    else {
                        // For the other columns we add them in 'properties'
                        feature.properties[key] = element[key];
                    }
                }
                // Once the feature is created, we add it to the "features" key of our "geoJSON" object
                geoJSON.features.push(feature);
            }

        return geoJSON;
    }

// Pass the list to the function and verify
    console.log('the output geojson: ');
    var geojson_res = getGeoJSON(list_elements);
    console.log(geojson_res);

</script>
0

如何從 MySQL 資料庫使用 geometry 型態儲存wkt多邊形的 shapefile 輸出成 GeoJSON 格式文件?

PHP CODE:

<?php
// .... 

echo geoJson(getData());

function geoJson($data){
$geojson = array(
    'type' => 'FeatureCollection',
    'crs' => array(
        "type"  => "name",
        "properties" => array("name" => "")
    ),
    'features' => array()
);
$features = array();
foreach($data as $key => $feature){
    $features[] = array(
        "type" => "Feature", 
        "properties" => array(
            "名稱" => $feature['name'], 
            "時間" => $feature['time'], 
        ), 
        "geometry" => $feature['wkt']
    );
}
$geojson['features'] = $features;
return toGeoJson($geojson);
}

function getData(){
$sql = 'SELECT xxxxx,xxxxx, time, ST_AsGeoJSON(geometry) as wkt 
FROM xxxxx';
$data =  $your_sql_orm->fetch();

/* the output data form you db like:
Array
(
[0] => Array
    (
        [name] => RXXXXXX1
        [time] => 2023-xxx-xxxx
        [wkt] => {"type": "Polygon", "coordinates": [[[120, 22], [120.3, 22.6], [120.37, 22.69], [120.37, 22.7], [120, 22]]]}
    )
[1] => Array
    (
        [name] => RXXXXXX2
        [time] => 2023-xxx-xxxx
        [wkt] => {"type": "Polygon", "coordinates": [[[100.9, 23.8], [118.21, 23.13], [118.3, 23.06], [119.2, 23.57], [100.9, 23.8]]]}
    )
[2] => Array
....
*/
return $data;
}

/** Code by Arter */
function toGeoJson($arr){
header('Content-type: application/json');
$json = json_encode($arr, JSON_PRETTY_PRINT|JSON_UNESCAPED_SLASHES|JSON_HEX_QUOT|JSON_HEX_TAG|JSON_HEX_AMP|JSON_HEX_APOS);
// 由於陣列內會產生多餘的反斜線,故取代移除 
// 如:"{\"type\": \"Polygon\", \"coordinates\":
$json = str_replace('\u0022', '"', $json);
$json = str_replace('"{', '{', $json);
$json = str_replace('}"', '}', $json);
return $json;
}

And Outupt a GeoJson format:

{
    "type": "FeatureCollection",
    "crs": { 
        "type": "name", 
        "properties": {
            "name": "urn:ogc:def:crs:OGC:1.3:CRS84"
        }
    },
    "features": [
        {
            "type": "Feature", 
            "properties": { 
                "名稱": "RXXXXXX1", 
                "時間": "2023-xxx-xxxx"
            }, 
            "geometry": {"type": "Polygon", "coordinates": [[[120, 22], [120.3, 22.6], [120.37, 22.69], [120.37, 22.7], [120, 22]]]}
        },
        {
            "type": "Feature", 
            "properties": { 
                "名稱": "RXXXXXX2", 
                "時間": "2023-xxx-xxxx"
            }, 
            "geometry": {"type": "Polygon", "coordinates":  [[[100.9, 23.8], [118.21, 23.13], [118.3, 23.06], [119.2, 23.57], [100.9, 23.8]]]}
        }
    ]
}
1
  • 1
    As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.
    – Community Bot
    Feb 3, 2023 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.