4

I have a line layer in PostGIS that I would like to add to a Leaflet map. I have the PHP setup, but I can't seem to get the Javascript correct. I know my issue is converting the line to geoJSON.

The following is the JavaScript code I have setup:

function refreshAttractions(){
    $.ajax({
        url:'load_attractions.php', 
        type:'POST',
        data:{filter:$("#filter").val()},
        success:function(response){

            lyrAttractions=L.geoJSON(JSON.parse(response), {
                LineString: function(latlngs) {
                    var strPopup = "<h2><b>"+feature.properties.bikeway_ty+"</b></h2><hr>";
                    strPopup += "<h5><b>Sponsor:</b> "+feature.properties.sponsor+"</h5>";
                    strPopup += "<h5><b>Location:</b> "+feature.properties.location+"</h5>";
                    strPopup += "<h5><b>From:</b> "+feature.properties.from_limit+"</h5>";
                    strPopup += "<h5><b>To:</b> "+feature.properties.to_limit+"</h5>";
                    strPopup += "<h5><b>Name:</b> "+feature.properties.local_name+"</h5>";
                    strPopup += "<h5><b>Length:</b> "+feature.properties.length_mil+"</h5>";
                    //strPopup += "<img src='img/"+feature.properties.image+"' width='200px'>";
                    return L.polyline(latlngs).bindPopup(strPopup);
                }
            });
            lyrAttractions.addTo(mymap);  
    }});
}

And the PHP code that fetches data from PostGIS and returns JSON is:

<?php
    $strQry="SELECT sponsor, location, from_limit, to_limit, local_name, length_mil, bikeway_ty, ST_AsGeoJSON(geom, 5) as geom FROM bikeways_existing";

    $db=new PDO('pgsql:host=*****.com;port=5432;dbname=geozeoco_demo;','geozeoco','******');
    $sql = $db->query($strQry);
    $features=[];
    while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
        $feature=['type'=>'Feature'];
        $feature['geometry']=json_decode($row['geom']);
        unset($row['geom']);
        $feature['properties']=$row;
        array_push($features, $feature);
    }
    $featureCollection=['type'=>'FeatureCollection', 'features'=>$features];
    echo json_encode($featureCollection);
?>
IvanSanchez
  • 10,206
  • 2
  • 19
  • 35
floyd434
  • 135
  • 6
  • 1
    What's the table definition in PostGIS? (Are you using a geometry/geography column type?) What's the PHP code that makes the request to the DB? – IvanSanchez Feb 25 '19 at 10:54
  • I am using a geometry column type. I added my PHP code above. Thank you. – floyd434 Feb 25 '19 at 13:38
  • There is definitely something wrong with my PHP code above. It is failing to load it. Any ideas why it would be failing? I am now trying to run it on the localhost. – floyd434 Feb 27 '19 at 05:53
  • The PHP actually looks fine. I suggest you check the output with tools like geojson.io and http://geojsonlint.com/ just in case. – IvanSanchez Feb 27 '19 at 12:21
  • IvanSanchez you are correct, the PHP is fine, I believe the issue is with converting the PostGIS line layer to geoJSON in my Javascript. I have spent many hours over several days trying to figure it out. – floyd434 Mar 01 '19 at 15:16

1 Answers1

1

Since you are getting your data from POSTGIS, you might want to use its native geojson query param ST_AsGeoJSON
There is a detailed description in geojson from POSTGIS

NettaB
  • 1,936
  • 1
  • 10
  • 19