postgresql – Show Points Layer from PostgreSQL Database on OpenLayers Map

ajaxgeojsonopenlayersPHPpostgresql

I have this code, I want to load a collection of points stored in a table in PostgreSQL database, I retreive the GeoJSON format succesfully, but the points never show up on the map!

The three files are included below: (Php file + html file + js file) 

// my php file to generate the geojson format

<?php 

require_once "connection.php";

// bd table to php array

function colsTabVersArray($nom_table){
    $res = array();
    $req = executerRequete("SELECT column_name FROM information_schema.columns WHERE table_name = '$nom_table'");
    while($ligne = pg_fetch_row($req)){
        $res[] = $ligne[0];
    }
    return $res;
}

// requete sql 

function executerRequete($requete){
    return pg_query($GLOBALS["db"], $requete);
}

// test


        $feature = array();
        $result = executerRequete("SELECT st_asgeojson(geom) AS geom FROM nyc_subway_stations");
        if($result)

        {
            while($row = pg_fetch_assoc($result)) {
                $type = '"type": "Feature"';
                $geometry = '"geometry": '.$row['geom'];
                unset($row['geom']);
                $properties = '"properties": '.json_encode($row);
                $feature[] = '{'.$type.', '.$geometry.', '.$properties.'}';

            }
            $header = '{"type": "FeatureCollection", "features": [';
            $footer = ']}';
            if(count($feature) > 0) {
                echo $header.implode(', ', $feature).$footer;
            }
            else {
                echo '{"type":"FeatureCollection", "features":"empty"}';
            }

        }


?>

**************************************************************************

// File Map.js 

data = {
    layer: true
}

actualiserCouche("couchePoint", 'couchePoint', 'Couche Point', data);
// /L'AJOUT D'UNE COUCHE


function actualiserCouche(couche, name, title, data) {

    geojson = new ol.format.GeoJSON();
    source_couche = new ol.source.Vector();

    // DÉFINITION DU STYLE DE LA COUCHE
    var myStyle = new ol.style.Style({
      image: new ol.style.Circle({
        radius: 20,
        fill: new ol.style.Fill({color: 'black'}),
        stroke: new ol.style.Stroke({
        color: [255,0,0], width: 2
        })
      })
    })
    // /DÉFINITION DU STYLE DE LA COUCHE

    // DÉFINITION DE LA COUCHE ARRONDIS
    window[couche] = new ol.layer.Vector({
        name: name,
        title: title,
        visible: true,
        source: source_couche,
        style: myStyle
    });
    // /DÉFINITION DE LA COUCHE ARRONDIS

    // SUPPRESSION DU CONTENU DE LA COUCHE
    source_couche.clear();
    // /SUPPRESSION DU CONTENU DE LA COUCHE

    // L'APPEL AJAX AVEC LES PARAMÈTRES
    success = function (result) {
        console.log(result);
        var features = geojson.readFeatures(result, { featureProjection: 'EPSG:26918' });
        source_couche.addFeatures(features);
    }
    error_fatale = function (jqXhr) {
        console.log(jqXhr);
    }   
    ajax("index2.php", data, error_fatale, success);
    // /L'APPEL AJAX AVEC LES PARAMÈTRES

    // L'AJOUT DE LA COUCHE À LA CARTE
    map.addLayer(window[couche]);
    // /L'AJOUT DE LA COUCHE À LA CARTE

}

function ajax(url, data, error, success = function (resultat) {
    if (resultat.type == "erreur") {
        console.log("erreur", resultat.msg);
    }
    else if (resultat.type == "succes") {
        console.log("succes", resultat.msg);
    }
}, complete = null, beforeSend = null) {

    $.ajax({
        url: url,
        data: data,
        type: 'POST',
        dataType: 'JSON',
        beforeSend: beforeSend,
        success: success,
        error: error,
        complete: complete
    });
}


**********************************************************************

//File : index.html to load the map 

<html lang="en">
  <head>
   <link rel="stylesheet" href="plugin/open-layers/css/ol.css">

    <style>
      .map {
        height: 100%;
        width: 100%;
      }
    </style>
       <title>OpenLayers example</title>
  </head>
  <body>
    <h2>My Map</h2>
    <div id="map" ></div>



<!-- SCRIPT D'OPEN LAYERS -->
    <script src="plugin/open-layers/js/ol.js"></script>

    <!-- /SCRIPT D'OPEN LAYERS -->
     <script>

  var map = new ol.Map({
        target: 'map',
        layers: [
          new ol.layer.Tile({
            source: new ol.source.OSM()
          })
        ],
        view: new ol.View({
          center: ol.proj.fromLonLat([-74.010806, 40.711412]),
          zoom: 10
        })
      });

       </script>

<script src="plugin/jquery/jquery.min.js"></script>
  <script src="map.js"></script>


  </body>

</html>

Here is a part of the geojson I get in tho log on browser 

{"type": "FeatureCollection", "features": [{"type": "Feature", "geometry": {"type":"Point","coordinates":[583521.854408956,4507077.86259909]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[583324.48663246,4506805.37316021]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[583304.182399475,4506069.65404812]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[590250.10594797,4518558.01992433]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[590454.739989117,4519145.71961785]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[590465.893419111,4519168.6974832]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[590573.169495527,4520214.76617728]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[591252.83141041,4520950.35335555]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[590946.3972263,4521077.31897688]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[591583.611145281,4521434.84662681]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[591242.929957587,4521581.88593774]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[593778.100679278,4521296.80231282]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":[591869.966492465,4522223.76481329]}, "properties": []}, {"type": "Feature", "geometry": {"type":"Point","coordinates":

Best Answer

What you have got wrong is CRS specification when reading features from GeoJSON with readFeatures method. This method has two CRS options: dataProjection tells projection of GeoJSON data and featureProjection tells projection of generated features.

In your case dataProjection should be 'EPSG:26918' and featureProjection default 'EPSG:3857':

var features = geojson.readFeatures(result, {dataProjection: 'EPSG:26918', featureProjection: 'EPSG:3857' });

Here is the picture of markers, based on your sample GeoJSON:

enter image description here

For EPSG 26918 to work you need to define it with proj4.js library:

<script type="text/javascript" src=https://cdnjs.cloudflare.com/ajax/libs/proj4js/2.5.0/proj4.js"></script>

proj4.defs("EPSG:26918","+proj=utm +zone=18 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs");
ol.proj.proj4.register(proj4);
Related Question