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);
?>
  • 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 at 10:54
  • I am using a geometry column type. I added my PHP code above. Thank you. – floyd434 Feb 25 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 at 5:53
  • The PHP actually looks fine. I suggest you check the output with tools like geojson.io and geojsonlint.com just in case. – IvanSanchez Feb 27 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 1 at 15:16
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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